July 8, 2014 at 1:41 am
I am facing below issue while executing SSIS package via SQL job:
Description: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager
This issue is occuring for all OLE DB connection managers in SSIS package. But I have already defined the connection string in my package config file (Sample below):
<Configuration ConfiguredType="Property" Path="\Package.Connections[XXXXXX].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>
Data Source=XXXXX;Initial Catalog=XXX;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-XXXXX;
</ConfiguredValue>
If you have faced a similar issue in past or could share your experience, that would really appreciated.
Thanks,
Maninder Kumar
July 8, 2014 at 2:15 am
Are there any configuration warnings?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 9, 2014 at 11:02 pm
Hi Koen,
I couldn't find any configuration warnings as I was running it via SQL Server Agent job and the package was running without any issue in my local machine.
However, I used below workaround and I was able to get pass this issue:
Created a UserVariable to hold the connection string value through Expresssion.
In package config file, instead of using Package.Connection I used package.variables and referred to above mentioned variable:
<Configuration ConfiguredType="Property" Path="\Package.Variables[User::XXXXX_ConnStr].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>
Data Source=XXXXX;Initial Catalog=XXXXX;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-XXXXX;
</ConfiguredValue>
Hopefully this would help anyone who faces similar issue in future.
Regards,
Maninder Kumar
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply