Date change before insert to database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Date change before insert to database

Date change before insert to database

Rate Topic

Display Mode

Topic Options

Author

Message

dbman

Posted Thursday, November 08, 2012 9:47 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 91, Visits: 1,150

I have a file with the date 20121025171203. I have created a database table with the field date in
SQL Server. I have also created a SSIS Package with data flow: Flat File to OLE DB Destination.

I have set the error outputs to Ignore failures. When the package is run the Source and destination turn green but then my execution results
show that 0 rows have been wrote.

I understand that the date above may be preventing this from being written to SQL server. Is there any transformation i can
use to change the date format before it is written to SQL server?

Regards

John

Post #1382594

Michael Valentine Jones

Posted Thursday, November 08, 2012 10:34 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 2,969, Visits: 10,619

If you change the format from 20121025171203 to a string in this format 20121025 17:12:03 that should load into a SQL Server datetime column OK.

Post #1382621

dbman

Posted Friday, November 09, 2012 2:54 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 91, Visits: 1,150

Hi,

The issue is that this file is extracted from another system and the date is in this format. I would like to change the date format to the format you suggested or something different before it hits the SQL server table (date field).

Post #1382906

dbman

Posted Monday, November 12, 2012 4:49 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 91, Visits: 1,150

Hi
Anyone have any thoughts on how this can be achieved?
Thanks

Post #1383609

Koen Verbeeck

Posted Monday, November 12, 2012 5:01 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 9,410, Visits: 6,495

You can use a derived column transformation to change the format of the date field.
Use the SUBSTRING function and convert the result the the appropriate datatype.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1383613

« Prev Topic | Next Topic »

Permissions