SSIS Script component (transformation)

Question

SSIS Script component (transformation)

rhd110

Ten Centuries

Points: 1062
More actions
March 25, 2011 at 2:58 am

#148623

Hi,
I have table having around 150 columns. I read that table using OLE DB Source like this
SELECT * FROM dbo.TABLE1
I have to do some trim, pad operations which could be done by derived column, but it would be hectic since there are more than 120 varchar column. I have to avoid derived column transformation.
So instead of using Derived column, I want to use script component (transformation). Here are my questions below:
1. Code to trim only varchar column : In C# we can check like typeof() == string like this then trim pad etc.
2. After that is done I have to push all columns as ouput column (Without declaring output column in the Input and OutPut Editor) Which then can be put in destination
Can the above be done pls help.

Viewing 14 posts - 1 through 13 (of 13 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

1) have a look at the Methods of the String Class, Trim is in there as well as all the other usual ones.

2) I am fairly sure that you will need to declare the output columns, otherwise you will not be able to connect up to your destination.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Have you considered changing the query in your data source? Something like this:

SELECT RTRIM(col1), RTRIM(col2), ...

FROM MyTable

John

rhd110 Ten Centuries Points: 1062 More actions · Answer 3

Thanks for the comment

Since all columns in input are pushed to the Output any method that will create the output columns dynamically.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Sorry, I don't understand why you want to create the columns dynamically. Do you require a different set of columns from your table each time the task is executed? If so, how do you determine which columns you need?

John

steveb. SSC-Forever Points: 46733 More actions · Answer 5

rhd110 (3/25/2011)
Thanks for the comment
Since all columns in input are pushed to the Output any method that will create the output columns dynamically.

I am not sure that will work, have you tested this..?

rhd110 Ten Centuries Points: 1062 More actions · Answer 6

Hi,

I will explain from the start. I have more than 10 tables having columns ranging from 85 to 150. I need to load them in 10 target tables.

First method is to create 10 DFT's and map the source and target task using a derived column(Where I am doing trim operations etc).

Second method is to Loop through the 10 tables uisng for loop.

Take source in DFT (using variable "SELECT * FROM " + variableTable)

Do trim operations in Script component (Here only create output columns which would be used as input to destination.)

For this I need to create column dynamically.

Please help...

steveb. SSC-Forever Points: 46733 More actions · Answer 7

why do you need to create them dynamically?, is there a reason you want to do a loop?

if you have 10 tables with differing amount of columns, then this does not really lend itself to using a loop. you may be better off having 10 seperate dataflows , one for each table.

rhd110 Ten Centuries Points: 1062 More actions · Answer 8

The first method is very much in my mind .

But is there any means the second method can be addressed that's the question.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Yes, there is, but it's messy. It involves query INFORMATION_SCHEMA.COLUMNS and using the results of that to set up your output columns and so on dynamically. Far better to keep it simple in the way that steveb suggested.

John

rhd110 Ten Centuries Points: 1062 More actions · Answer 10

Thanks,

Can you please put some light on that / any link to that code will be helpfull.

I have been through this link before, didn't understand what in it

http://stackoverflow.com/questions/673808/adding-new-output-column-to-custom-data-flow-component-ssis

Thanks

rhd110 Ten Centuries Points: 1062 More actions · Answer 11

Thanks,

CAn you put some light on that / Any link to that code will also prove helpfull.

I have been through this link before didn't understand the code here

http://stackoverflow.com/questions/673808/adding-new-output-column-to-custom-data-flow-component-ssis

steveb. SSC-Forever Points: 46733 More actions · Answer 12

rhd110 (3/25/2011)
Thanks,
CAn you put some light on that / Any link to that code will also prove helpfull.
I have been through this link before didn't understand the code here
http://stackoverflow.com/questions/673808/adding-new-output-column-to-custom-data-flow-component-ssis

not sure what you mean?, as suggested keep in simple. esp. if you are struggling with code for dynamic columns.

overall i think it would take less time to create 10 data flows, than it would to build a loop with dynamic columns

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

rhd110 (3/25/2011)
I have been through this link before didn't understand the code here
http://stackoverflow.com/questions/673808/adding-new-output-column-to-custom-data-flow-component-ssis

You're making it too complicated. Forget script tasks - just set up your data sources like this

SELECT col1, RTRIM(col2), col3,....

FROM MyTable

WHERE...

This assumes col1 and col3 are numeric and col2 is varchar. Don't use SELECT * FROM - it's bad practice.

John