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'
2. 'STUDIOXPSLAPTOP.Warehouse
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?
Thanks
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'
2. 'STUDIOXPSLAPTOP.Warehouse
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?
Thanks
Microsoft SQL ServerMicrosoft SQL Server 2008Databases
Last Comment
qprjohn121
double click the destination... in SSIS
ASKER
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?
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?
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.
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.
ASKER
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
John
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
John
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.
DataConversion.jpg
Metadata.jpg
Mapping.jpg
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.
DataConversion.jpg
Metadata.jpg
Mapping.jpg
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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.
I hope this helps every other SSIS newbie too.
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.