September 3, 2014 at 8:00 am
I am creating my 1st SSIS package. On my Control Flow I have a Execute SQL Task which checks the catalog and if the table exists drops it, then creates the SQL Table for me. Next I have a Data Flow Task which has a OLE DB Source (Microsoft Access) a Data Conversion - to convert all fields from access into Unicode String -- and a OLE DB Destination (SQL Server table).
My project builds A-Ok, but when i go to run it I get a package validation error that says it is unable to convert column A & B between unicode and non-unicode string data types. I thought the Data Conversion was converting for me so I didn't have to worry about this?
Then I get an error of: 'Error at SSIS.Pipeline: component OLE DB Destination (192) failed validation and returned validation status "VS_ISBROKEN" -- WHAT does that mean?
Thank you in advance for anyone who responds time
September 3, 2014 at 8:06 am
Regarding the first error.
When you use the data conversion component, it doesn't convert the column itself. It puts the converted data into a new column.
Don't forget to map this new column in your destination.
Regarding the second error.
The table that you drop in the first step needs to exist when the package validates the destination component. The problem is that it might not exist. In that case you need to set the DelayValidation property of the data flow to True. This delays the validation of the data flow until the data flow actually starts. If this doesn't fix the issue, the error is probably metadata related. In that case, you need to refresh the metadata of the destination by opening and closing the component.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 3, 2014 at 8:15 am
TY for the rapid response. I have mapped my OLE DB Destination to the converted columns from my Data Conversion but still receive the same error? I am covnerting to Unicode string [DT_WSTR] is that the correct Data Type to Convert to?
September 3, 2014 at 10:48 am
JoseMenendez (9/3/2014)
TY for the rapid response. I have mapped my OLE DB Destination to the converted columns from my Data Conversion but still receive the same error? I am covnerting to Unicode string [DT_WSTR] is that the correct Data Type to Convert to?
What is the data type in the destination table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 3, 2014 at 11:13 am
In the destination table
FieldA is set to be varchar(150)
FieldB is set to be nvarchar(150)
September 3, 2014 at 11:21 am
FieldA needs to be (DT_STR,150,1252), FieldB needs to be (DT_WSTR,150).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 3, 2014 at 11:52 am
Hoozah! That has it importing properly!
One last tweak I would like to make (if it's not to difficult)
The ID field that I create when creating my table is remaining null. How can I set it to be the PK and be an auto-increment?
September 3, 2014 at 12:03 pm
You need to specify that in the CREATE TABLE statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply