woensdag 31 maart 2010

Passing a parameter From a SSIS package to a storedprocedure and back again

Currently i'm developing a template package in SSIS for a new project. One thing that's important in my opinion is running package in an atomic way and his own environment (variables). So passing variables from a parent package to a child package is a no go for me. This would mean that you have to run the parent and the child to get a correct value of the variable. You can't run the package correctly when executing this separately. When running a lot packages you can determine a batchnumber once and pass it through from parent to child but this is a no goner (as is said)

So this is the case: i have a package in which i extract a table (Person) into a Import database and i want to log a batchnumber with every record, every logging and every error that could occur. So i created  SQL Task in which i execute a stored procedure and pass a tablename to the stored procedure and retrieve a proces run number back. This is the result

Below a screen dump of the SSIS Parameter mapping of the SQL Task:







And below you see the variable declarations




And this is the T-SQL code:


DECLARE @RC int

DECLARE @chvParDatabase varchar(100)
DECLARE @chvParTabelNaam varchar(100)
DECLARE @intParProcesNummer int
DECLARE @intProcesNummer int

EXECUTE @RC = [MetaServices].[dbo].[uspMetaServices_GetProcesnummer] ,?, ? OUTPUT

The problem with stored procedures, SSIS and the expression language it's hard to match the datatypes unfortunately. For instance the datatype in T-SQL is Integer, in SSIS int32 and in expression language is it a Long datatype. I tried the integerdatatypes in the parameter mapping screen but errors were my part.

Greetz,
Hennie

Geen opmerkingen:

Een reactie plaatsen