CODEWITHSUNDEEP
ssis
Metaphor
Metaphor

Reputation: 6415

Using SSIS 2012 package parameters for connection properties

I am trying to write ETL that collects data from many identical server into a central repository. What I'm trying to do is write one package with source address, user id and password as parameters and execute the package once per server to be copied.

Is this doable? How can I use parameters to create a source?

Upvotes: 3

Views: 9984

Answers (2)

Metaphor
Metaphor

Reputation: 6415

I meant to ask how to parametrize the connection manager (is that even a real word?), not where to store the connection parameters. The answer is simple:

  1. Create package parameters for Server, Database, User ID and Password
  2. Create a connection manager as part of defining a data flow component
  3. once a connection is defined, right-click on the connection manager at the bottom of the package design screen and select "Parametrize".
  4. Select "ServerName" in the property drop-down
  5. Select "Use existing parameter" or create new parameter if skipped step 1
  6. If using existing parameter, select it from the drop down
  7. Click OK to save (gotta do it after each parameter)
  8. Repeat steps 4-7 for the rest of the parameters

Upvotes: 9

Sam
Sam

Reputation: 7678

You can store parameters in a table. Query the table with a sql task and store the results in a object variable. You can then use this variable in a for loop. Use expressions in SSIS to change values of your connection during each loop iteration.

Several books outline this method. Here is a code example.

Here are some steps - hopefully I didn't miss anything. You mention a server "Address", but I'm not sure exactly what you are trying to do. This example queries multiple sql servers.

You create the variables, SQL_RS with type of object, SRV_Conn with type of string. This holds my servername. In the execute SQL task, I have a query which returns the names of sql servers I want to query. Then set the following properties:

SELECT     RTRIM(Server) AS servername
FROM         ServerList_SSIS
WHERE     (Server IS NOT NULL)
and coalesce(exclude,'0') <> 'True' 
and IsOracle is Null

Execute SQL Task > General > ResultSet = "Full Result Set"

Execute SQL Task > Result Set Tab "Result Set Name = 0", Variable Name = "User::SQL_RS"

So we have a list of server names in the SQL_RS variable now.

ForEach > Collection > Enumerator = "Foreach ADO Enumerator"

ForEach > Collection > Enumerator Configuration > ADO Object source Variable = User::SQL_RS

This maps the first column of the SQL_RS object to the SRV_Conn variable, so each iteration of the loop will result in a new value in this variable.

ForEach > Variable Mappings > Variable = User::SRV_Conn, Index = 0

Inside the ForEach are some other sql execs, performing queries on sql databases, so I need to change the ServerName of my 'MultiServer' connection. I have another connection for the initial query that got me the list of servers to query. Making the connection dynamic is done in properties of the connection - right-click the connection > properties. Click the ellipses to the right of the expressions.

Connection > Properties > Expressions > Property = ServerName, Expression = "@[User::SRV_Conn]"

Note: The index value of 0 for the variable mapping works for Native OLEDB\SQL Server Native Client. If you're using another db provider, you may need to use other index types - this makes setup more confusing. 

OLEDB = 0,1
ADO.NET = @Varname
ADO = @Param1, Param2
ODBC = 1,2

Full listing here.

Upvotes: 1

Related Questions