Execute SSIS from Sproc | SQL Server Performance Forums

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:’ /><br /><br />Here’s what we currently have…<br /><br />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)<br /><br />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??<br /><br />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!!<br />
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.