CODEWITHSUNDEEP
sqlsql-serverssis
Neo
Neo

Reputation: 16219

Parameters not getting loaded in run time in SSIS package

I have simple SSIS package. My source is sql command as follows:

  Select * from table1 where col in (select col from table2 where col3='VAL')

  For VAL I created the variable. and for subquery I created one more variable.

  Variable1=VAL

  variable2=select col from table2 where col3=Variable1

So my source is as follows:

  Select * from table1 where col in (variable2)

When I run the package, this is not replacing. Seems to be the Variable2 is not loading properly.

Please advice.

Upvotes: 0

Views: 1253

Answers (1)

user756519
user756519

Reputation:

First of all, if col is the joining column between table1 and table2, you can rewrite the query as shown below:

SELECT      *
FROM        dbo.Table1  T1
INNER JOIN  dbo.Table2  T2
ON          T1.Col      = T2.Col
WHERE       T2.Col3     = 'VAL'

I assume that you are using this query in the OLE DB Source or ADO.NET Source to read it using SQL Command option.

So, put your query in a stored procedure as shown below:

CREATE PROCEDURE dbo.GetData
(
    @Param1 NVARCHAR(50)
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT      T1.col
            ,   T2.col3
    FROM        dbo.Table1  T1
    INNER JOIN  dbo.Table2  T2
    ON          T1.Col      = T2.Col
    WHERE       T2.Col3     = @Param1
END

Create two SSIS package variables to store the stored procedure execution command and also the parameter value to be passed.

Variables

On the properties of the variable StoredProcedure, set the property EvaluateAsExpression to True and set the Expression to "EXEC dbo.GetData '" + @[User::Parameter] + "'"

Expression

Use the variable StoredProcedure as shown below to pass it as a variable to the OLE DB Source. Whenever the value in the variable Parameter changes, the stored procedure execution command will change accordingly.

OLE DB

Upvotes: 1

Related Questions