March 5, 2010 at 11:34 am
hi,
i have a set of Execute SQL Tasks in an SSIS package and I wanted to execute them dynamically using Script Task. But i'm just a newbie in VB.net codes so i'm not sure if this is possible and if yes, how to do it.
Please help.
Thanks!
March 5, 2010 at 12:42 pm
One, you really can't using the method you describe, however, you may be able to accomplish the same thing another way..
Why don't you explain in more detail the problem you are trying to solve, be sure to include the criteria that would trigger running one. We can probably come up with a workable solution for you.
CEWII
March 5, 2010 at 2:07 pm
I have one SSIS package that have a parameter "clients" This package basically extracts data from a data source and dumps it into a temp table for every client every end of the day.
What I wanted to do is to run this SSIS package concurrently for every client.
So for example I have 5 clients, I wanted this SSIS package to run concurrently every end of the day without creating 5 separate jobs for each client.
There's a requirement also that if the package fails for one client, this should not affect the others.
I hope i'm clear enough.
thanks a lot!
March 5, 2010 at 2:32 pm
Ok, I have some thoughts and comments.
It sounds like you have a process that an SSIS job populates a table, you called it a temp table, is it a true temp table or it is a regular table that just doesn't contain persisted information. The reason it is important is that SSIS has some trouble with temp tables and only works a little better with global temp tables. If it uses temp tables I would recommend a drop/create process of a non-temp table in tempdb in the SSIS package the fills it. You can cleanup the table at the end.
As far as all concurrent. That is a bit tougher, I see several issues. The first is that a package structure is set at design-time and doesn't chage at run-time, a lot of settings can change at run-time but the components are set. You could potentially build a script that instantiates a bunch of different command shells to call an SSIS package a bunch of times, but that approach would be a HUGE hassle to write and debug.
That kind of leaves us with a different approach. In this approach you decide up-front how many concurrent processes you are going to have running at one time. You build a process that takes the work and splits it up among the available concurrent processes. I'm working on a sample since this is the third time I've had this come up in two months and it is no fun to explain.
CEWII
March 5, 2010 at 2:43 pm
as for the temp table, yes it's a global temp table.
As for the runtime, i think there will be no changes in the package structure/components.
I will be looking forward for that sample - it will be of great help to me and all SSIS devs out there. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply