Post reply

passing connectionstring variable in connectionmanager

  • April 17, 2009 at 12:18 pm

    #131300

    Hi need to set a variable in the connection manager properites with expression ,can you please help me and send me some sample and detailed example

    please its very urgent

    thanks in advance

    Thanks,

    Neeth

  • April 17, 2009 at 2:23 pm

    #979969

    Not sure what you want to do exactly but this might help http://msdn.microsoft.com/en-us/library/ms141214.aspx

    Please post more details about what exactly you are trying to do?

  • April 19, 2009 at 5:26 am

    #980333

    Thank you for your reply Mukti,

    Actually, my situation is my database name,server name are coming from execute SQL task and these will be changing dynamically ,according to the server name and database name I need to run my package. I am strong these source server name, source DBname ,destination server name and destination DB name(which are coming from execute sql task) loading into the variables….then I am using a for each loop with ADO enumerator and source type is variable ,in this loop I placed a DF task which contains OLEDB source and OLEDB target and loading the data.finally I need to set USERNAME and PASSWORD in the variables for each one. Here I need to pass these variables in the connection manager properties with in the expressions like username, server name, initial catolg.Here is my problem is that where I need to set ‘password’ variable in the expressions.I came to know that I need set all these with "Data Source="+@[User::SourceServer]+";User ID="+ @[User::ConnectionUserID] +";Password="+ @[User::ConnectionPassword] +";Initial Catalog="+ @[User::SourceDatabase] +";Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False"

    Please let me know if you know about this please………and explain with the code

    Its very urgent for me

  • April 19, 2009 at 11:04 pm

    #980515

    [font="Verdana"]Hi,

    Have you checked the Expressions Editor of your DataFlow Task, which has got the Source and Destination Transformation. Basically all the properties are listed in the DataFlow task Expression Editor. Have a look on your DataFlow task Editor's Expression window. It may have the properties to define your connection string.

    Just have a look on it and let me know whether you are able to solve your problem.

    Regards,

    Ashok S[/font]

  • April 19, 2009 at 11:12 pm

    #980516

    Hi Ahok,

    Thanks you so much for your reply.........

    But I cant find any thing in the properties of expressions in Dataflow task.are you asking me to check in the source and destination properties

    please let me know it is very urgent for me to delever

  • April 20, 2009 at 11:36 am

    #980918

    an easy debugging method (if u dont already know it)

    Put a script task component between your Execute SQL Task and DataFlow Task and print the variables DBName, ServerName etc

    Add the following line of code in the script

    MsgBox.Show("ServerName: " + Dts.Variables["YourServerVariableName"].ToString "; DBName: " + Dts.Variables["YourDBVariableName"].ToString)

    And maybe disable the DFT and then execute the package see what your variables are being output as from the execute sql task.

    Another alternative:

    You could build another variable called connectionString in the Script Component (Add it a Read-Write vaiable in the script component). First see if the above works.

  • April 21, 2009 at 4:57 am

    #981305

    The connectionString, Servername etc is set in the ExpressionEditor of your ConnectionManager, not in the Data flow.

  • April 21, 2009 at 7:52 am

    #981438

    This is really easy to do. Create several variables (global) that will be used to pass your info.

    Then you will have to use a Script Task inside of the ForEach Loop. In that script task set your connection variables (i.e. ServerName, Catalog, etc.). The variable will have to be set to WriteVariables in the ScriptTask editor.

    This is pseduo code for script task:

    @ServerName= @ADOServerNameValue

    @Catalog=@ADOCatalogValue

    @UserName=@ADOUserNameValue

    ...etc

    Then on your Connections editor go to Expressions and assign the ServerName to your variable name used in the Script Task. There will be a dropdown that lets you choose each Connection component and then assign your variable names. This will cause the connection to change with each loop.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply