Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 513
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,
Chuck Rivel
Chuck Rivel
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 357
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
Mark Beringer-730383
Mark Beringer-730383
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
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.
Chuck Rivel
Chuck Rivel
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 357
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?
Mark Beringer-730383
Mark Beringer-730383
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
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?
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7802 Visits: 8745
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 513
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
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 513
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
Chuck Rivel
Chuck Rivel
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 357
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.
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 513
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search