November 7, 2012 at 3:34 pm
I need to import a flat file into a simple table. There are three 'epoch' date columns in the data. (Meaning they show the number of seconds since midnight 1-1-1970.) And look like this: 1352325814
I can use a SQL statement to convert them to UTC like this:
SELECT DATEADD(s, DATEDIFF(s, '1970-01-01 00:00:00', 1352325814), '1970-01-01 00:00:00') AS [UTC Date/Time]
to get something like 2012-11-07 22:03:00.000
I am trying to build a simple data flow with three tasks:
Flat File Source
Derived Column
OLE DB Destination
and the appropriate connections.
First, is this the way to do it? In the Derived Column task I have the expression setup like this:
"SELECT DATEADD( s, DATEDIFF(s, '1970-01-01 00:00:00', [dateTimeOrigination] ) , '1970-01-01 00:00:00' )"
where [dateTimeOrignination] is the epoch time I need to convert
When I run the package, it errors out after a few rows. The error messages are:
[Derived Column [2]] Error: The "Derived Column" failed because truncation occurred, and the truncation row disposition on "Derived Column.Inputs[Derived Column Input].Columns[dateTimeOrigination]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (2) failed with error code 0xC020902A while processing input "Derived Column Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[Flat File Source [17]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
When I open the CSV file in Excel, it looks right, I don't see any 'odd' data in the [dateTimeOrignination] column. Or anywhere else.
I know about enough SSIS to be dangerous but I think this looks like the way to load the data. Or is there another way?
And any ideas of where to start looking for what is causing these errors? Is there a problem with datatypes between the SSIS package and the database? The database file is set to datetime and the column property in the flat file connection is string[DT_STR] of 50.
The Test Connection works OK (everything is on my local desktop) and I'm running as SA.
Thanks for your time!
Norman
November 8, 2012 at 8:51 am
When I ran this in ssms
SELECT DATEADD(s, DATEDIFF(s, '1970-01-01 00:00:00', 1352325814), '1970-01-01 00:00:00')
I get an Arithmetic Overflow error reported.
And I cant for the life of me figure out why you need the datediff, which means the above can be simplifed to
Select DATEADD(s, 1352325814,'1970-01-01 00:00:00')
which when run returns the number you mentioned, at the top of your post.
In regards to the question what datatype is your original column as i believe it may need to be 8byte integer to hold these numbers, and the output should be a DT_DATE (i believe)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 9, 2012 at 12:22 pm
Thank you Jason,
I had the 'opportunity' to be in meetings for about 7 hours yesterday and didn't see your reply until just before time to leave for the day...
But thank you for pointing out that I was using the wrong formula to convert. I guess if you have a lot of stuff you have been copy and paste, make sure you paste the right thing into the little box... Your formula is what I intended, not exactly sure where I used came from.;-)
In the Derived Column task I don't see a way to change the data type. Do I need to add a Data Conversion task as well?
And I'm beginning to think the data isn't DOS/ANSI format. I get what appears to be data type mismatch. I'm having someone see if they can convert it to DOS/ANSI or at least verify that isn't the problem.
Thanks for pointing out my error, and for assistance. I'll keep trying and see what happens. I'm not a heavy user, not this is heavy lifting, but I don't have any local help either.
Norman
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply