SSIS Data Conversion

Question

SSIS Data Conversion

CountCet

Ten Centuries

Points: 1371
More actions
May 10, 2007 at 8:14 am

#173594

I'm creating my first SSIS package and I have yet to fully understand the data conversion.
I have a table with a single varchar(75) column.
And example would look like this
4032555445552215555333
I need to grab some substrings and convert them to integers and a couple to just string type data.
With t-sql I would usually just CAST(SUBSTRING(Data,16,1) As Int) AS A1
for each int column column. I just use SUBSTRING(Data,10,2) AS Code for the string data.
With a Derived Column task I am trying to do the same thing. I have tried several different expressions but I am not having any luck. The destination table has data types nchar and int.
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15671 More actions · Answer 1

Can you post some of the expressions you've tried, and the associated error message (or the output, if no error)

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Erich Brinker SSCrazy Points: 2973 More actions · Answer 2

You might also want to look at the data conversion task?

CountCet Ten Centuries Points: 1371 More actions · Answer 3

In the data conversion task I tried to do a SUBSTRING(data,16,1) and the next column gave an option for the data output type which I chose DT_I1 but that doesn't work.

I even tried something like

(DT_I1(SUBSTRING(data,16,1))

I just need a substring converted to an integer.

Unfortuantely my development server has been reaped sop I have to get a new one going.

Tim Mitchell SSCoach Points: 15671 More actions · Answer 4

I just ran through creating some dummy data (numbers stored as text) in a text column and running Substring() as you described, then entering the result into an int column in another table - it ran without errors, and the data was successfully imported into the integer column. When you say that it doesn't work, are you getting error messages, or does the data just not flow to your target table? Have you tried using the data viewer to see the data as it is passed from one component to another?

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

CountCet Ten Centuries Points: 1371 More actions · Answer 5

Your source data was data type text? I may have to look at my source again.

The error was converting the data conce it got to the int columns. I'm going to give it another go and use the data viewer.

Thanks.

Nishan Ratnayake SSC Veteran Points: 242 More actions · Answer 6

I have the same problem..I want to convert data from string to int...three columns of my table contain single letters(String datya type). Before i load them into destination TABLE, I want to convert them into int. I used data convertion transformation..but it doesn not allow me to convert data. when I change data type to string to unsigned int, it gives a error message saying "input metadata is mismatched or smthing". Do you have any idea where I went wrong?

Also please can you tell me how to run a SSIS package through command line?

surabhi_nair SSC Rookie Points: 43 More actions · Answer 7

Hi Forum Newbie,

You can use dtexec to execute an ssis package from command prompt. Explore dtexec for additional info....

Regards,

minu