Conditional checks in SSIS.

I have two connections in a package pointing to two different databases
on the same server. I have to insert records from 'DB1' table 'Gender1'
to 'DB2' table 'Gender2'. Before I do that though, I have to make sure
the minimum value (of all the Gender Keys that are going to be
inserted) of 'DB1' 'GenderKey' (which is an identity field) is greater than the
maximum value of DB2-GenderKey (which is a primary key but not an
identity field). How can I do this simple check? I have to do this process for many different tables ....... Gender table is just an example. If someone can give an detailed explanation on which tasks to use and how to use them (as I am relatively new to SSIS) that'd be great.So you want to move the rows from DB1 that are newer than what you have in DB2.

You should use an Execute Task in the Control Flow to get the ISNULL(MAX(GenderKey) ,0) from DB2 and place that in a variable. Have another variable with EvaluateAsExpression=True that builds a SQL statement with your MaxGenderKey variable to get any rows where GenderKey in DB1 is greater than the MaxGenderKey from DB2. Set the data access mode of your OLE DB Source to "SQL command from variable" and specify this expression-based variable.
|||actually, i do not want to do insert at all. If the condition check is successfull, then do the insert otherwise abort the package? How do i do that?|||You could certainly do that. You'd get the MaxGenderKey from DB1 as well and compare them in a precedence constraint to prevent any further execution.

Unless you're changing your original purpose of inserting new records from DB1 to DB2, it seems simpler to just let it try. If there aren't any, then it won't find anything to do.
|||

Use one Execute SQL to get the Max value from DB2, and one to get the Max value from DB1. Store the values in variables, as Jay described. Then have a data flow to do the actual inserts. Have a precendence constraint between the 2 Execute SQL tasks, and one between the 2nd Execute SQL and the data flow. Right-click on the precedence constraint between the 2nd Execute SQL and the data flow task, and change the Evaluation Operation to Expression and Constraint. Leave the Value as Success, and change the Expression to compare the variables you created (something like MaxIDFromDB1 > MaxIDFromDB2). Execution will only flow to the data flow if the expression evaluates to TRUE.

Exec SQL > Exec SQL --(expression goes here)-> Data Flow

|||

I think I might be getting what you guys are trying to convey.

So here is what I did.

I added a Sequence Container.

1. I added a Execute Sql Task to it which brings the GenderKey from Prod and puts it in a Variable using the following query ..

SELECT [User::GenderKey_Prod] = ISNULL(MAX(GenderKey) ,0) FROM dbo.DIM_Gender

2. I added another Execute Sql Task which brings the GenderKey from Staging and puts it in another Variable using following query ..

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

I added the connector from Prod to Stg (1 -> 2).

3. Then added another data flow task (which will actually transfer the data).

4. And added a connector from STG to this data (2 -> 3) flow task and on this connector I defined the expression check @.

[User::GenderKey_Prod] < @. [User::GenderKey_Stg] which if true, will execute the data flow task.

All this makes sense, however, I am having troubles on step 2. Where condition task a parameter called TaskId, this parameter is a

Package Level parameter I have added. I did the parameter mapping, but it keeps giving error on this step says 'Parameter Name is

Unrecognized'. Can you see anything wrong that I have done in Step 2 while defining runtime parameters?

|||

Ok, I think I found the issue. The Parameter name in mapping section has to be 0.

But I still have an issue, now the package is running and executing step 1 and 2, after than its just not executing step 3 even if the expression is true. How can i check the values of the variables I assigned value to in step 1 and 2 ?

|||

ASOOD wrote:

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

On the Parameter Mapping page make sure you specify 0 for the Parameter Name to represent the first parameter.

I'm not too sure about your SELECT syntax. You know you have to set the ResultSet to Single Row and make an entry on the Result Set page? You'll specify the output column name (which in your query above is [User::GenderKey_Stg]) and select the variable.

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).
|||Right-click on step 3 and add a breakpoint to the OnPreexecute step. Then run (debug) the package. When processing stops, you can go to the Debug menu and show the locals window. From there, drill down until you find your variables. Hit the run button to continue.|||

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?|||I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?|||

Phil Brammer wrote:

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?

Sorry, I should have qualified the statement. It will only happen if your datatype in SQL is not int. I was testing with a table that had a smallint and thus had to supply a CAST.
|||

ASOOD wrote:

I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?

Did you set the ResultSet to "Single Row" and create a mapping between the result column and the variable?
|||You guys are the best!! Works like a charm now. Thank you all.|||Thanks for the example as I was trying to do the same thing for the first time.