How do I programmatically create a Transfer SQL Server Objects task?

• Sunday, June 20, 2010 7:50 AM

  Moderator

   

   
  

  0

  [This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]

  Question:
  I have faced an issue where the TablesList property of a Transfer SQL Server Objects Task takes in list of tables to transfer. There is no way to dynamically set the property through SSIS variable because this property expects a StringCollection object.
   
  Answer:
  You can use a Script Task to add a Transfer SQL Server Object Task and set the TableList property with StringCollection.
   
  1. Create a package scope variable “TableName” to hold table names, change its type into Object.
   
  2. From the toolbox, drag an Execute SQL Task to the Control Flow panel. Double-click that task to edit it, change the “Resultset” value to “Full Result Set” and input the following T-SQL clause to get the table names.

  Select [table_name] from INFORMATION_SCHEMA.TABLES
   
  3. Click the “Result set” tab, click Add, input 0 for the Result Name and select “User::TableName” for the Variable Name.
   
  4. Drag a Script Task to the Control Flow panel, connect the Execute SQL Task to it. Open the Property Editor by double clicking the Script Task, input “User::TableName” into the ReadWritevariables field and use VB.NET as the script language.
   
  5. Click the “Edit script” button, in the Visual Studio, write your custom code by referring to the following code to copy data with Transfer SQL Server Objects Task.

  ' Create a package to execute TransferSqlServerObjectsTask
  Dim pkg As New Package()
  Dim MoveTable As Executable = pkg.Executables.Add("STOCK:TransferSqlServerObjectsTask")
  Dim MoveTableTask As TaskHost = CType(MoveTable, TaskHost)
   
  Dim aa As ConnectionManager = pkg.Connections.Add("SMOServer")
  aa.ConnectionString = "SqlServerName=.;UseWindowsAuthentication=True;UserName=;"
  aa.Name = "TestConn"
   
   
  MoveTableTask.Properties("CopyData").SetValue(MoveTableTask, True)
  MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True)
  MoveTableTask.Properties("ExistingData").SetValue(MoveTableTask, Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask.ExistingData.Replace)
  MoveTableTask.Properties("CopyAllTables").SetValue(MoveTableTask, False)
   
   
  ' Read the records from Object variable and put them into StringCollection
   
  Dim Tables As StringCollection = New StringCollection()
   
  Dim oleDA As New OleDbDataAdapter
  Dim dt As New DataTable
   
  Dim row As DataRow
  oleDA.Fill(dt, Dts.Variables("TableName").Value)
  For Each row In dt.Rows
  Tables.Add(row(0).ToString()) 
  Next
   
   
  MoveTableTask.Properties("TablesList").SetValue(MoveTableTask, Tables)
  MoveTableTask.Properties("SourceConnection").SetValue(MoveTableTask, aa.Name)
  MoveTableTask.Properties("SourceDatabase").SetValue(MoveTableTask, "Test")
  MoveTableTask.Properties("DestinationConnection").SetValue(MoveTableTask, aa.Name)
  MoveTableTask.Properties("DestinationDatabase").SetValue(MoveTableTask, "Test1")
   
  ' Execute package and dispose
  pkg.Execute()
   
  Dts.TaskResult = ScriptResults.Success
  

  ---

  Please remember to mark the replies as answers if they help and unmark them if they provide no help
  • Reply
  • Quote