Measuring SSIS Data Flow Progress

Question

I am running an SSIS package to load, say, a million lines from a flat file that uses a script task to perform complex transformations and assign SQL Server tables. I am trying to find a better way (well, ANY way at this stage) to write the number of rows (possibly in thousands of 1000 to be more efficient) to another table while processing the data stream. This means that I can determine the percentage of progress over the entire task, which can take several minutes by simply querying the table periodically.

I cannot add SQL tasks to the stream, so I assume that the only way is to connect to the SQL database inside a .NET script. It seems painful, and I'm not even sure that it is possible. Is there an even more elegant way? I saw a link to the Rows Read performance counter, but I'm not sure where I access this in SSIS and still do not know how to write it to the SQL table during data stream processing.

Any suggestions appreciated.

Glenn

Answer 1

OK, finally succeeded ... added a call to the following sub in the script component:

Sub UpdateLoadLog(ByVal Load_ID As Int32, ByVal Row_Count As Int32, ByVal Row_Percent As Int32, ByVal connstr As String) Dim dbconn As OleDbConnection Dim Sql As String Dim dbcomm As OleDbCommand dbconn = New OleDbConnection(connstr) dbconn.Open() Sql = "update myTable set rows_processed = " & Row_Count & ", rows_processed_percent = " & Row_Percent & " where load_id = " & Load_ID & " and load_log_type = 'SSIS'" dbcomm = New OleDbCommand(Sql, dbconn) dbcomm.ExecuteNonQuery() dbconn.Close() dbconn = Nothing dbcomm = Nothing End Sub 

This runs every 1000 rows and successfully updates the table. The line already exists because it is created in the control flow at the beginning of the package and is updated again in the control flow at the very end with the final number of lines and 100%.

Thanks for all your suggestions guys.

Answer 2

There are two simple options here:

Option 1: Use the built-in login using SSIS and view the on progress event. it can be configured to write to several different outputs, including a relational database and flat files.

Read more here

Option 2: you can add an SSIS script component that could turn off notifications for an external system, such as a database table

Answer 3

I recently decided this a little differently, and I believe that it is better to use scripts and open separate connections in the code for DB:

• In the original query or conversion form, add the number of rows (incremental)
• In conditional branching, use an expression for modulation (%) for branching when the number is a multiple of 1000, but this can be configurable or based on the original data (for example, from 0.0% to 100.0% of the data)
• Create a log connection manager and use the destination. Control the dosage sizes so that the rows are immediately bound to the target table.

Answer 4

Why not write a .NET application, and you can integrate it into this to get information about where the SSIS package is located.

Basically, everything that is sent to the console you can receive, and there are event handlers to which you can connect to receive information during package processing.

Here is a link that can help you with this: http://www.programminghelp.com/database/sqlserver/sql-server-integration-services-calling-ssis-package-in-c/

Answer 5

Is an application using the number of lines in a .net application? When it comes to sharing information between applications, there are many accepted practices. Maybe you should take a look at them. And for your specific case, if it is a .net application that uses this line number to calculate progress, maybe you can store information in a different place than the database table, for example, file system, web service, Windows environment variables, log (like a Windows event log) etc. some of them crossed my mind. I think updating a Windows environment variable with a line count form with your script component would be a pretty good solution. Just like using a global variable to exchange data between two functions inside a program. :)