We help IT Professionals succeed at work.

Can't Change Data Type in SSIS

NigelRocks
NigelRocks asked
on
Medium Priority
843 Views
Last Modified: 2013-11-10
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?

Comment
Watch Question

Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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?
 
 
Commented:
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.

Author

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?
 
 
CERTIFIED EXPERT
Commented:
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

Author

Commented:
Based on the advice of brad2575, I added an intermittend data conversion object.  Please see attached screen-shot.
Image.bmp
Commented:
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).

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.