Passing a param or syntax before SSIS package to run

I am writing an SSIS package and control and dataflows.  I have my control flow to run with SQL just fine with my OLE DB source and SQL Server Destination.

I want to be able to pass a param or more like an "iif" statement.  For example, only get this data IF the date is GETNOW()-1 > table1.datetime (or attached to a column name in a table as in example).

Example:

Iif the date is greater than or less than [table1].[datetime] then RUN THE PACKAGE if not, DON'T RUN.  In the end, I want to only bring changed records into the SQL Server Warehouse Database if they exist.

Thank you for all your experts so far!!
chrisdavis5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
Create a Variable to hold the decision, call it Decision. Make it a String Data Type. Do this in the SSIS, Variables window.
Set the Variable to YES or NO based on an Execute SQL Task that checks if the date criteria is met. I have attached a document on populating variables Refer to the SQL Task portion.
Then, use a precedence contraint to evaluate the Variable value and whether to continue or not. To create a precedence constraint, Connec the Execute SQL Task by arrow to the Data Flow Task. Then right click on the arrow, choose Expression and Constraint. See pic below.

precedence-constraint.bmp
SSIS-Populate-Variable.pdf
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PedroCGDCommented:
Dear Chris,
Follow the answer of HoggZilla.
Only one suggestion... if you are populating your FactTable in the Datawarehouse, be carefull about the validation you are trying to do. Because [table1].[datetime] means that you will make a SELECT MAX(DateTime) from Table1, is that true? IF the table is larger you will decrease a lot the performance... maybe could be more interesting adding a audit table.
Regards!
pedro
0
chrisdavis5Author Commented:
Wow thanks that's excellent detail!  Let's see I am having trouble connecting to Oracle.  Once I do that and test it I will give you points if I don't have any other questions.  Thanks guys!
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PedroCGDCommented:
HI Chris
We are here to help!
You are welcome. :-)
regards!
Pedro
0
chrisdavis5Author Commented:
hi guys.  so I called it "decision" and wrote a msgbox like in your example on page 14 of the PDF.  Now do I just run the package?

I set the value of the string of "decision" variable to "Y", was not sure what to put there.

In the script task I put under the ReadOnlyVarilables the value of "Decision" and designed the script with the msgbox you had.

On the result Set on the Execute SQL Task Editor I put the Result Name as value of "0".

Now do I just execute the package?  Do I drag the green data lines from script to SQL Task?
0
PedroCGDCommented:
attach images of your package here to understand your issues
0
chrisdavis5Author Commented:
I know my SQL Statement in the Execute SQL Task is not a real statement yet, just putting the logic in there.

Basically I want the script to run IF the date in my TPA tables is > the date in my Master Table.

Then I want it to load if it is TRUE, NOT load if it is FALSE.

Do I have my flow and variables correct?
Doc5.doc
0
PedroCGDCommented:
in the link row from the script to sql task, follow the suggestion of HoggZilla in the first answer.
Right-click in the row and use the expression property of evaluation criteria!
Helped?
regards!
0
chrisdavis5Author Commented:
Awesome..I wasn't following the precident contraint properly.  It turned blue when I got done with it.  Should the value of "Y" in the variable Decision be the same as in the Precedence Constraint Editor in the Expression (i.e., @[User::Decision]=="Y")  I tested the evaluation and it passed.

What if I need two data sources?  For example, an Oracle source for the first date and a SQL server for the other date?  In the SQL Statement in the Execute Task Editor it only gives me one connection?

You guys are so on top of it and super smart.  I appreciate all your replies.
0
PedroCGDCommented:
ok.... I promise I will not leave this question until you resolve your problem.
I made a simple package... update the connection manager to sql database...
now... tell me what is your issues and I will go from my package until final solution, ok?
regards!
Pedro

Package-EE52-dtsx.txt
0
chrisdavis5Author Commented:
Pedro!  Thanks man!  Let's see I need to query the date on an Oracle Server against a date on a separate SQL server.  Do I write a new data flow somehow for that?

My query may looking something like

WHERE MAX(oracle_date) (server 1) > MAX(SQL_Server_Date) (server 1)

If the statement is true, I need the package to run, if not, don't run.

I think my Script Task and Execute SQL Task Populate Variable Decisions are closer to being correct.
0
PedroCGDCommented:
You should do the validation inside database...
or you want something like:


