• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

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?

0
NigelRocks
Asked:
NigelRocks
  • 3
  • 3
4 Solutions
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now