June 2, 2011 at 12:47 pm
I'm moving some DTS Packages to SSIS. I have one, where I'm exporting from a table to a flat file, that on occasion will not have any data to export. In SQL2000/DTS I have a procedure that does an If Exists on the table, then uses xp_cmdshell to call DTSRun when there is data to export. I could do the same here, but I was hoping to not use xp_cmdshell.
I put a SQL Task in front of my Data Flow Task to check for data, but I can't figure out how to set a variable and then use that variable to either quit or let the package continue to the Data Flow.
I added a variable send, int = 0 that says it's scope is the whole package. My SQL Task is: If Exist (select * from MyTable) select send = 1. But I can't figure out how to test it.
Thanks.
June 2, 2011 at 1:32 pm
Randy Doub (6/2/2011)
...but I was hoping to not use xp_cmdshell.
+1 😀
I put a SQL Task in front of my Data Flow Task to check for data, but I can't figure out how to set a variable and then use that variable to either quit or let the package continue to the Data Flow.
I added a variable send, int = 0 that says it's scope is the whole package. My SQL Task is: If Exist (select * from MyTable) select send = 1. But I can't figure out how to test it.
Double click the green line connecting the Execute SQL Task and the Data Flow Task and change the Evaluation operation field from Constraint to Expression and Constraint. Leave the Value at Success and add an Expression to test your variable value.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 2, 2011 at 3:18 pm
Actually that is pretty easy and opc.three is on the right track.
CEWII
June 2, 2011 at 3:21 pm
Elliott Whitlow (6/2/2011)
Actually that is pretty easy and opc.three is on the right track.CEWII
Gee, thanks CEWII :blush:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 2, 2011 at 3:23 pm
opc.three (6/2/2011)
Elliott Whitlow (6/2/2011)
Actually that is pretty easy and opc.three is on the right track.CEWII
Gee, thanks CEWII :blush:
I had already started my response when I noticed you were already there.. So I just said you were right..
😛
CEWII
June 2, 2011 at 3:27 pm
Thanks, but it only gets more confusing. I created variable send, int32 = 0. Not @send. Try to add variable @send gives error messgage "must begin with alpha or underscore". So variable is send.
SQL Task query is "If exists (select * from mytable) select send = 1". And this parses without error.
I add the Precedence Constraint and click the Test button and get error message "If send is a variable it must be expressed as @send. So my expression is @send==1.
This is correct?
June 2, 2011 at 3:38 pm
I should add, this executes, but only does the SQL Task. I've set up my test with data in my table, so I would expect the Data Flow to execute. The SQL Task runs to completion, but it does not pass the test and go on to the Data Flow. I think there are two seperate variables being set and checked.
June 2, 2011 at 3:55 pm
Elliott Whitlow (6/2/2011)
opc.three (6/2/2011)
Elliott Whitlow (6/2/2011)
Actually that is pretty easy and opc.three is on the right track.CEWII
Gee, thanks CEWII :blush:
I had already started my response when I noticed you were already there.. So I just said you were right..
😛
CEWII
All good 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 2, 2011 at 3:57 pm
Randy Doub (6/2/2011)
I should add, this executes, but only does the SQL Task. I've set up my test with data in my table, so I would expect the Data Flow to execute. The SQL Task runs to completion, but it does not pass the test and go on to the Data Flow. I think there are two seperate variables being set and checked.
How confident are you that you're binding the results of the Execute SQL Task into a variable?
Please don't post any sensitive data or IP, but could you post the SSIS pkg as an attachment? I may not be able to run it with your database, but I can eyeball the settings to see if I see anything off center.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 2, 2011 at 3:58 pm
The output variable from the EXEC task needs to be the same name and scope of the variable that is tested in the precedence constraint between the EXEC SQL Task and the Data-Flow.
You might click on the show all variables and see if you might have the same variable in more than one scope.
CEWII
June 2, 2011 at 4:47 pm
The Result Set property on my SQL Task was set to None. Must be by default 'cause I didn't change it. Anyway, after a couple of hacks at it I got it to run. I changed Result Set to Single Row, then on the Result Set options page, Result Name had a default value of "NewResultName" and the Variable Name column had "User::send". I changed the Result Name to "send" and the darn thing worked.
If I set the test up to not export my SQL Task failed - error valuing variable send. So I changed my SQL Task statement to If exists (select * from mytable) select send =1 else select send = 0". And then it worked, whether there was an export to do or not.
So it seems to me that while I defined a variable User::send and set it's initial value to 0, it's not really what is getting tested in the sql statement, or in the constraint. I had to... I don't know what I had to do... move User::send to something else that the SQL Task and Precedence Contstraint could understand/find/test?? Much like a derived column or data conversion? It shouldn't be this hard to use a variable.
Anyway, while I have a a number of SSIS packages, most just import or export data to and from flat files and spreadsheets so I don't get exposed to much SSIS depth. I never would have figured this out alone, so thanks very much.
June 2, 2011 at 4:55 pm
Happy to assist, thanks for posting back!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply