I'm importing a (relatively) malformed CSV into SQL Server via SSIS. So far my workflow is:
- Import CSV as one column
- Use script component to clean up into 113 properly formed columns
Now I have a choice: I can configure the script component as either a 'transformation' or a 'destination' step. I have chosen 'destination', which involved:
- Creating a DataTable
- Then iterating through single column rows, splitting, and populating DataTable
- Calling SP in SQL Server with DataTable as a param
I have found this works with a sample of the CSV, but have not been successful with the entire CSV. I suspect this is because I'm developing remotely and the 50mb + DataTable upload is causing the package to time out.
So my question is:
Would using the SSIS
OLE DB Destination task be more reliable and faster in some way than what I'm currently doing?