September 9, 2015 at 10:03 am
Hello,
I have an SSIS package that pulls data from a MYSQL DB (Using RSSBus for Salesforce in SSIS to accomplish this). Most of the columns are loading properly, but I have many columns that I need to convert.
I have been using the Data Conversion dataflow task in SSIS to convert the rows.
I have 2 data conversions that work on most of the columns, but the DESCRIPTION column continues to return an error saying "Cannot convert between unicode and non-unicode types", regardless of what I choose on the Data Conversion task. So, basically I want to dump this column data into a SQL table with NVARCHAR datatypes. Here is what I am doing in my SSIS package...
1) Grab subset of data from SOURCE
2) Converts to TEXTSTREAM. (Data Conversion)
3) Converts to STRING. (Data Conversion)
4) Load Destination table. (OLE DB Destination)
I have also tried to simply convert the values to STRING, but that doesn't work either.
So, I have 2 Data Conversions working here that process most of the data correctly. Does anyone know what I can do to load the DESCRIPTION column?
Any help would be greatly appreciated.
Everyone has a plan until they get punched in the mouth. --Mike Tyson
September 9, 2015 at 11:45 am
Have you tried the Derived Column transformation? It will let you cast to an explicit string type.
September 9, 2015 at 12:09 pm
I'll check it out, thanks for your feedback.
On a side note... I am very surprised that SSIS is still (after all these years) one of the worst ETL tools on the market. I was able to convert all of these columns easily in EXECUTE SQL Task using an ALTER TABLE script. Our SSIS packages do not have the credentials to execute ALTER table scripts on production, so I had to junk that idea.
I just do not understand how you can easily convert a column using an ALTER table script, but the DATA CONVERSION tasks in SSIS choke on just about everything. SSIS has become just another cash-cow add-on.
Very disappointing.
Everyone has a plan until they get punched in the mouth. --Mike Tyson
September 10, 2015 at 12:51 am
SQLTougherGuy (9/9/2015)
I'll check it out, thanks for your feedback.On a side note... I am very surprised that SSIS is still (after all these years) one of the worst ETL tools on the market. I was able to convert all of these columns easily in EXECUTE SQL Task using an ALTER TABLE script. Our SSIS packages do not have the credentials to execute ALTER table scripts on production, so I had to junk that idea.
I just do not understand how you can easily convert a column using an ALTER table script, but the DATA CONVERSION tasks in SSIS choke on just about everything. SSIS has become just another cash-cow add-on.
Very disappointing.
The data conversion doesn't choke on anything. It will convert any string perfectly to a unicode string and vice versa.
It doesn't replace the values however, it creates a new column with the converted values. Make sure you map those in the destination.
And if your destination table has NVARCHAR, you need to choose the DT_WSTR data type.
Running ALTER TABLE scripts in order to convert data seems like a very bad idea. So every time the source changes you need to change your tables as well?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 10, 2015 at 10:23 am
Thanks!
Good point about the Alter Table script. I was able to dump the data into a table with all NVARCHAR datatypes and then was easily able to push the data into our staging tables.
There was some weird characters in the Salesforce data that was causing my package to crash and it would appear that dumping the data into a SQL table and then converting it worked.
Thanks for all of your responses!
Everyone has a plan until they get punched in the mouth. --Mike Tyson
September 10, 2015 at 3:13 pm
I found it! There was this weird character (?) in the data, so you are right about SSIS.
Everyone has a plan until they get punched in the mouth. --Mike Tyson
September 10, 2015 at 3:20 pm
SQLTougherGuy (9/9/2015)
I'll check it out, thanks for your feedback....
SSIS has become just another cash-cow add-on.
...
Really, cash cow? It comes free with SQL Server.
September 10, 2015 at 3:56 pm
What I mean by cash-cow is that they rarely make significant changes to the product and push to release new versions. SSIS is not free as it is included in the license.
Overall this tool is not that bad, but IMHO Microsoft could really enter the elite world of ETL (informatica), if they fixed some of the outstanding issues. There is still the issue of importing CSV files which is the most commonly used format, and the error messages in the debug window are cryptic at best. You also have to dig through rows of metadata just to find out which column is failing (if you are lucky enough for SSIS to provide you with that information). This is just to name a few issues that I have been dealing with since 2007.
I don't mean to sound like a hater but when you are facing a deadline and SSIS is not letting you know which column is failing, it could get quite annoying.
Apologies for the negative energy 🙂
Everyone has a plan until they get punched in the mouth. --Mike Tyson
September 11, 2015 at 12:30 am
SQLTougherGuy (9/10/2015)
What I mean by cash-cow is that they rarely make significant changes to the product and push to release new versions. SSIS is not free as it is included in the license.
There were quite some significant changes in SQL Server 2012. SQL 2016 will also have some changes.
There are also extra components released through codeplex (such as Odata source and Azure transformations).
SQLTougherGuy (9/10/2015)
Overall this tool is not that bad, but IMHO Microsoft could really enter the elite world of ETL (informatica), if they fixed some of the outstanding issues. There is still the issue of importing CSV files which is the most commonly used format...
Which issues with CSV files? I never had any issues.
SQLTougherGuy (9/10/2015)
I don't mean to sound like a hater but when you are facing a deadline and SSIS is not letting you know which column is failing, it could get quite annoying.
Ah, they fixed that in SQL 2016 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply