October 7, 2014 at 3:59 pm
I am trying to create a SSIS package that will create a csv of a dataset for daily events in the database. However there will be days that there was no activity and thus an empty dataset. The package still runs fine but I want to stop the package if the dataset is empty.
FLOW:
DATA FLOW task: get daily data and put in CSV file
FTP TASK: upload the file to FTP server
MAIL/Copy file task: Move the file and then send a confirmation mail on task completion status.
Pretty simple and it all works great, I do have a few complexities in there but you get the idea. What I would like to add and I am at a loss is at the beginning, if the OLE DB Task resultset is empty then move to Mail Task otherwise process normally. I have tried conditional split, derived columns, the only thing I haven't tried in Script task and am not sure about that yet. Any help or advice would be appreciated.
October 7, 2014 at 4:04 pm
Simply put, you can't (at least up to 2k8), which is why you're having trouble with it. The Data Flow task is actually a single component, it doesn't have logic flow internally.
There is only one way around this I'm familiar with, and it requires touching your data twice. You'll need to create a count() query to return to a local variable (no columns, just the same where clause and joins), and then change your Precedence Constraints to be Constraint and expression. You'll have two routes out of that Execute SQL task, one with @var == 0, and another with @var != 0, both success routes. The one ==0 goes straight to your email task, the other leads towards the dataflow.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 8, 2014 at 1:32 am
omakler (10/7/2014)
I am trying to create a SSIS package that will create a csv of a dataset for daily events in the database. However there will be days that there was no activity and thus an empty dataset. The package still runs fine but I want to stop the package if the dataset is empty.FLOW:
DATA FLOW task: get daily data and put in CSV file
FTP TASK: upload the file to FTP server
MAIL/Copy file task: Move the file and then send a confirmation mail on task completion status.
Pretty simple and it all works great, I do have a few complexities in there but you get the idea. What I would like to add and I am at a loss is at the beginning, if the OLE DB Task resultset is empty then move to Mail Task otherwise process normally. I have tried conditional split, derived columns, the only thing I haven't tried in Script task and am not sure about that yet. Any help or advice would be appreciated.
Add a RowCount component to your data flow and let the file get created (empty or not).
Use precedence constraints after the data flow to control which tasks execute next, based on whether the variable you have set in your RowCount component is greater than 0.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 8, 2014 at 8:02 am
I was thinking the same thing last night. I will have to give that a try. How would you go about the first case in 2012, ie is there a better way than a data flow?
October 8, 2014 at 8:41 am
omakler (10/8/2014)
I was thinking the same thing last night. I will have to give that a try. How would you go about the first case in 2012, ie is there a better way than a data flow?
Not that I can think of – though what do you mean by 'better'. Did you have some idea in mind?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply