A Multicast Transformation can be used for many things, and one of those things is to write the same data file generated from a query in a Data Flow Task into multiple network locations. The only problem with this approach is that each destination connection and file must be configured manually, which can get tedious and messy as the number of destination files grows. Just look at the package in the image below.

An alternative to a Multicast Transformation is to create a Data Flow Task with a single destination file, then copy that file to all the other destination folders using a Script Task.

Run the following sript in SQL Server Management Studio to create and populate a table named Customer.

CREATE TABLE [dbo].[Customer](
    [CustomerId] [int] NULL,
    [LastName] [varchar](20) NULL,
    [FirstName] [varchar](20) NULL,
    [City] [varchar](20) NULL,
    [State] [varchar](2) NULL,
    [ZipCode] [varchar](10) NULL,
    [Phone] [varchar](12) NULL
)
GO
INSERT INTO TestDB.dbo.Customer
(CustomerId,LastName,FirstName,City,State,ZipCode,Phone)
VALUES
(7923,'Blow','Joe','Chicago','IL','12345-9876','555-555-5555'),
(7924,'Antoinette','Marie','Seattle','WA','84356-8456','777-777-7777'),
(7925,'Doe','Janet','Houston','TX','99354-9445','333-444-555'),
(7926,'Alverez','Desmond','Des Moines','IA','79684-8473','222-222-2222'),
(7927,'Contrary','Mary','Boston','MA','17545-4564','111-111-1111')

Next, create a subdirectory named C:\DestinationSubdirectories\, and create 10 subdirectories under it named Dest01, Dest02, Dest03,...Dest10.

To create a Data Flow Task, run the import/export wizard from an SSIS project in Business Intelligence Development Studio, right-click on the SSIS Packages node and select SSIS Import and Export Wizard.

Choose the default SQL Server Native Client data source and the server and database where you created the Customer file, then click the Next button

.

Choose Flat File Destination and pick the first folder from the Destination drop-down list, then enter the DataFile.txt as the file name and click Next.

Choose the Write a query radio button and click Next.

Enter the query SELECT * FROM Customer and click Next.

Click the Edit Mappings button of the Configure Flat File Destination window.

Note that the Create destination file is the only available radio button. Click OK.

After returning to the previous Configure Flat File Destination screen, click the Preview button.

Note that the data from the SELECT query is the same data entered by the create script. Click the OK button.

After returning to the previous Configure Flat File Destination window once again, click the Next button.

From the Complete the Wizard window click the Finish button.

Click the Close button of the final Import and Export Wizard window.

At this point we have successfully created a Data Flow task that runs a query against the Customer table and writes a file named DataFile.txt containing the results of that query into C:\DestinationsSubdirectory\ .

Execute the package and confirm that C:\DestinationSubdirectories\Dest01\DataFile.txt has been created by the package.

Open the C:\DestinationSubdirectories\Dest01\DataFile.txt file to confirm its contents.

Add a Script Task to the package by dragging-and-dropping it onto the main panel.

Add a precedence constraint from the Data Flow Task to the Script Task by holding the CTRL key while clicking first the Data Flow Task, then the Script Task, then right-click on the Data Flow Task and select Add Precedence Constraint.

Double click on the Script Task to bring up the Script Task Editor.

Select Microsoft Visual Basic 2008 for the Script Language, then click the Edit Script button.

The Script Task contains the text Add your code here.

Replace the highlighted text with the following VB.NET code.

Dim FileName As String = "DataFile.txt"
Dim SourceSubdirectory As String = "C:\DestinationSubdirectories\Dest01\"
Dim DestinationSubirectories As New ArrayList
DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest02\")
DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest03\")
DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest04\")
DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest05\")
DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest06\")
DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest07\")
DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest08\")
DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest09\")
DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest10\")
Dim SeedFile As New System.IO.FileInfo(SourceSubdirectory & FileName)
If SeedFile.Exists = False Then
    Dim button0 As DialogResult = MessageBox.Show("Seed file '" & SourceSubdirectory & FileName & "' does not exist. " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & " Package exiting.", "DATA FLOW TASK FAILED", MessageBoxButtons.OK)
    Dts.TaskResult = ScriptResults.Failure
Else
    Dim message1 As String = SourceSubdirectory & Chr(13) & Chr(10)
    Dim message2 As String = ""
    Dim SuccessCount As Integer = 1
    Dim FailureCount As Integer = 0
    Dim DestSub As String = ""
    For Each DestSub In DestinationSubirectories
Try
    Dim PreviousDestinationFile As New System.IO.FileInfo(DestSub & FileName)
    PreviousDestinationFile.Delete()
Catch ex As Exception
End Try
Try
    SeedFile.CopyTo(DestSub & FileName)
    message1 += DestSub & Chr(13) & Chr(10)
    SuccessCount += 1
Catch ex As Exception
    message2 += DestSub & Chr(13) & Chr(10)
    FailureCount += 1
End Try
    Next
    If FailureCount = 0 Then
        Dim button1 As DialogResult = MessageBox.Show("'" & FileName & "' was written to the following " & SuccessCount & " subdirectories: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & message1, "Success Confirmation", MessageBoxButtons.OK)
    End If
    If FailureCount <> 0 Then
        Dim button2 As DialogResult = MessageBox.Show("'" & FileName & "' was written to the following " & SuccessCount & " subdirectories: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & message1 & Chr(13) & Chr(10) & "'" & FileName & "' was NOT written to the following " & FailureCount & " subdirectories: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & message2, "FAILURE ALERT", MessageBoxButtons.OK)
    End If
    Dim Writer As System.IO.StreamWriter
    Writer = IO.File.CreateText("C:\DataFlowTaskLogFile" & "-" & Now.ToString.Replace("/", "").Replace(":", "").Replace(" ", "") & ".txt")
    Writer.WriteLine("'" & FileName & "' was written to the following " & SuccessCount & " subdirectories: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & message1)
    If message2 <> "" Then
        Writer.WriteLine("'" & FileName & "' was NOT written to the following " & FailureCount & " subdirectories: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & message2)
    End If
    Writer.Close()
    Dts.TaskResult = ScriptResults.Success
End If

The first section of code defines values for the FileName and SourceSubdirectory variables, which define the location of the seed file generated by the Data Flow Task.

     Dim FileName As String = "DataFile.txt"

     Dim SourceSubdirectory As String = "C:\DestinationSubdirectories\Dest01\"

The next section creates an array list named DestinationSubdirectories. This is all the subdirectories to which the seed file will be copied.

     Dim DestinationSubirectories As New ArrayList

     DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest02\")
     DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest03\")
     DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest04\")
     DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest05\")
     DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest06\")
     DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest07\")
     DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest08\")
     DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest09\")
     DestinationSubirectories.Add("C:\DestinationSubdirectories\Dest10\")

This code confirms that the seed file has been created.

     Dim SeedFile As New System.IO.FileInfo(SourceSubdirectory & FileName)

     If SeedFile.Exists = False Then

If the seed file does not exist, the package displays an error message and ends execution.

    Dim button0 As DialogResult = MessageBox.Show("Seed file '" & SourceSubdirectory & FileName & "' does not exist. " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & " Package exiting.", "DATA FLOW TASK FAILED", MessageBoxButtons.OK)

    Dts.TaskResult = ScriptResults.Failure

If the seed file does exist, then the array list is looped through.

    Else

    For Each DestSub In DestinationSubirectories

The new seed file cannot be copied to any subdirectory where a previous version exists, so if a previous version of the file exists in the indexed subdirectory it is deleted.

    Try
        Dim PreviousDestinationFile As New System.IO.FileInfo(DestSub & FileName)
        PreviousDestinationFile.Delete()
    Catch ex As Exception
    End Try

The seed file is copied to the subdirectory. If it is successfully copied, the subdiretory name is added to the success message (message1) and the SuccessCount variable incremented by one. If it is not successfully copied, the subdirectory name is added to the failure message (message2) and the FailureCount varaible is incremented by one.

    Try
        SeedFile.CopyTo(DestSub & FileName)
        message1 += DestSub & Chr(13) & Chr(10)
        SuccessCount += 1
    Catch ex As Exception
        message2 += DestSub & Chr(13) & Chr(10)
        FailureCount += 1
    End Try

If, after the array list has looped through all the destination subdirectories, the FailureCount is still zero, a Success Confirmation popup is displayed.

    If FailureCount = 0 Then

        Dim button1 As DialogResult = MessageBox.Show("'" & FileName & "' was written to the following " & SuccessCount & " subdirectories: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & message1, "Success Confirmation", MessageBoxButtons.OK)

    End If

If, after the array list has looped through all the destination subdirectories, the FailureCount is not zero, a Failure Alert popup is displayed.

    If FailureCount <> 0 Then

        Dim button2 As DialogResult = MessageBox.Show("'" & FileName & "' was written to the following " & SuccessCount & " subdirectories: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & message1 & Chr(13) & Chr(10) & "'" & FileName & "' was NOT written to the following " & FailureCount & " subdirectories: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & message2, "FAILURE ALERT", MessageBoxButtons.OK)

    End If

The Success/Failure messages are also written to unique log files so that they are not overwritten.

    Dim Writer As System.IO.StreamWriter

    Writer = IO.File.CreateText("C:\DataFlowTaskLogFile" & "-" & Now.ToString.Replace("/", "").Replace(":", "").Replace(" ", "") & ".txt")

    Writer.WriteLine("'" & FileName & "' was written to the following " & SuccessCount & " subdirectories: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & message1)

    If message2 <> "" Then

        Writer.WriteLine("'" & FileName & "' was NOT written to the following " & FailureCount & " subdirectories: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & message2)

    End If

    Writer.Close()

This is what the package looks like upon successful completion.

You can confirm that the file has been copied to one or more of the destination subdirectories.

Conclusion

A Data Flow Task that writes a file to a single destination and copies that file to other destinations can be used in place of a Multicast Transformation.