Here is a possible way of exporting the tables of different structure to flat file using Script Task. This example will export two tables containing different fields and data to a flat file using Script Task. In order to export the data, you can use the DataReader instead of using the DataGrid. There could be other possible ways to do this.
Step-by-step process:
- Create three tables named
dbo.TablesList, dbo.Source1 and dbo.Source2 using the scripts given under SQL Scripts section.
- Populate the tables
dbo.TablesList, dbo.Source1 and `dbo.Source2`` with data shown in screenshot #1.
- On the SSIS package's
Connection manager, create an OLE DB connection named SQLServer to connect to the SQL Server instance as shown in screenshot #2.
- In the package, create 4 variables as shown in screenshot #3.
- In the Control Flow, place an
Execute SQL Task, a Foreach Loop Container and a Script Task within the Foreach loop container as shown in screenshot #4.
- Configure the
Execute SQL task as shown in screenshots #5 and #6.
- Configure the
Foreach Loop container as shown in screenshots #7 and #8.
- Replace the Main method inside the Script Task with the code given under the section
Script Task Code.
- Screenshot #9 shows package execution.
- Screenshots #10 - #12 show the files exported from SSIS using Script Task code.
Hope that helps.
SQL Scripts:
CREATE TABLE [dbo].[Source1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ItemNumber] [varchar](20) NOT NULL,
[ItemName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Source1] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Source2](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Country] [varchar](20) NOT NULL,
[StateProvince] [varchar](50) NOT NULL,
CONSTRAINT [PK_Source2] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TablesList](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](50) NOT NULL,
[FilePath] [varchar](255) NOT NULL,
CONSTRAINT [PK_Tables] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
Script Task Code: (Use the code given below to replace the Main() method in your Script task)
VB Main() method code that can be used in SSIS 2005 and above:
Public Sub Main()
Dim varCollection As Variables = Nothing
Dts.VariableDispenser.LockForRead("User::TableName")
Dts.VariableDispenser.LockForRead("User::FileName")
Dts.VariableDispenser.LockForRead("User::Delimiter")
Dts.VariableDispenser.GetVariables(varCollection)
Dim fileName As String = varCollection("User::FileName").Value.ToString()
Dim query As String = "SELECT * FROM " & varCollection("User::TableName").Value.ToString()
Dim delimiter As String = varCollection("User::Delimiter").Value.ToString()
Dim writer As StreamWriter = Nothing
Dim connection As OleDbConnection = New OleDbConnection(Dts.Connections("SQLServer").ConnectionString)
Dim command As OleDbCommand = Nothing
Dim reader As OleDbDataReader = Nothing
Try
If File.Exists(fileName) Then
File.Delete(fileName)
End If
connection.Open()
command = New OleDbCommand(query, connection)
reader = command.ExecuteReader()
If reader.HasRows Then
writer = New System.IO.StreamWriter(fileName)
Dim row As Integer = 0
While reader.Read()
Dim header As Integer = 0
Dim counter As Integer = 0
Dim fieldCount As Integer = reader.FieldCount - 1
If row = 0 Then
While header <= fieldCount
If header <> fieldCount Then
writer.Write(reader.GetName(header).ToString() & delimiter)
Else
writer.WriteLine(reader.GetName(header).ToString())
End If
header += 1
End While
End If
While counter <= fieldCount
If counter <> fieldCount Then
writer.Write(reader(counter).ToString() & delimiter)
Else
writer.WriteLine(reader(counter).ToString())
End If
counter += 1
End While
row += 1
End While
End If
Catch ex As Exception
Throw ex
Finally
connection.Close()
writer.Close()
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
Screenshot #1:
Screenshot #2:
Screenshot #3:
Screenshot #4:
Screenshot #5:
Screenshot #6:
Screenshot #7:
Screenshot #8:
Screenshot #9:
Screenshot #10:
Screenshot #11:
Screenshot #12:
