May 10, 2007 at 8:14 am
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!
May 15, 2007 at 7:40 am
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
May 15, 2007 at 8:18 am
You might also want to look at the data conversion task?
May 15, 2007 at 10:54 am
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.
May 15, 2007 at 11:25 am
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
May 15, 2007 at 11:54 am
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.
September 13, 2007 at 11:30 am
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?
March 25, 2009 at 1:28 am
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