1

I have a Stored Procedure in SQL Server starting like below:

CREATE PROCEDURE [dbo].[spReporting] 
   @DateCreated_Start datetime,
   @DateCreated_Finish datetime,
   @DeadlineDate datetime = NULL,
   @Duration [NumericTableType] READONLY,
   @ValueCheck [StringTableType] READONLY
AS
BEGIN
....
END

As to be seen, there are two custom types a variable can have, NumericTableType and StringTableType.

What I want to do is to execute the stored procedure in SSIS using an "Execute SQL Task" in Control Flow. The execute command (SQL Statement) I use is: exec spReporting ?,?,?,?,? However, I don't know how I should give the parameters since the custom parameters are not defined in "Parameter Mapping" section.

I tried changing the SQL Statement like: exec [dbo].[spReporting_AP] ?,?,null,null,null, but didn't help. I don't want to use a script task for this, unless I have to. Any advice/help would be appreciated.Thanks.

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82

2 Answers2

0

Are the two custom types both table types? If so, you can use a global temp table that's loaded in a preceding task in the package to populate a variable of the same type, and then execute the stored procedure with this variable. In order to have the global temp table accessible to other components in the package, the TransactionOption must be set to Required on the package, and either Supported or Required on the objects involved. If you want to isolate this work to a particular part of the package, place all the components involved in the same Sequence Container and apply the same TransactionOption settings there. You won't need to use parameters to do this, and the following example illustrates executing the stored procure after populating a variable of the same type.

DECLARE @TableParameter AS YourTableType

INSERT INTO @TableParameter (Column1, Column2)
SELECT Column1, Column1 FROM ##TempTable

EXEC dbo.testsp @TableParameter
userfl89
  • 4,610
  • 1
  • 9
  • 17
0

Some approaches to implement:

  1. Initialize Custom type variables inside SQL statement. This is done by storing data for custom type variables in some SQL tables before executing SP. Then create an Execute SQL task which:
    • Create variables of Custom type
    • Fill in the variables with SELECT INTO statements
    • Executes the SP

Passing parameters like DateCreated_Start could be done with technique of String Variable dynamic evaluation with expression.

  1. Script Task calling SP with all parameters etc.
Ferdipux
  • 5,116
  • 1
  • 19
  • 33