September 19, 2011 at 12:04 pm
i am trying to import an access table in sql server. One of the columns Age is defined as Integer in SQL Server.
In Access, this feild has non-numeric values in some rows. I need to either enter 0 where there is non-numeric value or i need to replace it with some integer.
pls assist.
September 19, 2011 at 12:26 pm
It depends! And there is no standard formula as per my knowledge. But I would prefer to import access table in a dummy sql table with varchar type of Age field and then perform all the update script with the help of
ISNUMERIC() function (to find all non numeric values). and then transfer data to the original table and then drop the dummy table.
Regards,
Sudhir
September 20, 2011 at 6:03 am
You can do it in SSIS by putting a Data Conversion component that converts the strings to int.
Map the error rows to a derived column that replaces the strings with a 0, and then merge the two flows together using a UNION ALL.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 21, 2011 at 7:36 am
Either the Data Conversion or the Conditional Split would work for this. Try to convert the value to the appropriate type, similar to:
(DT_I4)[input_column_name]
If the cast is successful, the integer value would be added to the data pipeline. IF not, the conversion would fail and send the row to the component's error output. Make sure you configure the component to "Redirect Rows to Error Output" rather than "Fail Component", since the latter is the default behavior.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
September 21, 2011 at 7:51 am
Sudhir's idea seems the least cumbersome to me ...
Or (and I am not sure whether this is even possible, because I have not tried importing from Access for ages...) use an Access query as your data source and do the required conversions and substitutions there.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 21, 2011 at 7:55 am
I imported the access db in a staging database. From there all conversions were smooth. database didnt have huge number of records so used normal select into statement with conditions 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply