How to add progress counter in SSIS

Question

I am running a package which is extracting data from a single flat file and load it into the SQL Server destination. Flat file has more than 10,000 rows (it changes daily)

Is there any way I can find how much percent the task is completed? Like when developer/Support team execute a select statement on a table at an instance like

Select percentComplete from PackagePerformance Where packageId = 12

they get output like:

60%

which means 60% of data is loaded into SQL table at this instance.

Any suggestion?

Answer 1

So you actually can't tell anyone how close the package is to completion since SSIS doesn't know how many records are in the file. It only knows that once it's completely read in.

Here's a few things I'm thinking of

1) if you want to write out progress indicators, then use a script transform in your dataflow. You 'll need to script out the database connectors, etc. Google will be your friend there. You could write out an indicator every 1000 records.

1) If you really want a percentage complete then read the file in completely once. (just read it into a script task) It's really fast and will give you a record count.

2) Look at the import table you are using. 10,000 rows really isn't that many. I'm importing files that have well over 1,000,000 in less than 60 seconds. I'm also not using a bunch of transforms in the dataflow though. That might be one of your issues.