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

Prevent flat file from being written (SSIS 2008r2) Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 6:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:11 AM
Points: 338, Visits: 956
SO In the project I'm working I'm importing csv files from a dynamic map to a database.
As an extra feauture I made it so that all the lines that are faulty get written into a csv file and all the good lines into another.
So that when correcting not all the lines have to be redone or people have to spend hours finding the correct line.

So I originally did this in SSIS 2012 now I have to remake it in 2008r2,in 2012 if a flat file doesn't receive inputs it isn't written but it seems in 2008r2 it does.
I can't go check the files based on size cause the column headers are already in the file.
So I thought about using a rowcount into a variable and based on that execute a create script, I can create the file however only the last row is written and also how do I access the variables.

The code section that should generate the lines is below & is based off
http://agilebi.com/jwelch/2007/06/03/multi-file-output-destination-script-component/

 'This method is called once for every row that passes through the component from Input0.
'
'Example of reading a value from a column in the the row:
' zipCode = Row.ZipCode
'
'Example of writing a value to a column in the row:
' Row.ZipCode = zipCode
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim column As IDTSInputColumn100

' If Not Row.SERVER = previousValue Then
If Not fileWriter Is Nothing Then
fileWriter.Close()
End If
fileWriter = New StreamWriter(targetFolder + fileName + fileCount.ToString() + fileExt, False)
'fileCount += 1
'previousValue = Row.SERVER
'End If

With fileWriter
.Write(Row.SERVER + delimiter)
.Write(Row.STATUS.ToString() + delimiter)
.Write(Row.OU.ToString() + delimiter)
.Write(Row.OS.ToString + delimiter)
.Write(Row.SP.ToString + delimiter)
.Write(Row.VERSION.ToString() + delimiter)
.Write(Row.INSERVEROU.ToString + delimiter)
.Write(Row.DATEADDED.ToString + delimiter)
.Write(Row.DATECHANGED.ToString() + delimiter)
.WriteLine()
End With

If Not Row.SERVER = previousValue Then
fileWriter.WriteLine()
previousValue = Row.SERVER
End If

End Sub

Post #1422062
Posted Wednesday, February 20, 2013 7:09 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:33 PM
Points: 4,983, Visits: 11,676
Maybe you could come at this from another angle.

Why not put a rowcount transformation underneath the script component and then delete the file in the control flow if [rowcountvariable] == 0?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1422069
Posted Wednesday, February 20, 2013 7:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:11 AM
Points: 338, Visits: 956
Tried that and it still executed the flat file destination
Post #1422078
Posted Wednesday, February 20, 2013 7:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:33 PM
Points: 4,983, Visits: 11,676
Resender (2/20/2013)
Tried that and it still executed the flat file destination


I did not say it wouldn't.

My suggestion is to perform the deletion as part of the control flow - after the completion of the data flow and after the file has been created.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1422088
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse