Post reply

Problem with SSIS connection manager while running from SQL job

  • July 8, 2014 at 1:41 am

    #287588

    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

    #1727668

    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

    #1728258

    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