WHERE MAX(oracle_date) (server 1) > MAX(SQL_Server_Date) (server 1)
server1 and server1 in Oracle and SQL???

You want to get from database to ssis the lastdate in SQL Source and lastdate in ORACLE, correct?
and compare it inside ssis? if true execute if not dont do nothing?
0
Steve HoggITCommented:
I did also notice in my picture above that the Evaluation option only says Expression. But you need to set yours to Expression AND Constraint. Sorry bout that.
0
chrisdavis5Author Commented:
No, server1 in Oracle on a different box, server2 on a SQL server on a different box.

Two different boxes two different data sources

table1.date1.server1 against (>) or (<) table2.date2.server2

I was trying a data source view but SSIS doesn't seem to have that like AS does.

Yes if the SQL statement is true, then execute, if not, do nothing or don't run the package.
0
Steve HoggITCommented:
What you need to do is grab each date seperately. Then you can create a Script Task to do the comparison and assign the value to your Decision variable
0
Steve HoggITCommented:
In the script task pass in the two date variables as ReadOnly and the Decision variable as ReadWrite.
So you have an Execute SQL task that gets the Oracle date and another Execute SQL Task that gets the date from SQL Server. Look at the code below for a script task.

dim OracleDate as datetime
dim MSSQLDate as datetime
 
OracleDate = DTS.Variables("MaxOracleDate").Value
MSSQLDate = DTS.Variables("MaxMSSQLDate").Value
 
If OracleDate > MSSQLDate Then
 DTS.Variables("Decision").Value = "Y"
Else
 DTS.Variables("Decision").Value = "F"
End If

Open in new window

0
PedroCGDCommented:
you wrote wrong and for that reason`I asked again..:-)

server1.table1.date1   > DATEA
server2.table2.date2   > DATEB

OK... You use SQL task to get each date (DATE A from server1 and DATE B from server2), ok?
You store each date in a ssis variable using the resultset of each SQL Task... ok?

Now, you have 2 ssis variables... uvDATEA and uvDATEB, ok?


Package-EE52b-dtsx.txt
0
chrisdavis5Author Commented:
you guys are terrific..I am at home now unfortunately and cannot get back to work until Monday morning to test this out.  I will post this as soon as I can test and get back to you.  I may have final questions and screenshots I can send to make sure it's correct!!

Thanks again guys and I will respond on Monday so be watching for my reply!!
0
chrisdavis5Author Commented:
Ok okay so I save the results of each SQL statement in the variable then execute the variable based on the results?  I think I may be getting this with your help!
0
PedroCGDCommented:
You have the result of each that in each variable, and then in the row that links to dataflow, you add the condition expression @uvDateA>@uvDateB
Helped?
;-)
Regards!!!
Pedro
0
PedroCGDCommented:
Improvements!?
0
chrisdavis5Author Commented:
haha!  Thanks for checking on me.  I have set up the new script based on what you guys told me.  Attached is a screen shot.

In the Result Set of Each (2) SQL Tasks, it asks for Result Name and Variable Name.

Do I make those SQLServerDate and OracleDate as new varilables?  Or use ONE variable, the variable of "Decision?"

Do I make the precident contraints normal?  They are green now.  Is my control flow correct?

I do not have any expressions in this flow.  I followed HoggZilla's script task and implemented it in the script.
Doc1.doc
0
PedroCGDCommented:
ok... my solution is not using the script. You can choose using a script, but I suggested another alternative.
Check the package i attached and see carefully to understand it, and give me feedback.
Regards!
pedro
0
PedroCGDCommented:
improvements? :-)
0
PedroCGDCommented:
HIChris,
Could you give feedback to close this question?
regards,
Pedro
0
chrisdavis5Author Commented:
Hi guys..still working on this wanted to let you know..I'm having some Oracle connection issues now and will test this soon!!
0
PedroCGDCommented:
ok!!!
Regards!
Pedro
0
chrisdavis5Author Commented:
Pedro and Hogg..I'm STILL working on this issue.  I've got most of the script written but haven't implemented it yet.  As soon as I do I will update/close the question and assign points.  I may have additional questions but not until I come to the point in the script where I have to use this so keep hanging with me!!
0
PedroCGDCommented:
ok!! Cheers!
pedro
0
Steve HoggITCommented:
Great! Let us know.
It is also OK to close this question and open another, that will allow other experts to get involved if needed. If you feel like this particular question is answered, then assign a solution so others can reference.
Hogg
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.