Dynamically mapping source and destination table and column information and load from souce to destination,how to use Microsoft.SqlServer.TableTransferGeneratorTask

To achieve this task (Dynamically map source and destination table in XML file) we can use the task Microsoft.SqlServer.TableTransferGeneratorTask

1. I am going to load the table data from source server to destination server without creating dataflow task in ssis package
2. all table, column mapping I am going to configured in XML file like
•  source table name and destination table name
•  source column name and destination column name
•  column length
• column data type
3. I am going to use Microsoft.SqlServer.TableTransferGeneratorTask  to achieve this task(Its       Microsoft task)
4. Please execute this script in any sql server database server

create database dbSource

go

create database dbDestination

go

use dbSource

go

Create Table Test(emp_id int)

go

insert into Test values (10)

insert into Test values (20)

insert into Test values (30)

go

Create table SourceSoaps (sSoapName varchar(20),sSoapCost decimal(15,4))

go

insert into SourceSoaps values('santoor',20.02)

insert into SourceSoaps values('mysoresand',50.23)

insert into SourceSoaps values('deve',852.02)

go

use dbDestination

go

Create Table Test(emp_id int)

go

Create table DestinationSoaps (dSoapName varchar(20),dSoapCost decimal(15,4))

Create a SSIS Package and Please follow the below steps

• Create two oledb connection managers with the below names(names should be case sensitive)

1. SourceConnection       (This should map to source database server in our example this connection manager should map to dbSource database)
2. DestinationConnection (This should map to destination database server in our example this connection manager should map to dbDestination database

• Create a xml file which will contain all source and destination table ,column information(table, column names and data types) and save it as TableSchema.xml

<?xml version="1.0"?>

<Tables>

<Table>

<Name>Test</Name>

<Schema>dbo</Schema>

<DestName>Test</DestName>

<DestSchema>dbo</DestSchema>

<Columns>

<Column>

<Name>emp_id</Name>

<DataType>int</DataType>

<DestName>emp_id</DestName>

</Column>

</Columns>

</Table>

<Table>

<Name>SourceSoaps</Name>

<Schema>dbo</Schema>

<DestName>DestinationSoaps</DestName>

<DestSchema>dbo</DestSchema>

<Columns>

<Column>

<Name>sSoapName</Name>

<DataType>varchar</DataType>

<Length>20</Length>

<DestName>dSoapName</DestName>

</Column>

<Column>

<Name>sSoapCost</Name>

<DataType>varchar</DataType>

<DataType>decimal</DataType>

<Precision>15</Precision>

<Scale>4</Scale>

<DestName>dSoapCost</DestName>

</Column>

</Columns>

</Table>

</Tables>

• Create an empty package and packagename as   "InnerPackage.dtsx"
• Create a connection manager for " InnerPackage.dtsx" and connection manager name as " InnerPackage.dtsx"

• Drag TableTransferGeneratorTask into control flow, if you are unable to find this task try to add this task to toolbox and the dll is located at "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\Microsoft.SqlServer.TableTransferGeneratorTask.dll" and register in GAC
• If you are unable to add this task to toolbox please add this code to package XML code(By right click on package and view code) as shown in picture

 



<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.TableTransferGenerator.TableTransferGenerator, Microsoft.SqlServer.TableTransferGeneratorTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0">

  </DTS:Executable>

NOTE:Please check this version and public key token from dll It may change

• save package xml content and close package xml
• open package designer, now you will get errors i.e.

1. Error 1 Error loading Package1.dtsx: Error loading task "Task does not have TaskContact Information".   C:\SSISSample\SSISSample\Package1.dtsx 1 1 
2. Error 2 Validation error. {11099D17-3EE8-473A-869F-320BD015E419} TableTransferGenerator Task: A connection name or variable name for the package must be specified. Package1.dtsx 0 0 
3. Error 3 Validation error. {11099D17-3EE8-473A-869F-320BD015E419} : There were errors during task validation.   Package1.dtsx 0 0 

• To resolve these issues go to TableTransferGenerator task properties
• Give valid "name" and "description"
• Give "ConnectionName" as InnerPackage.dtsx
• Create a variable in main package with name as "CurrentTableNode" and type as object
• set tablemetadatanodevariablename as : CurrentTableNode
• drag foreach loop container
• right Click on edit
• go to collection (As shown picture)
• Take enumerator as : Foreach NodeList Enumerator
• DocumentSourceType : FileConnection
• DocumentSource : XML connection manager
• EnumerationType: Node
• OuterXPathStringSourceType: DirectInput
• OuterXpathString: Tables/Table

      

• go to variable mappings

1. select CurrentTableNode as variable(index automatically 0)

now drag TableTransferGeneratorTask into foreach loop container

 

now drag execute package task into foreachloop container

1. set package as "InnerPackage.dtsx "

now run main package the data will be loaded to two tables