How to transform data with SSIS Transfer SQL Server Objects Task?

Question

I am creating an SSIS package to transfer tables between databases on different servers. The timestamp on the source database is in UTC and I would like to convert it to my local time using [CallConnectedTime] AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS CallConnectedTime.

How is it possible to do this transformation in the Transfer SQL Objects Task? I thought about using a Data Flow Task but then I need to create one for each table I am bringing over.

Answer 1

The Transfer SQL Server Objects Task does not allow you to define any data transformations.

If you don't want to go the route of creating multiple Data Flow Tasks, you could create an ADO recordset of the table names after the Transfer task runs, then create a Foreach Loop to iterate over the ADO recordset. To do this you might use the following query in an Execute SQL Task, with ResultSet set to Full result set:

SELECT Name 
FROM sys.tables
WHERE type_desc = 'USER_TABLE'

Map the result set to an Object variable type. Then you can create the Foreach Loop with an ADO enumerator, point it at your Object variable, and then create another variable to hold the value of the Name field.

Using the variable that now holds the Name field, create another Execute SQL Task inside your Foreach Loop. This Execute SQL task will build and execute a dynamic SQL statement that will UPDATE the table by setting the CallConnectedTime.

The Execute SQL Task inside your loop would then look something like this:

DECLARE @query NVARCHAR(MAX)
DECLARE @Table VARCHAR(1000) = ?

SET @query = N'
UPDATE ' + @Table + N'
SET CallConnectedTime = CallConnectedTime AT TIME ZONE ''UTC'' AT TIME ZONE ''Pacific Standard Time'''

EXEC(@query)

And under Parameter Mapping on the left pane of the editor, you'd add your table name variable as Parameter Name 0 like this:

Your Control Flow should then look something like this when you're done with the above steps (ignore the error icons).