Insert records into a table with a GUID and several additional columns?

Question

I have a preparation table with 4 fields. Once the records are ready I use the sql script below to insert them into an existing table. It works great however I would like to watch this process in SSIS. Is this possible?

All the fields below exist in the destination table, but only the 4 in brackets are in my source table. Using TSQL, this is super easy. But the closest I have gotten in SSIS is by using derived columns and adding columns but it won't let me create a GUID. There are also some date fields that I have formatted in TSQL that is not possible in this dialog.

use MyDatabase
insert into dbo.DestinationTable (FILEID,
FILENAME,
STATUS,
LIBRARY,
FILESIZE,
MAJREV,
MINREV,
IMPORTDATE,
IMPORTTIME,
[CATNUM],
[MATDESC],
[CATCLASS],
[DOCVER])

select NEWID()
[PartNumber],
'In',
'Projects',
0,
0.0,
0.0,
DATEPART(YYYY, GETDATE()),
DATEPART(HH, GETDATE()),
[Catalog #],
[Material Description],
[Catalog Class],
[Document Version]
from dbo.SourceTable;

BICube BICube · Accepted Answer · 2015-05-23T06:11:31

When you do your query, in the data source component what is the problem with having the same query that you have?. It will create a GUID for you. And to make sure it is of data type GUID, you do a derived column transformation and use the following expression to convert any string to GUID

(DT_GUID) ("{" + YourGuid + "}")

If you don't like the idea of creating it using the source query, you can create a script component (in your data flow) that will generate a GUID for each row. The script would be something like this

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;


    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
        public override void Input0_ProcessInputRow(Input0Buffer Row)
        {
            // Create a Globally Unique Identifier with SSIS
            Row.Guid = System.Guid.NewGuid(); 
        }
    }

Check out this link for further details Create GUIDs in SSIS

Insert records into a table with a GUID and several additional columns?

1 Answers