SQL Server Performance

Execute SSIS from Sproc

Discussion in 'SQL Server 2005 Integration Services' started by fullomschf, Apr 11, 2006.

  1. fullomschf New Member

    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 />
  2. DStevensTN New Member

    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.

Share This Page