ASOOD
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.
Re: Conditional checks in SSIS.
JayH
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.
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.
Re: Conditional checks in SSIS.
ASOOD
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
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
Re: Conditional checks in SSIS.
JayH
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.
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.
Re: Conditional checks in SSIS.
jwelch
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
Re: Conditional checks in SSIS.
ASOOD
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
Re: Conditional checks in SSIS.
ASOOD
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
Re: Conditional checks in SSIS.
JayH
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).
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).
Re: Conditional checks in SSIS.
Phil Brammer
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.
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.
Re: Conditional checks in SSIS.
Phil Brammer
I have not had this problem... Can you post a repro
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
Re: Conditional checks in SSIS.
ASOOD
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
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
Re: Conditional checks in SSIS.
JayH
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.
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.
Re: Conditional checks in SSIS.
JayH
Did you set the ResultSet to "Single Row" and create a mapping between the result column and the variable
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
Re: Conditional checks in SSIS.
ASOOD
You guys are the best!! Works like a charm now. Thank you all.
You guys are the best!! Works like a charm now. Thank you all.
Re: Conditional checks in SSIS.
Dave Sundell
Thanks for the example as I was trying to do the same thing for the first time.
Thanks for the example as I was trying to do the same thing for the first time.