I have to design a SSIS package which uses Excel Source files and inserts the data into SQL Server Database.

The problem is that I've created the packages top transfer data, but now I need to validate whether the Number of Rows in Excel File and the number of Rows in Database are the same. usually a single package has to process 3-4 Excel Files and each file has 5-6 sheets in it.

I've searched over the internet and found various ways one of which was that I use 'Row Count' component in Data Flow Tasks but I think that its a useless step since all the rows which will be read from the excel source will surely be transferred to the Destination. I want to know all the Excel Row Counts BEFORE the Data Flow Tasks are executed.

Is there a way to do this?

  • How would you identify the row count in Excel? Have you experienced an instance where the row count you're computing differs from what SSIS was able to extract? – billinkc Jan 22 '15 at 14:32

Not really.

Since Excel isn't a proper client server database, you can't ask it how many rows are there before bringing them in. You could bring them into a staging area and count rows as they come in. If they don't match and you need to handle that with a business process, the destination is untouched. If they meet your validation criteria, do a second transfer to the real destination. You can use a database table for this, or a raw file connection if you'd rather spill to the filesystem. Cache connection managers are useful if your dataset is reasonably sized.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.