March 1, 2010 at 10:14 am
I'm about to begin building SSIS jobs and sequestering data into a data warehouse and I'd really like to get off on the right foot. I've built SSIS jobs before, in fact I built a big one that runs looping through ODBC connections and it pulls data and it works just great. The problem is, I got it to run and I don't want to recreate the hot mess that it is. Mostly because I got it to work somehow, and in some parts, it works and I don't know how :0
I'd like to start with something small and do it properly by building in notification when things succeed instead of just when they fail. I'd like to build in pieces so that I can scale it up slowly and leverage pieces for other jobs i know I'm going to build.
To that end I would like to build a job that I can use as the basis for this big job. That way, I can use it to go back and run the job again for the individual facilities if they fail once I discern what the failure was caused by. We have a bunch of data that exists in 47 separate databases. I wrote a job to cycle thru all of the odbc connections, copy data from the Progress db table to the SQL Server table and then go on to the next facility in the loop.
What I'd like to do initially is build a single facility job that I can prompt for a facility in some kind of drop down. The connection string information I've stored in a table:
SELECT
[odbcConnSeq]
,[connString]
,[facID]
,[updated]
FROM [odbcConnections]
so what I'd really like to do is give a user (most likely me) a dropdown interface where they can pick from a list of odbcConnections from this table and run the job.
As I am writing this, something just occurred to me. Is there a way to create one package that will run everything for one facility, and create another package that just consists of a for-loop that feeds the connection information to the first package and embeds the first package within it?
That way I'll be guaranteed to be running the same thing for a failed site as it would normally run for all of them.
March 2, 2010 at 12:16 am
My first guess is the use an Execute Package Task in a for loop. (or a for each loop).
Right before this taks you use another task (Execute SQL Task or Script Task or something else) that writes the connection information to a configurion table or config file. Link these two tasks together with a precedence constraint and you're all set.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply