How to insert the date data in the database through SSIS Package .

start_date            end_date

01/08/2014         01/01/2015

01/10/2014     02/02/2015

Hi,

Using SQL task you can simple write

insert into TableName (start_date, end_date)
values (value1, value2)

Depends on server you can use convert functions

For example in oracle you can use "to_date(value1,'DD/MM/YYYY')"

in SQL server you can use "cast" function.

What is your data source ? Is it a file(txt,csv, xls etc) or sql table ?

It is csv file ,

start_date            end_date

01/08/2014         01/01/2015

01/10/2014     02/02/2015

In SSIS Package , I tried to use Derived Columns i.e database date ,

in expression  : (DT_DBDATE)((contract_start_date == (DT_DBDATE)"0001-01-01") ? (NULL(DT_DBDATE)) : ((DT_DBDATE)contract_start_date)) 

but it failed

What i need to do , please suggest .

Without using Derived column / Data Conversion transformation, does your package run ? Because when you use CSV file as source, by default the data type will be DT_STR with length 50 and when you try to connect to the OLE DB destination (guessing) then it should insert without any issue.

Using a derived column you should be able to use:

contract_start_date == '' ? NULL : (DT_DBDATE)contract_start_date

Hi kalsubu,

According to your description, you want to import data from csv file to database use SSIS package, you tried to use expression in tried Derived Columns, but failed.

To import data from csv file to database with SSIS package, please refer to the following steps:

1. In design mode, click on Control Flow and drag Data Flow Task to the right side pan.
   
2. Double click on the Control Flow task, it will take you to Data Flow pan.
   
3. Drag Flat File Source from Toolbox to Data Flow task pan.
   
4. Configure flat file Connection manager.
   
5. Select the Path of the file and specify Text Qualifier.
   
6. Click on Columns and adjust OutputColumnWidth.
   
7. Drag Derived Column from Toolbox to Data Flow task pan.
   
8. Double click Derived Column, add start_data and end_date to derived column name, then type (DT_DBDATE)[Column] to expression text box.
   
9. Select OLE DB Destination from right side Toolbox and drag to below the Derived Column.
   
10. Double click on OLE DB Destination and connect to the database and table created earlier.

The following screenshots are for your reference:
 

 

If you have any more questions, please feel free to ask.

Thanks,
Wendy Fu

0001-01-01 is not a valid date values in SQLServer unless you use datetime2

Can you try this and see if it works?

([contract_start_date] == "0001-01-01" ? (NULL(DT_DBDATE)) : ((DT_DBDATE)[contract_start_date])

If you've other date values also which are beyond the range of sqlserver datetime datatype (ie dates older than 1753-01-01) it will still break

Much better option would be this

((DT_I4)SUBSTRING([contract_start_date],1,4) < (DT_I4) "1753" ? NULL(DT_DBDATE) : (DT_DBDATE) [contract_start_date])