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
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 ] 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 ] 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!