SSIS - fill unmapped columns in table in OLE DB Destination

sql sql-server ssis etl dataflow
sion_corn picture sion_corn · Nov 29, 2012 · Viewed 8.3k times · Source

As you can see in the image below, I have a table in SQL Server that I am filling via a flat file source. There are two columns in the destination table that I want to update based on the logic listed below:

  • SessionID - all rows from the first CSV import will have a value of 1; the second import will have a value of 2, and so on.
  • TimeCreated - datetime value of when the CSV imports happened.

I don't need help with how to write the TSQL code to get this done. Instead, I would like someone to suggest a method to implement this as a Data Flow task within SSIS.

enter image description here

Thank you in advance for your thoughts.

Edit 11/29/2012

Since all answers so far suggested taking care of this on the SQL Server side, I wanted to show you what I had initially tried doing (see image below), but it did not work. The trigger did not fire in SQL Server after SSIS inserted the data into the destination table.

If any of you can explain why the trigger did not fire, that would be great.

enter image description here

Answer

Ken Rachynski picture Ken Rachynski · Nov 29, 2012

If you are able to modify the destination table, you could make the default values for SessionID and TimeCreated do all the work for you. SessionID would be an auto-incremental integer while the default value for TimeCreated would be getdate() or gettime() depending on the data type.

Now, if you truly need it the values to be created as part of your workflow, you can use variables for each.

SessionID would be a package variable which is set by an Execute SQL Task. Just reference the variable in your result set and have your SQL determine the next number to use. There are potential concurrency issues with this, though.

TimeCreated is easily done by creating a Derived Column in your data flow based on the system variable StartTime.

Related questions

convert Excel Date Serial Number to Regular Date

I got a column called DateOfBirth in my csv file with Excel Date Serial Number Date Example: 36464 37104 35412 When i formatted cells in excel these are converted as 36464 => 1/11/1999 37104 => 1/08/2001 35412 => 13/12/1996 I need to do this transformation in SSIS or in …

Read More
Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

I'm trying to import a flat file into an oledb target sql server database. here's the field that's giving me trouble: here are the properties of that flat file connection, specifically the field: here's the error message: [Source - 18942979103_txt [424]] …

Read More
SSIS Connection not found in package

I'm kind of new to SSIS programming, and I'm having some problems deploying an SSIS package. This package runs correctly on my PC, does everything it needs to do ... but when I deploy it cannot find the connection strings. Here …

Read More