Hi, I have an excel sheet as source file,in this source file date format is yyyy-mm-dd. Now my issue is when i take this as a source in ssis,date format automatically get change in mm-dd-yyyy. I want date format same like source file. Can anyone help me ..?? thanks in advance..

The OLE DB provider determines the datatype. In this case it will probably be datetime (check it by right clicking on the data flow path right after your excel source and choose meta data). If you want an other format when you export it to for example a flat file then you have to cast it to a string. (Or maybe check your regional settings in windows.)Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

The datatype will be determined within the excel file by the "number category" eg General, Text, Date, Currency etc. The oledb connection manager does a scan of the first 50 or so rows to determine the datatype. In your case the cells in this column must be formatted as General or date, which SSIS reads as DT_DATE or DT_DBDATE. You have three options: 1) Add a Data Conversion Transformation to the Data Flow in SSIS (http://msdn.microsoft.com/en-us/library/ms141706.aspx) to convert to a text string in the appropriate format. Using SSIS data types. 2) Add a script task that converts the data type. Using VB data types 3) Read the source as a query rather than direct table (~sheet) access and use a cast/convert function in the query. (I'm not sure standard t-sql functions will work against excel sheets, but worth a try). Be aware that the base date (ie decimal = 0) differs for SSIS to SQL Server, have a look at my post: http://karlberan.wordpress.com/2010/03/31/ssis-vs-t-sql-datetime-datatypes/ My Blog "Karl Beran's BI Mumble"

The datatype will be determined within the excel file by the "number category" eg General, Text, Date, Currency etc. The oledb connection manager does a scan of the first 50 or so rows to determine the datatype. In your case the cells in this column must be formatted as General or date, which SSIS reads as DT_DATE or DT_DBDATE. You have three options: 1) Add a Data Conversion Transformation to the Data Flow in SSIS (http://msdn.microsoft.com/en-us/library/ms141706.aspx) to convert to a text string in the appropriate format. Using SSIS data types. 2) Add a script task that converts the data type. Using VB data types 3) Read the source as a query rather than direct table (~sheet) access and use a cast/convert function in the query. (I'm not sure standard t-sql functions will work against excel sheets, but worth a try). Be aware that the base date (ie decimal = 0) differs for SSIS to SQL Server, have a look at my post: http://karlberan.wordpress.com/2010/03/31/ssis-vs-t-sql-datetime-datatypes/ My Blog "Karl Beran's BI Mumble"

Where is the destination of the data? If SQL server you don't have to do anything. The display is depending on the client settings.

Yes, my destination is SQL server..