none
SSIS Package issue while OLEDB Connection String retrieved from variable. RRS feed

  • Question

  • Hi,

    My SSIS Package(VS2012) have two different SQLServer connection called SrcCon and DestCon . And its working fine with normal scenario. But when I try to pass the connection string using a package variable with the help of Expression editor like connectionstring = strconvariable, Then SrcCon getting connected fine but DestCon having  issue to connect and all the task used by the DestCon marked as red. If I open the task and try to connect DestCon it will asking me to username and password every time. so every time It will lead me the username,password prompt.

    SrcCon : Provider=SQLNCLI11.1;Password=XXXXX;Persist Security Info=True;User ID=myuser;Initial Catalog=SrcDB;Data Source=*.0.0.31;

    DestCon : Provider=SQLNCLI11.1;Password=XXXXX;Persist Security Info=True;User ID=myuser;Initial Catalog=DestDB;Data Source=*.0.0.31\Inst2;

    Expecting valuable suggestions.



    Thanks, Vasantha Prabakaran


    Wednesday, March 4, 2015 9:38 AM

Answers

  • Sorry I didnt get you

    Are you telling even with configuration it doesnt work?

    You can even pass password alone if you want through config and then use an expression to append it to rest of the connectionstring inside another variable. Finally you can use appended variable to map to connectinstring property of the connection

    Do not save sensitive protection level setting means it will never save senstive information like password which have been set at design time inside the package so you've to use configuration itself for passing the values


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Wednesday, March 4, 2015 12:04 PM

All replies

  • Thats is because you're not using trusted connection (windows authentication) so you've use a uername password which has to be passed

    What you can do in that case is to make credentials as config items and pass values through it. Then it will get values always and work without prompting

    see this example

    http://blogs.msdn.com/b/runeetv/archive/2009/12/22/ssis-package-using-sql-authentication-and-dontsavesensitive-as-protectionlevel.aspx


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 4, 2015 10:20 AM
  • I do have same answer as visakh, make credentials as config items .

    and also you can try by setting a package level encryption to save your password.

    Wednesday, March 4, 2015 10:39 AM
  • Thanks Vikash & SKC,

    Normally we will use entire connection string  value to a variable and then variable value should be taken from configuration file as you suggested. because we can change the username, password,server even we can modify to windows authentication too.

    Package encription level : Do not save sensitive 

    Every thing working fine with the source but not for Destination.

    Thanks,


    Thanks, Vasantha Prabakaran

    Wednesday, March 4, 2015 11:53 AM
  • Sorry I didnt get you

    Are you telling even with configuration it doesnt work?

    You can even pass password alone if you want through config and then use an expression to append it to rest of the connectionstring inside another variable. Finally you can use appended variable to map to connectinstring property of the connection

    Do not save sensitive protection level setting means it will never save senstive information like password which have been set at design time inside the package so you've to use configuration itself for passing the values


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Wednesday, March 4, 2015 12:04 PM