Sql-server – Validating Excel Counts against Database Inserts

sql serverssis

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?

Best Answer

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.

Related Solutions

Sql-server – SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

My original answer was working (changing int to nvarchar) but I ran into another column which contained dates in the excel source file where some cells contained the string "NULL" (I did not want to have dates in a NVARCHAR column.) When SSIS got to this date column it was generating an error because it could not convert the string "NULL" to a date. It was not properly reading the "NULL" as a NULL value, instead it was reading it as a string. The way I was able to resolve this issue is by adding a derived column component to the package that replaced the date field by using the following expression

(DT_WSTR,255)Auth_from_date == "NULL" ? NULL(DT_WSTR,50) : (DT_WSTR,255)Auth_from_date

this is just an IF statement that will change the string "NULL" to an actual NULL value. The date is then passed thru a data conversion component and converted into DATETIME format.

Copy data through SSIS package from one machine to another

Depending on how much data you are loading into SQL Server from Excel, you can choose different routes :

When designing SSIS package, you can use configuration file. The advantage is that your packages will be customizable, like when you are developing it can point to your local server and when you want to promote it to prod, you just have to change connection string to point to PROD server. Good example can be found here.
Use Bulk Insert to load data.
As @listik mentioned, you can use Import/Export wizard as well.
You can use linked server too as described here.

SSIS will be more flexible and customizable.

Best Answer

Related Solutions

Sql-server – SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

Copy data through SSIS package from one machine to another

Related Question