Measuring Progress of SSIS Data Flow

Question

I am running a SSIS package to load say a million rows from a flat file, which uses a script task for complex transformations and a SQL Server table destination. I am trying to figure out the best way (well, ANY way at this stage) to write out to a different table the row count (probably in multiples of 1000 to be more efficient) DURING the data flow processing. This is so that I can determine the percentage of progress throughout a task that might take a few minutes, simply by querying the table periodically.

I can't seem to add any SQL task into the flow, so I'm guessing the only way is to connect to the SQL database inside the .NET script. This seems painful and I'm not even sure it is possible. Is there another more elegant way? I've seen reference to "Rows Read" performance counter but not sure where I access this in SSIS and still not sure how to write it to a SQL table during the Data Flow processing.

Any suggestions appreciated.

Glenn

Answer 1

there are two easy options here:

Option 1: use the built-in logging with SSIS and watch the on progress event. this can be configured to log to several different outputs including relational database and flat files

See more Here

Option 2: you could add a SSIS script component that could fire off notifications to an external system like a database table

Answer 2

I recently solved this in a slightly different manner, which I find superior to using scripting and opening separate connections in code to DBs:

• In the source query or a transform shape, add a row count (incremental)
• In a conditional branching, use a modulo expression (%) to branch whenever the number is a multiple of for example 1000, but this could be configurable or based on source data (for example 0.0% to 100.0% of the data)
• Create a log connection manager and use a destination. Control the batching sizes so that rows are immediately committed to the target table.