November 8, 2011 at 6:44 pm
Is there a way to store connection information in a table, and then feed that information, row by row, to an SSIS package? The table would probably look something like this.
Source Connection Destination Connection
------------------ ----------------------
Source 1 Destination A
Source 1 Destination B
Source 2 Destination C
Source 3 Destination D
Also, we use VisualChron. Could VisualChron read this data from the table as parameters and pass it to SSIS?
November 9, 2011 at 2:06 am
To do this I generally enable package configurations and use an XML file for storing the values.
Add package variables called TargetDatabase and Target Server as strings.
Populate them from the package configuration.
Set the connection to have a expression for the connection string.
That way moving from one server to another you just have different configuration files.
If you are talking about table names for OledB sources and destination rather than connections then the same approach can be taken. Just use the variable rather than the name of the table.
Hope that helps.
M
November 9, 2011 at 4:27 am
Package configurations are a good option as you can read from table as well as XML files.
An alternative method (not neccessarily the correct method but it works) is to do the following:
Start with an execute sql task that queries your table. Set result set property to Full result set;
Map the result to a variable of type object;
Then have a for each loop set to run for each ADO Enumerator and select said variable.
Set up further variables for each field returned in the initial table query and map them in the for each loop.
What you've essentially got now inside your for each loop is a process that will loop through each of your records in the table holding the connection data. Use your variables to then manage your connections.
November 9, 2011 at 11:38 am
These are all great ideas. However, the requirement is that the SSIS packages iterate through a list of source and destination connections. If possible, the source/destination connection pair should also have an active/inactive flag.
November 9, 2011 at 1:31 pm
chris_hurley (11/9/2011)
An alternative method (not neccessarily the correct method but it works) is to do the following:Start with an execute sql task that queries your table. Set result set property to Full result set;
Map the result to a variable of type object;
Then have a for each loop set to run for each ADO Enumerator and select said variable.
Set up further variables for each field returned in the initial table query and map them in the for each loop.
What you've essentially got now inside your for each loop is a process that will loop through each of your records in the table holding the connection data. Use your variables to then manage your connections.
That will get you what you want. It will get source and destination names into a variable and then you use that for your source and destination?
November 10, 2011 at 2:30 pm
The variable of type System.Object can have multiple fields, so the correct combination of Source/Destination works.
When the table adds an active flag, then you just adjust the SQL to include the value for Active.
Also, use an ADO NET connection with the Execute SQL Task and not an OLEDB Connection
The last step to making the connection truly dynamic is, in the Connection section of the design surface; create a connection for each the Source and Destination. Then in the Expression of each Connection, reference the corresponding variable mapped to the Foreach Enumerator as stated above.
November 17, 2011 at 2:49 pm
This all worked well, thank you! Now for the next step. We want to turn the previously mentioned package into a parent package. And we want the child packages to be inside of the Foreach Loop Container instead of a data flow task.
Here is the question: how can the parent package pass both the Source and Destination Connections as variables to the child packages? If I understand correctly, a parent package can only pass one variable using the Package Configurations. Am I correct in that assumption? And if so, how to get around it so I can send two variables?
November 17, 2011 at 3:31 pm
There's no reason you can't create more than one Parent Package configuration, each for its own variable.
November 17, 2011 at 3:37 pm
You're right. I created two parent package configurations, one for each variable. Now I'm trying to figure out the "plumbing" of passing the variable values from parent to child.
November 17, 2011 at 3:49 pm
Basically, when the child package executes configurations are applied (the value of the parent package is retrieved at this point in time).
November 17, 2011 at 4:52 pm
You're right again. Why am I making this so complicated?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply