Can't Change Data Type in SSIS

Experts,

In a simple flat-file to table operation in an SSIS package, I'm getting the proverbial error that it can't convert between data types.  I go into the ODBC destination object and change the types from DT_WSTR (under "External Columns") to DT_STR.  I run the package, it blows up with the same errors, and when I go back into the destination object, the columns are unchanged.  Why can't I change the data type of the external columns?

LVL 1
NigelRocksAsked:
Who is Participating?
 
brad2575Commented:
It wont let you, but you can do a transformation script between the two and use that to convert each field in the flat file to the DT_STR data type then insert the data from the transformation task to the DB.
0
 
NigelRocksAuthor Commented:
I'm using a data conversion between the flat file object and the database object.  Both the input and output fields are of DT_STR type, as are the input columns of the database object.  Everything in the flat file is of type DT_STR.  Where does shaking the dead chicken at the computer come into play?
 
 
0
 
brad2575Commented:
It is not just a data conversion between the two but you have to have a middle step using a "Data Conversion" task to do the transformation for you.  Going from the flat file to the database insert directly it cant do the conversion properly.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NigelRocksAuthor Commented:
I just said I have inserted a data conversion object between them, which is a type of data transformation object.  What more specifically should I do?
 
 
0
 
HoggZillaCommented:
Nigel,
>I go into the ODBC destination object and change the types from DT_WSTR (under "External Columns") to DT_STR.
If I understand you correctly, you changed the datatypes in the columns of the Destination component of the Data Flow task. If so, you can't do -  or at least, if you do that it will likely cause an error because the database data types are still what they are. In other words, when you connect the Destination to the database table it reads the data types and if you change them it will error.
Please correct me if I misunderstood your issue.
Remember, #1 rule of SSIS is to use the Wizard. :-)
Thanks, Hogg
0
 
NigelRocksAuthor Commented:
Based on the advice of brad2575, I added an intermittend data conversion object.  Please see attached screen-shot.
Image.bmp
0
 
brad2575Commented:
that should do what you need, the only other step is to update the destination to use the converted field names in the destination part (that is most likely the error you are seeing).

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.