June 26, 2007 at 6:44 pm
I have just installed SQL 2005 and wish to use SSIS to transform some of my data as it is copied. I have the logic I wish to use, I am just not sure as how to apply it.
Basically I have some numerical data stored in the source (Oracle) as a whole number without decimal places.
Eg: Instead of 100.34 it would be 10034
This is not fixed it may 100.34 -> 10034 or 100.334 -> 100334 and so on. The number of decimal places for a given field are stored in another table.
So I need to get the name of the column being processed, check if it has an entry in the lookup table and modify the value of the column according to the result from the lookup table.
Any ideas on how I can do this?
June 26, 2007 at 10:35 pm
You will need to script out a logic and then use the script to upload data in destination. Through SSIS i see no such features available for you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 27, 2007 at 9:19 pm
I have made some progress in regards to my task, but was wondering if anyone had any advice.
I have stored procedure that takes a column name and value and returns the corrected value.
I need to know how to loop through all columns added to a script component as I do not want to have manually provide that information.
ScriptMain.ComponentMetaData.InputCollection(0).InputColumnCollection(0).Name gives me access to the name, but what about the data?
ScriptMain.ReadOnlyVariables seems to be inaccessible at run time.
Any ideas?
June 28, 2007 at 1:55 am
You could continue down that path but would this not be easier / quicker (Script task can slow you down)
Use a SQL task before your DF and grab all the mapping information. I am assuming you have a finite number of columns and their decimal places.
Create a bunch of variables called COL1, COL2, COL3 etc and map their values to the values obtained from the mapping table.
Then, using a derived column, you can, in place on the same column, convert the source column to string, substring it and place a . in it and then convert it all back to decimal.
This has the advantage of not creating new buffers for new columns (you reuse the original columns) and is quicker that the script component.
I disagree with the script component and looping over all columns for find the right one then doing the stuff to it as this would happen for each row and become CPU intensive / slow.
Try the derived column way and see.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 28, 2007 at 4:40 pm
I will look into this, but I was trying to avoid having to specify each column as there are probably more than 30 tables with up 200 columns.
Still I may have to go this route if performance is unreasonable.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply