Back again with another SSIS SQL Server based configuration problem. I was using SQL server based configuration and the SSIS configuration wizard created one table with the default field lengths. The table is like this:
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) NOT NULL,
[ConfiguredValue] [nvarchar](255) NULL,
[PackagePath] [nvarchar](255) NOT NULL,
[ConfiguredValueType] [nvarchar](20) NOT NULL
)
ON [PRIMARY]
While importing my configuration properties into this table, I realized that one of my package property’s “ConfiguredValue” is more than 255 in length and it was causing an error. So, I opened the table in design mode in Management Studio and modified the length of that field to 4000. Now, it was time for the great surprise, when I started the package, it was throwing an error like this:
the connection string format is not valid . it must consist of one or more components of the form…
I tried to find out where is the problem in my connection string and after a lot of search, I got my answer. I reduced the long “ConfiguredValue” of that SSIS package property to 255 and modified the table field’s length back to 255. Then the package worked. The lesson is, when the table is already created by the wizard, u cannot change its length afterwards. If u do so, u cannot run the package. So, always adjust the column length of the configuration table at the time of creation.
Zahid's blog on Data Engineering and BI 