August 11, 2008 at 9:19 am
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,
August 11, 2008 at 9:42 am
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
August 11, 2008 at 9:43 am
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.
August 11, 2008 at 9:48 am
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?
August 11, 2008 at 10:16 am
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?
August 11, 2008 at 10:38 am
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.
August 11, 2008 at 10:51 am
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
August 11, 2008 at 11:10 am
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
August 11, 2008 at 12:44 pm
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.
August 11, 2008 at 1:08 pm
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
August 11, 2008 at 3:13 pm
Think from the other direction - if there were no rows, delete the file.
Get the rowcount during your data flow and put it into a variable. In the control flow of the package use a precedence constraint to check if the rowcount was zero - if it was, use a file system task to delete the file.
August 12, 2008 at 7:28 am
Thanks Michael.........yet another way of doing it , more simple.
But now the problem is ( i would say not problem, but more complex) that i have seven flat files destination, then so i have to create 7 variable to hold rowcount for each file connection, i. e. 7 row count task with 7 variable and also agian 7 file system task to do delete.
Would n't it be possible that just loop over the folder to see if any file that exists has size 0 , then if so delete it. Now, here i have problem, i am nor good at coding VB. so i have to work hard to get some code..
ANyone out there can plzzzzzzz post me a code to see if any file in a folder has size 0 and if so delete it.
Thanks.........
August 12, 2008 at 7:37 am
wow. Talk about many was to skin a cat. Some convoluted methods at that.
Just use a row count component, set the value to a variable, after your data flow, if value is zero, run script task and delete the file.
If you _really_ don't want to create the file, use a script component as a destination. Only create the file when you receive your first row.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 12, 2008 at 8:09 am
if you want to go down the path of looping through a folder and checking file sizes, there are two steps to add to your package. (I like this idea, as you mentioned, because then you do not need the 7 variables and 7 script tasks)
First is add the for each loop container which uses the Foeach File Enumerator and returns the Fully Qualified path to a variable, which I setup as FilePath
Then, inside of the ForEach Loop, I have a script task with the following code (pass in the FilePath as a ReadOnly variable):
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Try
Dim ofile As New FileInfo(Dts.Variables("FilePath").Value.ToString)
' Check size of file, if 0 then delete
If ofile.Length = 0 Then
File.Delete(Dts.Variables("FilePath").Value.ToString)
End If
Dts.TaskResult = Dts.Results.Success
Catch
Dts.TaskResult = Dts.Results.Success
End Try
End Sub
End Class
August 12, 2008 at 8:15 am
While this is a perfectly valid solution if you don't want to use the variable / delete option, I would rather pass the folder to the script task and use a for each file in folder and delete like that. It would be far far quicker than SSIS looping and executing the script task. If of course you want to do other stuff before or after deleting then it is acceptable.
Chuck Rivel (8/12/2008)
if you want to go down the path of looping through a folder and checking file sizes, there are two steps to add to your package. (I like this idea, as you mentioned, because then you do not need the 7 variables and 7 script tasks)First is add the for each loop container which uses the Foeach File Enumerator and returns the Fully Qualified path to a variable, which I setup as FilePath
Then, inside of the ForEach Loop, I have a script task with the following code (pass in the FilePath as a ReadOnly variable):
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Try
Dim ofile As New FileInfo(Dts.Variables("FilePath").Value.ToString)
' Check size of file, if 0 then delete
If ofile.Length = 0 Then
File.Delete(Dts.Variables("FilePath").Value.ToString)
End If
Dts.TaskResult = Dts.Results.Success
Catch
Dts.TaskResult = Dts.Results.Success
End Try
End Sub
End Class
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply