Update a table in ssis without using Data Conversion

3
votes

whenever i try to update tables from source to destination tables (sql server to sql server) in ssis i run a stored procedure with the update query. i execute this SP via OLE DB Command. Now for the OLE DB command to run i have to do a Data Conversion from DT_WSTR to DT_STR.

enter image description here

My question is: is it possible to perform update without this data coversion? what is confusing to me is that i am pulling data from sql server and pushing data to sql server. so why is that varchar having different data enconding(DT_WSTR and DT_STR)?

But if i simply do an insert, i.e no update(use the OLE DB Command) i dont have to do any coversion and my SSIS package works fine. Now there is no problem of DT_WSTR and DT_STR but the problme occurs only when i try to run an OLE DB Command. how is ssis now inserting data from nvarchar to varchar?

enter image description here

i am sorry if this is a foolish question. i am quite new to SSIS.

1
sqlsql-server-2008business-intelligencessis
Does your Source SQL Azure and destination both have same data type ? – praveen
yes they have the same table structure and data types – Newton Sheikh
r u mapping the columns in OLEDB Command properly .This seems weird .You can double click the arrow between SQL Azure and Lookup and check the metadata for the column which is causing this issue – praveen
ya it have been mapped properly. This is just killing me and m so confused that its running great when i do an simple insert but if i try to do an update i m forced to do a conversion. The metadata is DT_WSTR. – Newton Sheikh
I suspect that since your are using ADO.NET destination so it is implicitly converting from DT_WSTR to DT_STR as mentioned in Microsoft Connect. – praveen

1 Answers

2
votes

It seems that ADO.NET source which you are using to connect to SQL Azure is by default converting varchar to nvarchar.Since at present OLEDB source cannot be used to connect to SQL Azure which in my case will give you correct column data type so you are left with using Data Conversion to convert to DT_WSTR to DT_STR.

There is a Microsft Connect item which shows this issue with ADO.NET source

 DECLARE @column_value AS NVARCHAR(30)
 SET @column_value = ?
 UPDATE YOURTABLE 
 SET column1=convert(varchar(30),@column_value)