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.

Side note:

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.

  • Have you looked into python's pandas library? I'd smush it through a data frame and see what happens – James Oct 24 at 20:37
  • @James I don't know python, but after this is all said and done that will become a priority as that seems to be the most logical solution. – Tyler Raber Oct 25 at 18:58
  • I would try loading it into the sql server as it is, then converting it after its in. You'll get some more helpful error messages at the very least. Once it's in, you can use dynamic sql to recast all the columns. – James Oct 25 at 19:49
  • @James I did think about doing a staging table with all nvarchars and then processing from there... I have noticed the error messages in SSIS aren't the most friendly. I will keep this in mind. I appreciate the feedback. – Tyler Raber Oct 28 at 14:06
  • 1
    You can also try BIML, it will allow you to easily generate required tasks: bimlscript.com – Piotr Nov 10 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.