Post reply

Execute SSIS from Sproc - Extremely Frustrated

  • April 11, 2006 at 3:59 pm

    #112490

    I need to convert several DTS packages to SSIS...the migration wizard just doesn't do 100% of what I need...and I'm totally lost

    Here's what we currently have...

    We have one table that contains the properties of a particular Site in a single database (i.e. File Paths, Site ID's, Corresponding DTS package, etc).  There could be 5 Sites within a single database, and two of those sites require a different DTS package than the other 3 Sites to import the same type of data (i.e. Different formats for Sales/Service from different customer systems...but still within the same company...hence the same database)

    Currently in SQL 2000, we have a scheduled job that kicks off one Sproc which is a cursor that loops through each Site in a single database, which then kicks off another sproc that contains all the sp_OA commands that pass that site's properties/variables to a DTS package...this loop may run the same DTS package 3 times with different global variables each time, set by the 2nd sproc...then this loop reaches the 4th Site and passes that Sites properties/variables to a different DTS package.  Keep in mind there is only one scheduled job with a step for each database...that may or may not contain several Sites....with me so far??

    I'm having trouble executing the SSIS package from the sproc, and getting the SSIS to assign the appropriate variables.  Now I realize that the method that worked well in DTS/SQL2000 may not be the most efficient in SSIS/SQL2005...however, based off of our current procedure how could I transfer this into SSIS or build on this idea.  If someone could provide examples or links that they have found helpfull I would be eternally grateful!!

  • April 11, 2006 at 5:39 pm

    #631982

    Could you design one SSIS package to loop through all of the databases with a ForEach loop, then have separate SSIS packages that could use parameters from the parent to execute if certain conditions are true? You should be able to use some expressions to enable/disable the different packages based on what's in the table.

    I don't think the sp_OA commands will continue to work and I actually ran into trouble when they completely stopped working for me at one point - could never get them to fire again. The DTS worked as expected, but no more sp_OA with variables to call it, so I'm wary of that method anyway.

    Have you looked at http://www.sqlis.com for some examples of what they've been doing? I know they have examples of how to consume recordsets, though that's not the terminology they use. Calling a package within a package should be straightforward and mapping the parameters should be possible with a good expression.

    Of course, there's always the new book by Brian Knight and company that is probably worth a peek. 🙂

  • April 12, 2006 at 9:16 am

    #632120

    I think creating a separate package to loop through all DB's and execute the import package is the way to go, thanks for your help paschott

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply