Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date change before insert to database Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 9:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 6, 2014 1:45 PM
Points: 112, Visits: 1,481
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
Posted Thursday, November 8, 2012 10:34 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:23 AM
Points: 3,108, Visits: 11,503
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
Posted Friday, November 9, 2012 2:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 6, 2014 1:45 PM
Points: 112, Visits: 1,481
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
Posted Monday, November 12, 2012 4:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 6, 2014 1:45 PM
Points: 112, Visits: 1,481
Hi
Anyone have any thoughts on how this can be achieved?
Thanks
Post #1383609
Posted Monday, November 12, 2012 5:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:40 AM
Points: 13,294, Visits: 11,085
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 »

Add to briefcase

Permissions Expand / Collapse