IS IT POSSIBLE ?--NOT to CREATE Flat File If it is empty?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » SQL Server 2005 Integration Services » IS IT POSSIBLE ?--NOT to CREATE Flat File If...

Add to briefcase

17 posts, Page 1 of 2

1 2 »»

IS IT POSSIBLE ?--NOT to CREATE Flat File If it is empty?

Expand / Collapse

Author

Message

Posted Monday, August 11, 2008 9:19 AM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

I have a SSIS packages that does a SQL command on OLEDB Source and finds the records and outputs to 7 different flat files based on conditional splits. Now i don't want to create a file if the record is empty for that type file.

OK. here is 7 differnt files type call 1,2,3,4,5,6,7. Now If type 7 file doen't have any records during the dataflow i just don't want to create a flat file which is empty. IS there anyway in SSIS to say don't create file if there si no record or Size is 0 kb.

Thanks,

Post #550334

Posted Monday, August 11, 2008 9:42 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:43 PM
Points: 609, Visits: 326

one approach, there is a row count data flow task that will add the row count value to a variable for your dataset and you can then perform another conditional split on that value being greater than 0 to hook into the flat file output path.

hth

Post #550371

Mark Beringer-730383

Posted Monday, August 11, 2008 9:43 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, January 06, 2011 6:44 AM
Points: 206, Visits: 363

You can write the record counts to variables and then in your Precedence Contraints use expressions that check if the variable is > 0. If not the Data flow task will not execute.

Post #550372

Posted Monday, August 11, 2008 9:48 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:43 PM
Points: 609, Visits: 326

mark, i think the issue here is that the decision to write to a flat file is occurring in the data flow, where you cannot use a precedence constraint within the data flow.

gyanendra.khadka, am i correct on that thought?

Post #550383

Mark Beringer-730383

Posted Monday, August 11, 2008 10:16 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, January 06, 2011 6:44 AM
Points: 206, Visits: 363

True, there are no Precedence Contraints on dataflow so my idea would have to happen in the control flow. I have not used your method before and I am trying it now but it still creates the file even though the condition is not met. Or am I still missing something?

Post #550409

Posted Monday, August 11, 2008 10:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:35 PM
Points: 6,650, Visits: 5,666

You could read the record sets to an ADO recordset in your dataflow, then use rowcount variables (there would have to be 7) in the same dataflow to count each record set. Once the dataflow ends, the rowcount variables would be set. Then you do your precedence constraints based on the variables being == or > 0. In the next step(s), have another dataflow that reads from the ADO recordsets into the appropriate files.

It's a little clunky, but it should work.

Another option is to use a FOR Loop container to check for records, set the variable and then do the dataflow task. But this is just an "off the top of my head" thought, so I'm not entirely sure of the details of setting it up.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #550425

Posted Monday, August 11, 2008 10:51 AM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

Guys sorry for late reply, got stuck in something.
Chuck U are absloutelt rt, in the datafllow i don't think we can we any precendence connstraints. I didn't get the Brandie response. Either it is too complicted or i am a fool here.
Mark ur approach looks ideal but again when u have a conditional split based on record count, so if record count is o what will be the destination....Don't tell me trash destination or another flat file..

Lets's again hope we are on teh same page

OLEDB Source
|
Conditional Split
| |
Conditional Split Conditional Split
| | | | |
1 2 Conditional Split 3 4
| | |
5 6 7

So If file type 4 is empty i .e has no rows to write i don't want to create a file with name 4 .

Thanks

Post #550445

Posted Monday, August 11, 2008 11:10 AM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

hey chuck i am applying ur approach, However it is not working. its still creating file with 0 kb.

Do u know if there is anything i am missing in Rowcount task that i have to do in any properties window

thanks

Post #550464

Posted Monday, August 11, 2008 12:44 PM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:43 PM
Points: 609, Visits: 326

i appear to be incorrect in that the conditional split is still creating the file, sorry about that. i was also thinking off the top of my head.

what if you still go down the path of using the Row Count task in your data flow to setup your variable and then the next step would be to write out the records in the dataflow to the flat file. (this will always create the file, yes)

after that, place in a script task that has a precedence constraint to check the row count variable to look for to be zero. if so, run the script task that contains script which will delete the file.

not knowing the size of your files, but an (UGLY) alternative, if the files are tiny, have your dataflow return into a reccordset destination, which is simply an object variable. then use that variable in a for each loop container to execute a data flow which would have something as simple as a ole db source to say "select 1 as dummy" field (all data flows must have a source object). then you can use the derive columns task to create new fields for your dataset based off of looping variables that you are placing your results into (for each record). then, insert that data flow into a destination file, ensuring that the overwrite property is not set. if there are no records, the For Each loop would not execute.

PLAN B IS UGLY, I WILL ADMIT THAT. I would much rather use the script component to just delete the file if the record count variable equals 0.

Post #550545

Posted Monday, August 11, 2008 1:08 PM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

I AGree With you. I was thinking across the same line. I was thinking to loop over the folrder to see if anyfile has size 0, if so then use filesystem task to delete that file. I belive script will work here the same. But, thanks for trying ..and let me know if u happen to come acroos it in some other way.........
thanks

Post #550563

« Prev Topic | Next Topic »

Add to briefcase

17 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse