March 3, 2016 at 8:13 am
I'm new to SSIS and have many questions, here is one...
I have an SSIS package that extracts data from a SQL Server table and overwrites a pipe delimited flat file. There are several Datetime columns in the table, most of which have zeros in the seconds and fractional seconds parts of the field. The exception to this is a column called ExtractDateTimeStamp, which does have fractional seconds. This particular field shows up in the flat file with 6 extra zeros at the end.
For example,
Extract Date
2016-03-03 08:45:45.357
shows up as
|2016-03-03 08:45:45.357000000|
The datatypes in the SSIS package are (both) DT_DBTIMESTAMP, so I'm not sure why zeroes are being appended.
I will most likely massage the column to strip off/zero out the seconds and fractional seconds in my stored procedure prior to inserting to the SQL table, but I'd like to understand what is happening.
Thanks
March 18, 2016 at 7:27 am
I'm still not sure why this is happening (other dates in the table are being extracted without any problem), however I'll outline here what I did to fix it in the event it is helpful to someone else:
This package was simply to extract all data from an SQL table and write it to a flat file.
Edit the package using Visual Studio.
1.Double Click on the package in the Solution Explorer
2.Click on the Data Flow tab
3.For simple packages, like the this extract, I removed the existing Flat File Destination, (Right Click and Delete it, confirm the prompt)
4.From the toolbox, click and drag Derived Column, from Data Transformations (expand this if it isn’t already expanded)
5.Click the Source-Query element and drag the green arrow into the Derived Column box
6.Right Click the Derived Column box and select Edit
7.Expand the Columns list, select the column to transform and drag it to the Derived Column name box.
8.Change the Derived Column to ‘Replace <<column name>>’
9.Select/Expand Type Casts option on the right panel, choose DT_STR
10.To cast the date with extra zeros appropriately, (DT_STR,19,1252) (This casts the date to a string, and uses just the first 19 characters. The 1252 is the code page)
11.Be sure to configure the error handling if you don't want a truncation error to fail the package. I chose to ignore truncation errors since I knew I was truncating the extra zeroes off the column.
12.Apply
13.Add flat file destination to the data flow (decide if overwrite should be checked)
14.Edit it to check the column mappings
15.Execute package to test and confirm results in flat file.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply