Share via

Convert SSIS Packages to MSSQL Stored Procedures

Michael Henderson 111 Reputation points
2020-09-14T20:23:33.703+00:00

I have been tasked to convert a SSIS package to stored procedures.

The package runs in less than 60 seconds.
The package builds staging files from DB2 to MSSQL and then runs the MSSQL Merge statement.

Unfortunately, if I try to use an OPENQUERY to update a staging file from DB2 to MSSQL it can take up to 2 minutes.

Any thoughts?

Thank you.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,486 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2020-09-15T02:03:50.697+00:00

    Hi @Michael Henderson ,

    Please use the following methods to improve the functionality and output of SSIS catalog package executions invoked from T-SQL.

    1.Execute Packages Synchronously
    2.Force a Script Failure if the Package Fails

    Please refer to A Better Way to Execute SSIS Packages with T-SQL and Run an SSIS package from SSMS with Transact-SQL.

    Best Regards,
    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.