1

I have quite a few tables and I'm using SSIS to bring the data from Oracle to SQL Server, in the process I'd like to convert all varchar fields to nvarchar. I know I can use the Data Conversion transformer but it seems the only way to do this is to set each field one by one, then I'll have to manually set the mapping in the destination component to map to the "Copy of" field. I've got thousands of fields and it would be tedious to set it on each one... is there a way to say "if field is DT_STR convert to DT_WSTR"?

Benjamin Bryan
  • 351
  • 1
  • 8
  • 21
  • When you say "bring from Oracle to SQL Server", does that mean you are using SSIS to create the SQL Server tables? Or do they already exist? – Dave Mason Mar 13 '14 at 18:21
  • On the OLEDB destination to SQL Server I hit the "New..." button to generate a create table script (I replace all the varchars with nvarchars before running the script) so at that point the table already exists. I'll probably truncate the tables as the first step of the SSIS package. But if there's a way this would work better if the tables were created on the fly I'd be open to that solution as well. – Benjamin Bryan Mar 13 '14 at 18:46
  • When you generate table scripts, is that a one-time process? Or do you have to do it every time you run the SSIS package? – Dave Mason Mar 13 '14 at 18:58
  • The tables need to be generated once. My normal practice is to create the tables and just run truncate statements afterwards. – Benjamin Bryan Mar 13 '14 at 21:03
  • Ok, sorry to keep coming back with questions. It's been a while since I've used SSIS... Is the source data from Oracle unicode? If so, is the SSIS package converting it back to non-unicode, even though you've specified `NVARCHAR` columns? – Dave Mason Mar 13 '14 at 21:16
  • No problem. Source data is non-unicode. The SQL destination is Unicode (I need the data in Unicode because of this bug: http://connect.microsoft.com/SQLServer/feedback/details/695902/performance-problem-with-multi-value-parameterized-queries-on-non-unicode-databases). I know the Data Transformer will convert non-unicode to unicode but I'd have to manually setup the conversion for each column, and then I'd have to manually map the destination columns to the converted columns. I just want a way to tell SSIS to convert all non-unicode fields to unicode fields, or to do an implicit conversion. – Benjamin Bryan Mar 13 '14 at 21:25
  • After reading the link to the bug, I'm doubting what I had in mind will solve your issue. I was thinking just load the Oracle data (which is non-unicode) into SQL Server. During the load, don't worry about VARCHAR vs NVARCHAR. After the load is finished, use some dynamic SQL to change the VARCHAR columns to NVARCHAR in the target tables in SQL Server. Again though, I'm thinking this won't help you. :( – Dave Mason Mar 13 '14 at 22:33

2 Answers2

0

what you can do is, instead of replacing varchar with nvarchar manually before running the script is copy and save all the create table scripts generated by SSIS to a document. Then you can do a global replace nvarchar x varchar in the document. Use then the amended script as a step in your SSIS package to create the tables before populating them with the data from Oracle.

Jayvee
  • 10,670
  • 3
  • 29
  • 40
  • That's what I did, I'm not concerned with creating tables, but rather the data conversion in SSIS. Thanks. – Benjamin Bryan Mar 13 '14 at 21:04
  • I modified my question to clarify it's the data I'm trying to bring over with SSIS, not the metadata. – Benjamin Bryan Mar 13 '14 at 21:12
  • I see, sorry I misunderstood. I think the only way would be to make the changes directly in the mapping files used by DTS. They should be located @ C:\Program Files\Microsoft SQL Server\110\DTS\MappingFiles or similar depending on your installation. You may need to change a couple of xml files OracleToSSIS10.XML and OracleClientToSSIS10.XML. A good article about this: https://www.simple-talk.com/sql/ssis/working-with-ssis-data-types/ – Jayvee Mar 13 '14 at 22:09
  • That's exactly what I needed, I ran a quick test and that seemed to do the trick. Thanks! – Benjamin Bryan Mar 13 '14 at 23:15
-1

The proper way is to use the data conversion step...

That said, it appears if you disable external meta data validation in SSIS, you can bypass this error. SQL will then use an implicit conversion to the destination type.

See this SO post for a quick explanation.

Community
  • 1
  • 1
Dave C
  • 7,272
  • 1
  • 19
  • 30
  • Where do you set this? I set ValidateExternalMetadata to false in the OLE DB Destination but I still get an error converting non-unicode to unicode. [OLE DB Destination [2]] Error: Column "COMPANY_CODE" cannot convert between unicode and non-unicode string data types. – Benjamin Bryan Mar 13 '14 at 21:08
  • ValidateExternalMetadata is available on the source/destination tasks. DelayValidation is higher up at the control flow level. Click on the data flow task, and you'll see it in properties. – Dave C Mar 14 '14 at 14:01
  • I think that's the same place I set it, I also had DelayValidation set but I still got the error messaging about converting between unicoe and non-unicode data types. Thanks. – Benjamin Bryan Mar 14 '14 at 17:06