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:
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…