SQL Server Performance Forum – Threads Archive

Execute SSIS from Sproc

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<img src=’/community/emoticons/emotion-43.gif’ alt=’:unsure:’ /> 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!!

Make sure you are running dtexec instead of dtsrun if this is a true SSIS package
dtexec /DTS "MSDBProcess HubPDS" /SERVER msisql /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V Also, you don’t have to migrate everything to SSIS, you can bring it in as a legacy DTS package under the management section.

]]>