Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

IS IT POSSIBLE ?--NOT to CREATE Flat File If it is empty? Expand / Collapse
Author
Message
Posted Monday, August 11, 2008 3:13 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.

Post #550629
Posted Tuesday, August 12, 2008 7:28 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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.........
Post #550977
Posted Tuesday, August 12, 2008 7:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 11, 2013 7:41 AM
Points: 917, Visits: 410
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,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #550992
Posted Tuesday, August 12, 2008 8:09 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:42 AM
Points: 621, Visits: 343
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
Post #551025
Posted Tuesday, August 12, 2008 8:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 11, 2013 7:41 AM
Points: 917, Visits: 410
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,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #551037
Posted Tuesday, August 12, 2008 10:38 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
I belive Crispin is right too. But there will be only about 7 files each time i run and mostly one or two files will be empty. But agian we start using application later on , there won't be any file with 0 Kb. this is simplly for the initial stage of the apllication.
Thanks CHuck!!!! for the code.............

Really appreciate all the help and inputs..
thansk guys...............
Post #551221
Posted Thursday, March 07, 2013 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:31 PM
Points: 4, Visits: 53
thanks!!!
Post #1428019
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse