CODEWITHSUNDEEP
sql-serverssis
Josh
Josh

Reputation: 367

SSIS: Adding multiple Derived Columns without using the gui?

I have about 500 fixed width columns in a flat file that I want to apply the same logic to to replace an empty column with null before it goes into the database.

I know the command to replace the empty string with null but I really don't want to have to use the gui to input that command for every column.

So is there a tool out there that can do this all on the back end?

Upvotes: 1

Views: 720

Answers (2)

Josh
Josh

Reputation: 367

My googlefu worked a little better after lunch.

I as able to modify about the 5th comment down on http://social.msdn.microsoft.com/Forums/sqlserver/en-US/222e70f5-0a21-4bb8-a3fc-3f365d9c701f/ssis-custom-component-derivedcolumn-programmatically-problems?forum=sqlintegrationservices to work for my needs.

My c# code will now loop through all the input columns from a "Flat File Source" object and add a derived column for each.

Upvotes: 1

billinkc
billinkc

Reputation: 61201

You could look at something like the EzAPI to create your data flow. This this answer, I have an example of how one creates a EzDerivedColumn and sets the formula within it.

Automatically mapping columns with EZApi with OLEDBSource

If you can install third party components, I've seen a number of implementations of a Trim-To-Null functionality on codeplex.com

BIML might be an option to generate your package as well. I'd need to play with that to figure the syntax though.

Upvotes: 1

Related Questions