June 12, 2012 at 2:06 pm
Hi Everyone,
Hope all is well.
I am trying to run a package that moves data from table to another in a different database. The issue I have is the table from the database1 has different datatypes for some columns when compared to datatypes of database2 and I need to move this data. Once the columns are mapped and when I ran package I am getting errors shown below.
Error at Data Flow Task [OLE DB Destination [43]]: Column "COlumn1" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [43]]: Column "COlumn2" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [43]]: Column "COlumn3" cannot convert between unicode and non-unicode string data types.
How can this issue be resolved.
Source Table
Column1 has varchar(50)
column2 has varchar(8000)
Column3 has numeric(18,0)
Column4 has Money
Column5 has varchar(50)
Destination Table
Column1 has nvarchar(12)
column2 has nvarchar(10)
Column3 has int
Column4 has decimal(18,2)
Column5 has nvarchar(20)
At this point of time we cant change the DDL of these tables as it would break the code.
Need your valuable inputs.
Thanks in advance
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 12, 2012 at 2:10 pm
Unless you match the data types up between the source and destination tables there is always the potential for conversion issues.
I don't understand why it would break anything to change the column data type of destination table to match that of source table. The numeric into a int could be a problem down the road as well.
The probability of survival is inversely proportional to the angle of arrival.
June 12, 2012 at 2:21 pm
sturner (6/12/2012)
I don't understand why it would break anything to change the column data type of destination table to match that of source table. The numeric into a int could be a problem down the road as well.
Because there are two different applications that will be using the data from these two different databases. And when the application team started designing the code the tables had different ddl. So is there something that I can do to make this inserts work? Like a stored proc that converts the datatypes before making the inserts??
Please advise.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 12, 2012 at 2:29 pm
I assume what you mean is that you have some code that diddles with the destination table directly and would break if you changed the data type of a column? wow....
Well the only other thing I might suggest is to create an intermediate table with appropriate data types. Move the data from the source table into it first, then insert from there into the final destination table and use CAST or CONVERT as needed.
The probability of survival is inversely proportional to the angle of arrival.
June 12, 2012 at 2:44 pm
ETL = 3 steps.
1. Export to the target server. For this I would create your own copy of the source servers table structure and move records from source to target.
2. Transform the data into the target format (see Cast statements below)
3. Load transformed records into the target table.
Cast statements:
cast(substring(Column1,1,12) as nvarchar(12))
cast(substring(Column2,1,10) as nvarchar(10))
cast(Column3 as int)
cast(Column4 as decimal(18,2))
cast(substring(Column5,1,20) as nvarchar(20))
June 12, 2012 at 3:05 pm
thadeushuck (6/12/2012)
ETL = 3 steps.1. Export to the target server. For this I would create your own copy of the source servers table structure and move records from source to target.
2. Transform the data into the target format (see Cast statements below)
3. Load transformed records into the target table.
Cast statements:
cast(substring(Column1,1,12) as nvarchar(12))
cast(substring(Column2,1,10) as nvarchar(10))
cast(Column3 as int)
cast(Column4 as decimal(18,2))
cast(substring(Column5,1,20) as nvarchar(20))
That looks well. I already created a source table structure in the destination db. But where/how do I need to use the transform statements you mentioned above?
Thanks much.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 12, 2012 at 3:39 pm
I think I figured it out. Thanks much for your help.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 12, 2012 at 3:45 pm
Something like this...
BEGIN TRY
INSERT INTO destinationTable
SELECT cast(substring(Column1,1,12) as nvarchar(12)),
cast(substring(Column2,1,10) as nvarchar(10)),
cast(Column3 as int),
cast(Column4 as decimal(18,2)),
cast(substring(Column5,1,20) as nvarchar(20))
FROM stagingTable;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
June 12, 2012 at 3:55 pm
thadeushuck (6/12/2012)
Something like this...BEGIN TRY
INSERT INTO destinationTable
SELECT cast(substring(Column1,1,12) as nvarchar(12)),
cast(substring(Column2,1,10) as nvarchar(10)),
cast(Column3 as int),
cast(Column4 as decimal(18,2)),
cast(substring(Column5,1,20) as nvarchar(20))
FROM stagingTable;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
Thank You
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 13, 2012 at 7:53 am
One of the columns has a type as unique identifier which accepst NULL values as well and I am using NULL as [columnname] to insert null as a hardcoded value into the table and I am getting this error. Can I cast this as well?
The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_I4" and "DT_GUID"
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply