Multi-File Output Destination Script Component

Yesterday I posted about using a destination script component to write XML files from a data flow. Today, I’m continuing in the same vein with a post about writing multiple destination files from a destination script component. The inspiration for this was a post on the MSDN forums where a developer described a scenario where he had a single file and needed to split it into multiple output files based on some conditional logic. Normally, this could be handled with a conditional split and multiple destinations, but the catch in his scenario was that he didn’t know how many destination files he would need.


Based on that, I worked up a quick script destination to accomplish his goal. Later, I’m also going to post an alternative approach that does not use any script.


Multifile Destination


The data flow is fairly straightforward. The flat file source is retrieving a flat file with the following data:


NAME;VALUE;DATE
A;1;1/1/2000
A;2;1/2/2000
A;3;1/3/2000
A;4;1/4/2000
A;5;1/5/2000
B;1;1/1/2000
B;2;1/2/2000
B;3;1/3/2000
B;4;1/4/2000
B;5;1/5/2000
C;1;1/1/2000
C;2;1/2/2000
C;3;1/3/2000
C;4;1/4/2000
C;5;1/5/2000

The goal is to output three files, one each for the A, B, and C rows. I used a folder connection rather than a specific flat file connection. The folder connection specifies the output directory to place the files in.

DestinationFolder

On the script task (created as a destination), all the available input columns were selected.

Script Inputs

The Destination Folder is specified on the Connection Managers page.

ConnMgr

The script is as follows:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO

Public Class ScriptMain
Inherits UserComponent

Private Const fileName As String = “Output”
Private Const fileExt As String = “.txt”
Private Const delimiter As String = “;”
Private targetFolder As String
Private fileWriter As StreamWriter
Private fileCount As Int32 = 1
Private previousValue As String = String.Empty

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
targetFolder = CType(Me.Connections.Destination.AcquireConnection(Nothing), String) + “\”
End Sub

Public Overrides Sub PostExecute()
fileWriter.Close()
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim column As IDTSInputColumn90

If Not Row.NAME = 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.NAME
End If

With fileWriter
.Write(Row.NAME + delimiter)
.Write(Row.VALUE.ToString() + delimiter)
.WriteLine(Row.DATE.ToString() + delimiter)
End With
End Sub

End Class


The most interesting portion of this script is in the ProcessInputRow method. The initial section of the method checks to see if the current row Name value is the same as the previous row. If it is different, it closes the current file, opens a new one, and increments a counter for the file name. Then the current row values are written out to the file.

This script works well for outputting multiple files. In the script, I’m using a simple previous value comparison to see if  I need to start a new file, but the logic could be much more complex if necessary.

An advantage of this approach over the non-script approach is that it only requires one pass through the source file. Another advantage is that the comparison logic can be fairly complex and can easily handle triggering new file creation based on number of rows or a trigger row. The pure SSIS approach can’t handle that as easily. However, this approach does require that your rows are sorted properly before being fed into the destination, whereas the alternate approach can handle the rows in any order.

Leave a Reply