January 9, 2008 at 10:40 am
Hi,
I am exporting data using a dataflow tool running a query from an OLE DB source and kicking the records out to a flat file. It then moves on to an email job to say the records have been exported. Is there a way I can stop the file being created (and email being sent in the next job) if there is a record count of 0 in the selection?
Thanks.
January 9, 2008 at 11:56 am
I believe you can use the [Conditional Split] transformation and then [Send Mail Task]. This will easily solve your problem.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
January 10, 2008 at 3:03 am
Am I missing something ? As far as I can see the conditional split only allows you to perform splits according to column content. The only thing I can thinnk of here is to uise isnull on a column but although the rowcount is 0 it still creates the file. In fact now if I have a condition column1 is null and another condition column1 is not null going to seperate 'files' even though there are no records 2 files are now created!
Do you know if there is a way I can set the split to look at column count or more likely (?) if there is a way i can stop the Flat File Connection manager or flat file source from creating a file if there are no records ?
Thanks.
January 11, 2008 at 2:55 am
I think he means assign the row count to a variable or temp table and then use conditional split against that.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 11, 2008 at 3:14 am
I've set the variable using an ExecuteSQl with a rowcount and then used a script to set a success or fail based on the rowcount. Sadly the Fail then goes to another script that just sets the DTS job as a whole to Success !
It does the job though. I'd be interested if it could be done with a Split as its all interesting knowledge but my impression is that the split repeated the problem I was alreadyfacing - of carrying on regrdless. This is my first SSIS task though and I m,ay very wellbe wrong.
Cheers,
Ben.
January 11, 2008 at 3:58 am
Try using a temp table. you could do something like insert into #mytable (your already setting a parameter, so won't be much difference there.
then use the split to say if {column} = 0 clear the table (as a tidy up for the next run) and continue on to do what ever .
and case when {column} > 1 go and create the extract file and send the email
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 11, 2008 at 5:12 am
Okay I think I see now.
I've had a bit of trouble with the temp table aspect - I created a temp table in an OLE DB Source query wioth a count column but for some reason I couldn't get it to see the column in the split (or in the output columns tab of the Source) - even if I followed the creation of the Temp table with a select. I'm sure I'm missing a step there ?
Anyway if I did the select without inserting to the temp table and fed into the split I think I achieved the same effect that you proposed.
Thanks.
March 5, 2008 at 12:46 pm
Another really easy way to achieve this is through the use of SQL Server Reporting Services. I have several reports are outfiled to a csv and then it emails me certain info I wanted to know about the export. All really simple to setup and keep functional. I wouldn't setup SSRS just for one activity like this but if you already use it for other scheduled reports and data transfers, then I would say just implement this via this utility.
anyways just a thought
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply