I have to insert a large UTF-8 encoded flat file to a 1252 encoded sql server. In order to do so, I am using SSIS to type cast each column. Is there a way, other than copy pasting through all 400 columns, to create/edit a derived column transform from text/pragmatically? I cannot use bulk insert. BCP is an option, but I had issues getting that to work.
This is for replicating Salesforce to SQL Server. Other than pre-built solutions DBAmp, Skyia etc..., I'm open to other options. I need to make this happen quickly due to business demands.