Adding many output columns to script component

user_1034012 0
Viewed 112

I have a Data Flow with OLE DB Source, Script Component (Transformation), and Flat File Destination:

enter image description here

The OLE DB Source task has 100+ columns. The script component is going to cleanup data in each column and then output it to the Flat File Destination.

Adding output columns by hand in Script Component is unthinkable to me.

enter image description here

What options do I have to mirror the output columns with the input columns in the Script Component? While the output column name will be the same, I plan to change the datatype from DT_STR to DT_WSTR.

Thank you.

ssis
1 Answers

You are short of luck here. Possible scenarios:

  • Either you use Script Component and have to key in all columns and its properties manually. In your case, you have to set proper datatype.
  • Or you can create your own Custom Component which can be programmed to create output columns based on input columns. It is not easy and I cannot recommend a simple guideline, but it could be done. This might have sense if you have to repeat similar operations in many places so it is not a one-time task.
  • You can create a BIML script that creates a package based on metadata. However, the metadata (list of columns and its datatypes) has to be prepared before running BIML script or do some tricks to get it during script execution. Again, some proficiency with BIML is essential.

So, for one-time job and little experience with BIML I would go for a pure manual approach.

user_10106220
Related