July 12, 2012 at 3:24 pm
Hello All,
I'm trying to create an SSIS package that will loop through ten different databases and perform a simple data flow task on each. All of the database schemas are identical and I just need to pull some data into a centralized table for each. I've found a bunch of different discussions and walkthroughs on the web and it sounds like my best bet is to dynamically create the connection string for each database and pass that into a variable in the ForEach loop. While this sounds like the best way to go, I haven't been able to get this to work for the life of me. Does anyone have a reliable walkthrough that I could follow or possibly a sample package they could provide to an SSIS newbie?
Thanks!
Ryan
July 12, 2012 at 11:54 pm
This is how I would do it:
1. Store the names of the databases in a table.
2. Execute a SQL query that fetches the database name. Store the result in an object variable by using the Full Result Set option.
Examples:
SSIS Nugget: Execute SQL Task into an object variable - Shred it with a Foreach loop
3. Drag in a For Each Loop and shred the object variable (explained in first example). In the For Each Loop, you can map a column of the current row to an SSIS variable. Put the database name into an string variable.
Example:
SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers[/url]
4. Put an expression on the OLE DB connection manager. I assume the server is always the same, so you need to put an expression on the database name only. Go to the properties of the OLE DB connection manager, go to expressions, hit the ellipsis. In the pop-up window, choose the property InitialCatalog and as expression choose your string variable.
5. Set the dataflow and the OLE DB connection manager to DelayValidation = True.
That should be about it. If you have any question or issue, let me know.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 17, 2012 at 3:50 am
Koen Verbeeck process is very good and I hope this is the one of the best and simple way to achieve your requirement
July 17, 2012 at 2:47 pm
Koen, thank you so much for your response and help. I'm making some progress, but I've hit a wall. I have the package created using your instructions. I had to make a minor addition because all of the databases aren't necessarily on the same server. (In the development environment they are, but not in production) So I just added another variable for the ServerName property.
Now when I attempt to run the package the first iteration completes and writes the rows to my destination table, but then it errors out and doesn't hit the second database. Unfortunately, the package output isn't very helpful (see attached) and I can't figure out what I'm doing wrong. Is there another step that I need to perform to get the new ServerName and IntialCatalog variables populated?
July 18, 2012 at 1:40 pm
There's a problem with the column system_date:
"The value violated the integrity constraints for the column"
My guess is you're trying to insert some value that violates a primary key, a unique key or a check constraint.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 19, 2012 at 3:54 am
I think you are inserting the duplicate records into a primary key column or Null values into NOT NULL columns.Check your data source.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply