CODEWITHSUNDEEP
sql-serverexcelssisdata-conversiontimestamp-with-timezone
J.S.Orris
J.S.Orris

Reputation: 4821

Error at Data Conversion Transformation when Trying to Convert String from Excel Source to DB_TIMEZONEOFFSET to SQL Server Destination

Been several years since I had to write an SSIS package and struggling with converting a datetimezone string from Excel to Data Conversion transformation converting to DB_TIMEZONEOFFSET to a SQL Server Destination.

The following is the Timestamp in all 7 rows of Excel source: enter image description here

The date column from the Excel source is a Unicode String [DT_WSTR] as seen below:

enter image description here

The SQL Server destination field is setup as DATETIMEOFFSET(7) as seen below:

enter image description here

I am receiving the following errors on the Data Conversion transformation when run the data flow:

[Data Conversion 2] Error: Data conversion failed while converting column "Column1.createdAt" (82) to column "Copy of Column1.createdAt" (38). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion 2] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of Column1.createdAt]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of Column1.createdAt]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (2) failed with error code 0xC0209029 while processing input "Data Conversion 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.

It is also important to note that I did also try to use a Derived Column transformation before the Data Conversion transformation as well to cast as DATETIMEOFFSET(7) to match that of SQL Server destination and received errors there as well similar to above.

Been fighting this for a while now and any extra eyes will help.

Thanks.

Upvotes: 1

Views: 802

Answers (1)

Mostafa NZ
Mostafa NZ

Reputation: 382

ssis cannot do the conversion if there are T and Z letters in the field. Use the REPLACE command to replace the letter T with space and the letter Z without characters. Like This : (DT_DBTIMESTAMPOFFSET,7)REPLACE(REPLACE(createdAt,"T"," "),"Z","")

Upvotes: 2

Related Questions