Conditional Split
-
I have a simple data flow task taking the output of a query to a text file. Works perfectly except I don't want a file created when the rowcount is 0. So, I added a variable and a Row Count transformation to populate the variable. Then I added a conditional split with a condition that if the rowcount variable is > 0, go to the data flow destination.
Do I need anything else or will this just know that if it is 0 to exit the data flow task?
-
Minor update to our process:
We just (in the last 30 minutes) decided it would be good to have an exceptions file that tells us when no records were exported. So, I changed my source so that if the record count is 0, it sends a single record that begins with "No records exported..."
I setup the conditional split to send this "No records" record to the exception file and then the "default" of the conditional split to the original file so we get our records output when they are available.
It seems to work great but... We don't want the "default" file to be created if there are no records. This process is creating the file even when there are no records to export. Am I going to have to do something in the main control flow of the package to determine whether or not to even run the data flow task? If so, I'm at a loss to know what since I don't see an equivalent to the Conditional Split in the data flow task.
This is my query for the source:
DECLARE @RecordCount intSELECT @RecordCount = COUNT(sendrec)
FROM WFDailyView
WHERE (DATEDIFF(dd, pdate, GETDATE()) = 0)
IF @RecordCount > 0
BEGIN
SELECT sendrec
FROM WFDailyView
WHERE (DATEDIFF(dd, pdate, GETDATE()) = 0)
ORDER BY acctnum, rectype
END
ELSE
BEGIN
SELECT 'No records exported for ' + CAST(GETDATE() AS VARCHAR(50)) + '.' AS sendrec
END
-
An easier way would be to attach a function to the control flow...
so where you've got your green arrow coming from the bottom of your rowcount task, you could right-click on the arrow and go to properties. then change it to an expression. that way you can say like user::rowvar > 0... that tells SSIS to only go to the data flow if your rowcount variable is > 0.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
The green control flow arrow coming out of the Row Count transformation does not have the ability to change it to an expression. I had to search for a while before I found the ability to make it an expression in the main control flow of the package rather than inside the data flow task.
The OLE DB Source of the data flow task is based on the query posted above. I need a way to direct the output (exception or actual data) to the appropriate file but not create the data file if no records are exported. The conditional split transformation seems to be the best option but it still creates the data file even if it doesn't have anything to put there.
-
Perhaps run the query a count(*) in a sql task beforehand and only go to the data flow if the count is 0... would that work for you...
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Almost but not completely. I actually have three data flow tasks in this package so it seems that if I do a count and then don't proceed if the count is 0, that indeed would eliminate the file but then would eliminate all three files (unless there is a way to conditionally split execution of the package after the SQL Statement runs). All three files are generated (or not generated) based on the same criteria (record count but different record sets).
-
In SSIS there is a rowcount transformation, which will update a variable with the count of a particular data stream.
I would go for that, and add an expression
~PD
-
How do I add an expression inside the data flow task? It appears I can only add the expression in the control flow of the package and the rowcount transformation is only available inside the data flow task. I'm at a loss here...
-
Inside the dataflow you will have the rowcount which will update the variable.
Inside the controlflow you will specify not to run if the rowcount variable is equal to zero
-
So, I will actually have to have two data flow tasks in the control flow? The first to do the rowcount and populate a package scope variable and the second to do the actual extract with the expression tying the two together?
-
Correct
First data flow simply checks if there is data, and populates this check to a variable.
Second data flow is only executed if first one returns favorable results, and does the actual transfer.
Make sense?
~PD
-
Makes perfect sense. Now I just have to figure out how to make it work - to the books! I'll be back if I can't figure it out.
Thanks!
-
Only a pleasure man.
Both these pieces of functionality are really simple to use, and most effective for what you are trying to achieve
~PD
-
That's almost exactly what I said... whatever...
And I also believe I said that you could count the rows ahead of time both with the row count and the exec sql task. I think the sql task is more efficient because you're letting the SQL engine do all the counting and you're just returning an int to the package which you'll turn around and assign to a var in the sql task. Then you can do whatever data flow based off of those results.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Agreed, exec SQL would be the fastest, neatest method
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply