Validation Task / Step

Question

Validation Task / Step

BAGs

Right there with Babe

Points: 793
More actions
November 16, 2010 at 10:22 am

#229329

Hello All.
I have process set up that utilizes a combination of execute sql tasks and data flow tasks.
I utilize some staging tables in my database and subsequently process and move data into multiple locations that are final. One of my steps is to truncate data in the staging tables before loading new data. The data i collect is from an external source (DB2 or AS400).
I want to do a validation step before truncating. 2 things can happen in this situation.
1. data may not be updated in the source data.
2. the source database may be unavailable.
My steps are basically:
TRUNCATE
LOAD
So #1 isn't a big deal to me.
If #2 exists, i truncate and then LOAD nothing because the connection fails.
My thoughts were this: (Logically)
execute a sql statement that returns records. if records > 0 then i'm good.
if records are 0 or then don't truncate.
i could check the value of connection.state
How can i pull this off in SSIS??
Script Task?
Other?
And how do i stop the package from continuing based on meeting a condition??
Thanks in advance for your help.
Peter

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

I think the easiest way to do this is to perform some sort of check (either with the execute SQL task or with a script task that validates the connection, as you proposed yourself), but the important point here is to store the result in a variable. Preferably a boolean variable.

Then, after the check, use a precedence constraint with an expression. If you do not know what that is, double click on a green arrow in the control flow. You'll get an editor window where you can configure your precedence constraint.

Anyway, construct a precedence constraint so that you only truncate when the connection exists.

If you have more questions, let me know.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

BAGs Right there with Babe Points: 793 More actions · Answer 2

Thanks for the response. Haven't got back to it but i do follow your suggestion. I'll let you know how i make out.