Using SSIS to map hard coded values as part of a one-to-many table migration in Visual Studio

Question

Using SQL Server 2005 and Visual Studio 2005, I'm trying to create a SSIS package to merge data from 1 table to several other tables.

The source table does not have a several fields that the destination tables do. For example 'CreatedBy' and 'CreatedDate' fields. I would like these to be hard coded (in a sense) as part of the package import process.

The problem is not knowing what to use to facilitate this mapping. As a starting point it would be acceptable to have a hard coded '1' and GetDate() for createdBy and createdDate respectively.

The "Input and Output Properties" or "Column Mappings" tab on the "Advanced Editor for Destination" options dialog does not have any apparent support for mapping "default" values such as GetDate().

Any suggestions as how to achieve this?

Answer 1

The SSIS way to create new columns (with static values or not) is to use the "Derived Column" transformation in your dataflow, in between the source and destination.

This enables you to specify additional columns and their values using an expression. For the current date/time, use Getdate() as the expression and set the datatype to "date (DT_DATE)". To hard code a value, double-quote it in the expression (e.g. "1") and specify the relevant data type.

Answer 2

Rather than using a Table as a source, how about specifying the query specifically? That way, you can statically define values as part of the source.

e.g.

SELECT id, fieldOne, fieldTwo, '1' AS createdBy, GetDate() AS createdDate
FROM SourceTable

I've done this exact thing recently.

One important thing to remember is that you need to make sure your datatypes match. I had a few problems with string data types not matching up (UTF-8 and the like).