May 7, 2011 at 7:50 am
I have a table in sql server which is populated from oracle table . For this import i am using ssis . now I have columns of first name and last name . In these columns the data is not going in proper manner. This is because Some data is comming with ","(comma) in beween strings. exmp: lastname='Mor,gan' .
I want to get out of this. I want my data to be inserted in proper format.
If any commas in between or in last or in start of the string then it will eliminate it and insert corrected data to sql server.
May 8, 2011 at 3:03 am
Do you want to remove the comma when moving the data from Oracle to SQL Server or in SQL Server?
Russel Loski, MCSE Business Intelligence, Data Platform
May 9, 2011 at 12:20 am
yes, I want to eliminated comma before importing my data into sql server.
May 9, 2011 at 12:39 am
scottichrosaviakosmos (5/9/2011)
yes, I want to eliminated comma before importing my data into sql server.
You can first import your data into an SQL staging table. Then run an update statement to remove the commas from your columns (such as using the REPLACE command) and finally import your data into your destination SQL server table.
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
May 9, 2011 at 5:30 am
ok this will work fine to single action, like here for removing commas. but what if i have some other special character to remove then again i have to write update. And is there any method to eliminate using SSIS tool. ?
May 9, 2011 at 5:46 am
scottichrosaviakosmos (5/9/2011)
ok this will work fine to single action, like here for removing commas. but what if i have some other special character to remove then again i have to write update. And is there any method to eliminate using SSIS tool. ?
Once you have your staging table in place, it would just be a matter of adding more UPDATE statements to cater for additional data cleaning, i.e. doing your clean-up job in one step. You can also group your update statements into a stored procedure to keep it more structured.
Having said all this, you can use Transformation scripts (C# or VB code) to perform this cleanup, however this will be done on a row-by-row basis, not ideal when you have large amounts of data.
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
May 9, 2011 at 2:53 pm
In SSIS you can use a Derived Column transformation and use REPLACE. You will find it in the String Functions folder in the Derived Column transform
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply