SSIS Data Conversion

  • 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!

  • 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

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

  • 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.

  • 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

  • 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.

  • 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?

  • Hi Forum Newbie,

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

    Regards,

    minu

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

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