CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduresparametersssis
Akane
Akane

Reputation: 13

call parameterized SSIS package from stored procedure

I’m using a SP for calling a SSIS package, I was previously using the

EXEC msdb.dbo.sp_start_job.

The problem is that I need to pass a parameter when I’m calling that package and I’m not able to do it. Example below:

ALTER PROCEDURE [dbo].[Clawback]
      @Country      CHAR(2)
    , @StartDate        DATETIME
    , @EndDate      DATETIME
AS

BEGIN
    BEGIN TRY
        TRUNCATE TABLE [dbo].[ClawbackConfig]
        INSERT INTO [dbo].[ClawbackConfig] 
(
             Country
            ,StartDate
            ,EndDate
            ) VALUES (
                 @Country
                ,@StartDate
                ,@EndDate
                )

EXEC msdb.dbo.sp_start_job N'ExecuteJob'

This Stored Procedure is for running an extract using the desired days and countries, these values are stored in one table and used in the usual incremental process.

I want to mention that the idea is to use the same package for the incremental + clawback and the incremental process needs the country parameter.

So I need somehow to pass the @Country given on this procedure to the package before it runs. The package is stored in Integration Services SQL Server 2008 so I’m not able to use the catalog.create_execution or cmd. Any ideas about this? Is the only solution to use C# ?

Many thanks.

Upvotes: 0

Views: 1861

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

There is no way to do it. You can either read from SSIS Configurations (in the called proc see:

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/execute-ssis-from-sql/)

or you store the values to a table that the invoked proc reads, and then, uses the read variables in the proc.

Upvotes: 1

Related Questions