Insert date while importing a flat file into SQL Server table

Question

I'm trying to import a flat file into SQL Server table using SSIS package. This package is scheduled to run everyday. I've already created an extra column before importing the file.

Ex:

Flat file on 01/08/2019:

001,Tony Stark,Ironman
014,Steve Rodgers, Captain America

Flat file on 01/09/2019:

414,Peter Parker, Spiderman
007,Clark Kent, Superman

Expected result:

After importing, table should look like this on 01/08/2019

ID      Name              Alias                 Date
````    ``````            ``````                `````
001     Tony Stark        Ironman               2019-01-08  
014     Steve Rodgers     Captain America       2019-01-08

like this on 01/09/2019

ID      Name              Alias                 Date
````    ``````            ``````                `````
001     Tony Stark        Ironman               2019-01-08  
014     Steve Rodgers     Captain America       2019-01-08 
414     Peter Parker      Spiderman             2019-01-09
007     Clark Kent        Superman              2019-01-09

You explained very well the sources and expected results but... what have you obtained instead? — Angel M., Jan 09 '19 at 21:58
@intruder from your profile it looks like you are asking a lot of question without replying on answers or accepting them!!! — Yahfoufi, Jan 16 '19 at 10:14

score 3 · Accepted Answer · answered Jan 09 '19 at 22:11

You can achieve this in many ways:

1- Add Derived Column to the package

You can add a Derived Column Transformation in you Data Flow Task, and Use the [System::StartTime] variable which contains the time that the package started to run. Or you can use the GETDATE() function that returns the current date.

2- Add a default value to your added Column

You can add GETDATE() function as a default value to the column, so foreach new line inserted, the value of the current date will be assigned.

ALTER TABLE DestTable ADD CONSTRAINT DF_SomeName DEFAULT GETDATE() FOR [Date];

References

Thanks! I'm using DEFAULT GETDATE() while creating the table. So every time a record is inserted, it writes the current date to that column. — intruder, Jan 17 '19 at 15:10

score 0 · Answer 2 · answered Jan 09 '19 at 22:09

When you go to load the csv file into mysql, load it into a new mysql table.

Then, use a table alter to create a new date column. Update that new table, setting the column to the desired date.

Then, insert the values from the new table into the final destination table, using mysql insert-select syntax.

Finally, drop the new/temporary table.

Insert date while importing a flat file into SQL Server table

2 Answers2