the connection string format is not valid . it must consist of one or more components of the form….error

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.


Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in SSIS (SQL Server Integration Service). Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s