0

I have made a SSIS package to transfer data between two databases on different server. Now the issue is that i am unable to pass connection string in to package dynamically at run time. i have a windows form to execute that package. On that Windows form i have drop downs to select source and destination Databases to transfer data. so can i have a way to tell a package that this is the connection string of source and destination database where it needs to perform actions at running time, dynamically on click of windows form drop down clicks..

user2587360
  • 1
  • 1
  • Look at http://stackoverflow.com/questions/2497875/ssis-how-to-set-connection-string-dynamically-from-a-config-file I think this is related. You need to create parameters to your project then reference those parameters in the connection manager. – Brian O''Byrne Aug 28 '13 at 11:25
  • Are you starting your package via `dtexec.exe` or are you instantiating the underlying .net object model? – billinkc Aug 28 '13 at 12:14

2 Answers2

0

If the destination server will not change each time you can concatenate the server name in the ssis package connection string with variable, If not you need to pass the server name too form the Windows form.

Roshan Nuvvula
  • 803
  • 1
  • 8
  • 28
  • Thanks Roshan, I can pass the server name from win form, but i am new to this, can you please tell me how i can change connection string of SSIS package using variable. – user2587360 Aug 28 '13 at 10:34
  • Open the existing ssis package in BIDS Create two new varialbe (VAR1 & VAR2) in with data type string and the scope should thorughout the package. For Ex: if you want to change the source connection then you need to overwirte the existing ServerName with New server and Database with New dababase Pass the Source connection servername to Var1 and Databasename to VAR2 Just override the Servername & Databasename using expression with new Variables. – Roshan Nuvvula Aug 28 '13 at 13:19
0

In your package, right-click on the connection manager and select "Parametrize". Create paramenters for ServerName, InitialCatalog, UserName and Password. When executing the package, pass the parameters for the connection you want.

Alternatively, you can create one parameter for ConnectionString and pass that as a whole. I, personally, prefer the former.

I should add... this is for SSIS 2012 only. Package parameters didn't exist before then.

Metaphor
  • 6,157
  • 10
  • 54
  • 77