[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Passing a param or syntax before SSIS package to run

Posted on 2008-11-18
31
Medium Priority
?
334 Views
Last Modified: 2013-11-10
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!!
0
Comment
Question by:chrisdavis5
  • 14
  • 11
  • 5
30 Comments
 
LVL 17

Accepted Solution

by:
HoggZilla earned 1004 total points
ID: 22990707
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
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 996 total points
ID: 22992992
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
 

Author Comment

by:chrisdavis5
ID: 22997436
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Expert Comment

by:PedroCGD
ID: 22997591
HI Chris
We are here to help!
You are welcome. :-)
regards!
Pedro
0
 

Author Comment

by:chrisdavis5
ID: 23014439
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23014534
attach images of your package here to understand your issues
0
 

Author Comment

by:chrisdavis5
ID: 23014685
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23015094
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
 

Author Comment

by:chrisdavis5
ID: 23016003
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
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 996 total points
ID: 23017528
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
 

Author Comment

by:chrisdavis5
ID: 23017626
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23017681
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
 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 1004 total points
ID: 23017793
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
 

Author Comment

by:chrisdavis5
ID: 23017799
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23017856
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
 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 1004 total points
ID: 23017890
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
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 996 total points
ID: 23017907
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
 

Author Comment

by:chrisdavis5
ID: 23018821
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
 

Author Comment

by:chrisdavis5
ID: 23019163
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23020153
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23028397
Improvements!?
0
 

Author Comment

by:chrisdavis5
ID: 23029247
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23030787
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23035113
improvements? :-)
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23049939
HIChris,
Could you give feedback to close this question?
regards,
Pedro
0
 

Author Comment

by:chrisdavis5
ID: 23070250
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23076023
ok!!!
Regards!
Pedro
0
 

Author Comment

by:chrisdavis5
ID: 23217192
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23219641
ok!! Cheers!
pedro
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23225777
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question