I'm new to SSIS and I'm starting with 2019.
I'm getting a csv with 330 million rows and some of the data is dubious. Some rows I can identify as bad based on a single column value. Other issues I wanted to detect related to duplicated rows. As far as I was trying to get was sequential duplicated rows; to do that I created a couple of package variables to remember a couple of columns from the last row processed for comparison.
My problem is that the package is running pretty slowly - about 600 rows per second - and I was trying to put my finger on the most time-consuming part.
My data flow is like this:
- a Flat File Source component reading the csv feeding a
- conditional split component with a condition that checks a couple of columns for bad data and compares against the package variables for last row. Bad rows go out one branch and get saved to a bad rows table through an OLE DB Destination; good rows go on to
- A script component that catches and translates the MySql \N null representation to a real null, and then updates the package variables for duplicate detection. Then the row goes to
- A data conversion component to turn the csv string representation of some columns to bits and ints, etc, then
- an OLE DB Destination to get saved to the good rows table
I'm running the package in Visual Studio for testing, and I've noticed that whenever I hit a patch of bad data that the conditional split can immediately detect, it goes very quickly. It's when I process the good rows through the script component etc that the rate slows to a crawl.
A) I don't see in Visual studio how to get a more accurate sense of where the time is going in each component (is it the script component slowing things down? Or the OLE DB destination?) Are there some tricks I'm missing on how to get a better picture of where the time is going?
B) All of the articles and Stack posts I've read so far vis a vis the OLE Db Destination seem to refer to settings that are no longer exposed (for example, the older docs and articles talk about "Rows per batch" and "Maximum insert commit size" as separate settings but all I see in 2019 is "FastLoadMaxInsertCommitSize") or moved to other presentations. So I'm not clear if I should knock down FastLoadMaxInsertCommitSize to 20000 or something to improve the throughput, since there doesn't appear to be a "Rows per batch" setting anymore.
Not seeing where all the time is going, I was thinking of adding a "bad flag" column to the Flat File Source component and then moving the script component above the conditional split (thinking maybe the "good" path is so slow because it's getting hung up in the long tail down to saving in the db.
The data is running about 20% bad but the good path is the one that seems the slow.
Any tips for figuring out where the time is going or the best way to organize my workflow?
Updates via comments
I've been taking your advice and trying the timings while chopping things back. First, a bit about the environment. The file is local to the system I'm running Visual studio on, as is the Sql Server instance, so there's no network latency
Chopping it back to just the flat file source and conditional split (dead ending in Row Counts), I got ~4.85 million rows / minute processed.
Adding the script transform (and still dead ending in Row Counts), it ran at ~3.75 M rows / minute
Adding the Data Conversion blocks, it ran at ~1.78 M rows / minute
The full package storing to a local Sql Server was running at 33-36 K rows / minute. So the big (a few orders of magnitude) slow boat was the OLE Db destination. The Data Conversions cut it in half. The script block was about a 23% hit.
The destination table is a heap with no indexes at all. No TEXTs or NTEXTS. One nvarchar(512), a couple of ints, a bunch of varchar(128) is the layout of the table
Oh, and I noticed the difference on the batch size parameters was the difference between the Editor and the Advanced Editor. Editor has 2 params (Rows Per Batch and Maximum insert commit size); Advanced Editor drops the first and renames the second.
I went to implement the "Maximum insert commit size" change you suggested, and I noticed in my "good" record ole db destination somehow it got flipped off of "OpenRowset - fastload" to just old "OpenRowset". Don't know how or when that happened, but I flipped it back and added your change and it's running much faster. Looks like it will finish in about 5 hours rather than 5 days this time.