How to Read Null Values From Excel Source in Ssis
SSIS – Remove empty rows from Excel import
From the first time that I started SSIS, I started to beloved information technology. In about cases it'southward easy to create a packet, easy to understand, and fifty-fifty readable for people who don't "speak fluent SQL". But what if yous want to perform an easy chore, and the event isn't what y'all expect?
Formatting an Excel sail
One of the almost basic tasks you can create in SSIS, is importing an Excel sheet. Nigh of the fourth dimension this works like a charm. But in my case, I wanted to filter out some rows from the workbook.
The business organisation delivers an Excel canvass, that needs to be imported into the database. Only because they don't have the technical cognition we take, they don't know how important the format of the file is. They sent us this file (I've created a smaller sample, so it'southward easier to read and empathize):
The first thing you lot'll find every bit a data-professional is the ii empty rows in the sail. Abreast that, nosotros have an employee without a name. You know this is going to cause issues when you run into it. These errors are easy to spot in the example, but imagine if these ii rows are hidden in a dataset with l.000 or more rows. So fifty-fifty though they might concluded up there accidentally, your procedure is going to fail.
When you lot add an "Excel Source" to your packet, and y'all expect at the preview of that import, y'all immediately see the problem:
Table construction
In order to make up one's mind what columns can be left blank, and what columns tin't be Zip, I looked at the table construction:
CREATE TABLE ResultSSIS (ID INT IDENTITY(1, 1), FullName VARCHAR(l) NOT Nothing, Department VARCHAR(fifty) NULL, EmployeeNumber INT NOT Cipher)
And so in the dataset, FullName and EmpolyeeNumber are mandatory, and Department is optional. With this in heed, I started to work on a way to exclude those rows.
Import without excluding
The first thing I tried is to import the file, and see what the results are. Because I knew the data wasn't correct, I didn't want to import the Excel sheet into a SQL Server database only yet. So as a destination, I used the "recordset destination" control in SSIS. Importing the data into this retentivity table besides immune me to use the "data viewer" to see the imported data, without the need to truncate a table afterwards each run. You can enable the "data viewer" by right-clicking the import-connector (the pointer between controls), and click "Enable Information Viewer":
If y'all run the SSIS bundle in debugging mode, you lot'll see the data that is imported in a pop-upward window:
Every bit you can encounter in the screenshot above, the records with Goose egg values in information technology are included in this import. So which records do we want to exclude, based on our table construction?
So from the vi records in the Excel sheet, we want to exclude 3 in our import considering of NULL values. Merely how do we do that? The easiest way to solve it, is to import it into a temp table, delete the Nix records, and insert the other records in the destination tabular array. But what if that isn't possible, and you want to filter the records in your import? I've chose to use the "Conditional Split".
Provisional Dissever
You don't have to rebuild your whole package, when y'all want to exclude records with the "Provisional Split". You can but add together this control, at to the lowest degree in this case, in between you source file and your destination. If you open the command, you can add an expression that is used to filter records. In my case, I wanted to exclude the rows with an empty "FullName" and "EmployeeNumber":
When connecting your "Conditional Split" to your destination, SSIS volition enquire y'all what output the "Conditional Split" needs to return. To output the entire set without the empty rows, chose the "Conditional Split Default Output":
When you run your package with the extra "Conditional Separate" (and you lot enable Information Viewer again), you'll encounter the filtered output of the "Conditional Divide". The three Goose egg records are excluded like expected:
Conclusion
SSIS is piece of cake to employ, and yet a a really powerful tool. Even if you lot build your processes in SSIS, it'due south not always necessary to rebuild your whole bundle. Sometimes y'all can relieve the day with merely a minor change. That'southward the power of SSIS!
Source: https://devjef.wordpress.com/2014/02/05/ssis-remove-empty-rows-from-excel-import/
0 Response to "How to Read Null Values From Excel Source in Ssis"
Post a Comment