CODEWITHSUNDEEP
c#etlssis
VInayK
VInayK

Reputation: 1559

How to read and change the column name in Script Component in SSIS

Source:ADO.Net(TSQL -query): Query will return the dataset(column names are months i.e. JAN FEB MAR APR JUN JUL .... DEC)

Destination: Excel

If you run the package it is transfering the data and the excel is having the column names like JAN FEB MAR .. DEC --> this is as expected result

New Requirement: 1.Declare a variable called "Year" and value is 2010. this may vary. 2. using the Script Component in data flow i have to change the column names to have _2010 at the end i.e JAN_2010 FEB_2010 MAR_2010 ... DEC_2010(2010 should come from variable) 3. what needs to be coded inside a method called "ProcessInputRow" in script component. Please update me how to achieve this i.e changing the input column names in Script Component and transfer that to destination?.

Thanks in Advance

Upvotes: 2

Views: 4004

Answers (1)

Jeff Hornby
Jeff Hornby

Reputation: 13640

You can't actually add columns or change the column names programmatically because the names of the columns will need to be static later on down the data flow.

What you can do is add new columns to your output by going into the Inputs and Outputs area on the Script Transformation Editor, opening the Output 0 and then adding 12 columns in the Output columns area. Then you would assign the data from each of your input columns to the appropriate output columns in your script.

This would be better accomplished with a Derived Column transformation.

All that being said, I have a feeling that what you really want is not JAN_2010, FEB_2010, etc. but the current year appended to the name of the month. You can't actually have dynamic column names flowing through your data flow.

However you could try the following: Excel column names are really just the contents of the first row of the excel spreadsheet. Create a flow that outputs a single row at the top of the spreadsheet with the correct data (use the script component as a data source) and then run your data flow to add the data.

Upvotes: 2

Related Questions