How do I create an output file only if source query returns rows

Question

Post reply

How do I create an output file only if source query returns rows

fstop

Default port

Points: 1482
More actions
June 27, 2008 at 10:50 am

#189865

I would like to get some ideas on the best way to only create my Excel output file if my source query returns rows. Would a Conditional Split work after the OLE DB Source task? If so, would I also need to include a Row Count task?

Viewing 5 posts - 1 through 4 (of 4 total)

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

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

Look at this topic, this is what you want:

http://www.sqlservercentral.com/Forums/Topic522708-148-1.aspx

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

fstop Default port Points: 1482 More actions · Answer 2

Thanks, Sean. Your video was excellent, and a great learning tool. It really helped in understanding how to formulate my solution.

I received one error that I was hoping you could make sense of. In the Script Task, I am comparing 2 variables created in the 2 prior "Execute SQL Task" steps. I want a Success if CNT2 > CNT1, however that statement produces an error during the script compile: "Option Strict On prohibits operands of type Object for operator '>'" The exact statement is: "If Dts.Variables("CNT2").Value > Dts.Variables("CNT1").Value". Do you know where I would turn Option Strict Off, assuming that is the best solution?

Thanks.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

I think you've basically got 3 options:

1. Set option explicit off. you can do this at the top of the script above the import statements by typing "Option Explicit Off"

2. Cast your vars as a different data type. So something like CInt(DTS.Variables("CT1").value)

3. Why are these object types in the first place? If they're holding a single value, which I suspect they are, then why not create them as the

type you need to begin with, then you won't have this issue?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

fstop Default port Points: 1482 More actions · Answer 4

Thanks for your suggestions. Actually, adding "Option Strict Off" worked for me. I've never seen that command, but it took away the error.

Regarding your 3rd suggestion, I didn't do anything that I can tell to force the compiler to perceive it as an Object. I followed the directions in your video to a T and that's the way it showed up. Go figure!

Thanks again.