Long SQL code in variable expression : Objecname column size

[tgauchet]

Hi Keith,
In an SSIS package we have long queries as expression in string value.. More than 1000 chars, and even more than 4000. those values are stored in Objectname column, defined as NVARCHAR(1000)
This lead to following issue:
"Committing analysis information to database...Unexpected error occurred: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column."
We have change the column definition in Repository.cs
sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Objects]') AND type in (N'U'))\r\n" + "BEGIN\r\n" + "CREATE TABLE [dbo].[Objects](\r\n" + " [RunKey] [int] NOT NULL,\r\n" + " [ObjectKey] [int] NOT NULL,\r\n" + " [ObjectName] [nvarchar](MAX) NULL,\r\n" +
What do you think about it?

[keif888]

It shouldn't be creating an ObjectName field with the expression's string value. That should be stored into the ObjectDesc field.

Can you please upload an example SSIS package that demonstrates your issue, so I can definitely fix it.
Make the SSIS package as simple as possible.

FYI:
The correct code to address this within Repository.cs should be as follows, and is checked into source now:

if (dbVersion == 9)
            {
                using (SqlCommand sqlCommand = repositoryConnection.CreateCommand())
                {
                    dbVersion = 10;
                    sqlCommand.CommandText = String.Format("INSERT INTO dbo.Version\r\n" +
                                            "(VersionID, InstallDate)\r\n" +
                                            "VALUES\r\n" +
                                            "({0}, GETDATE())", dbVersion);
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "ALTER TABLE [dbo].[Objects] ALTER COLUMN [ObjectDesc] NVARCHAR(MAX)";
                    sqlCommand.ExecuteNonQuery();
                }

            }