Avatar of qprjohn121
 asked on

SSIS - importing a csv file into SQL table

This should be really simple but I am pulling my hair out as a result of data conversion issues.

I want to import a csv file (dimDate.csv) into a SQL table (dbo.DimDate) using SSIS. In my data flow task I have 3 components:

1. A Flat File source component called 'DimDate csv file'
2. A Data Conversion component called 'Unicode to ANSI'
3. An OLEDB Destination component called 'SQL Table DimDate'

I also have 2 Connection Managers for the csv file and the SQL database:

1. 'Connection to DimDate Flat File'

In the SSIS Data Flow design view, I have a 'x' in a red circle within the OLEDB Destination component which is alerting me that one of the fields cannot convert between unicode and non-unicode.

The problem field shows in the Flat File Source Component editor as being of data type 'Unicode string [DT_WSTR]'. I have tried many things within the data conversion component but am unable to configure it to remove the alert message.

Please can someone explain what I need to change or configure to make this work?

Microsoft SQL ServerMicrosoft SQL Server 2008Databases

Avatar of undefined
Last Comment

8/22/2022 - Mon

Change the destination field in your table to be unicode (NVARCHAR).
Then double click the destination and close it again to force it to refresh the meta data.
This assumes you want your calendar table to contain unicode data.

double click the destination... in SSIS

Thanks nmcdermaid I will give this a try as a workaround.

However it feels wrong that I am having to change my destination table. Surely SSIS is supposed to handle the data conversion for me? If not, what purpose has the Data Conversion SSIS component?

Any SSIS-based suggestions anyone?

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

Like I said in my post, this assumes you want your calendar table to contain unicode data.
If you don't want it to contain unicode data, you can simply use a data conversion transformation in SSIS to convert the incoming unicode data into non unicode data. Thats an SSIS based suggestion.
It is a bit of a pain that text data always comes in as unicode but its really down to the text driver reporting it that way.

Thanks - I would like to follow your suggestion to 'simply' use the data conversion transformation.

This is the bit I have tried and failed to do. Would it be possible to spell out the input output configurations necessary?

I am amazed that I have not been able to find a simple walkthrough of the process anywhere on the web despite seeing many similar requests.

I think you will be helping a huge amount of people if you can walk through the steps.

Best regards

Sorry I didn't mean to be smart, I didn't read your original post properly.
So.... in your text file source you can see that the data type of the columns is DT_WSTR.
In the Data conversion editor add another column with an alias and ensure this additional column is of type DT_STR
Now double click the green line coming out of the data conversion and note that you have an extra field which is not unicode.
Now make sure you map the non unicode field in the oledb destination.
It should be a matter of using data conversion to cast the fields that you need them map the correct field.
It can sometimes confuse itself by not being up to date with metadata, but opening and closing the property on the destination often fixes that.

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Thanks so much, I just needed to uncheck the unicode box! I didnt understand unicode issues previously, and the box was checked by default, leading me into an unnecessary world of problems. Once fixed, I was able to remove the data conversion component and avoid any column conversion issues once I tried it again with the box cleared.

I hope this helps every other SSIS newbie too.