I'm importing a (relatively) malformed CSV into SQL Server via SSIS. So far my workflow is:

  1. Import CSV as one column
  2. 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:

  1. Creating a DataTable
  2. Then iterating through single column rows, splitting, and populating DataTable
  3. 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?

  • My guess both methods will provide similar performance with attention to detail. In the case of the DataTable TVP, it is important to specify a MaxLength that matches the target table type column length for string columns, especially when a server side trace that captures RPC events is running. The default max length is -1 (2GB). – Dan Guzman Jun 23 '17 at 19:11
  • I've specified NVARCHAR(MAX) for all string types in the table-type. I could probably shorten that to NVARCHAR(250) or so and just truncate any longer values without anyone caring. What are the potential pitfalls of using NVARCHAR(MAX) in a TVP? – Zach Smith Jun 23 '17 at 19:27
  • I would avoid nvarchar(MAX) in TVPs when possible. See dbdelta.com/sql-server-tvp-performance-gotchas. – Dan Guzman Jun 23 '17 at 19:31
  • Thanks @DanGuzman. Looking at my code, I'm specifying the DataTable columns like so: _data.Columns.Add("LineCode", typeof(string));. And adding a C# string object to the cell for that column. So I'm not specifying a string length in the SSIS package. When the SP is called with a param (@param = _data), would the column definitions in the type have any effect on how the data is sent over the network? – Zach Smith Jun 23 '17 at 19:54
  • No, difference on what's sent over the wire except for the column meta data preceding the rows. For non-MAX strings, you should specify the max length with code like _data.Columns.Add("LineCode", typeof(string)).MaxLength = 250; – Dan Guzman Jun 23 '17 at 19:59

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.