Null or Empty Value in SQL Server Integration Services

By : Kasim Wirama, MCDBA, MCITP

It is quite common you receive flat file where contains columns list where some of them don’t have value. Such as this example below :

customerid,firstname,middlename,lastname
001,John,,Smith
002,Alex,Leef,Junior

Save the file in c:\customer.txt for demo purpose.

Notice that middlename column for customerid 001 has no value. Given you would like import into customer table with following structure :

create table Customer

(

customerID char(3),

firstname varchar(10) not null,

middlename varchar(10) null,

lastname varchar(10) not null

);

Create the table in tempdb database.

Notice that middlename column allows NULL value. By default importing no-value column into nullable column in a table, SSIS will put blank value (empty value for string, 0 for numeric) instead of NULL value; but you could put NULL value instead of blank value. I show you how to do this.

Fire up your Visual Studio 2008. Go to File menu > New > Project. Give the project name with “NullabilityDemo” (without double quotes) and click okay as presented on figure 1.

Figure 1

You will be presented will blank area of Control Flow designer on Package.dtsx. Click on Data Flow Task on Control Flow Items and drag into Control Flow Area as presented on Figure 2.

Figure 2

Double click on the dragged and dropped Data Flow Task instance in Control Flow area and your designer will be switched into Data Flow Designer. Drag and drop Flat File Source (located on Data Flow Sources) into Data Flow designer as presented on Figure 3.

Figure 3

Double click on Flat File Source instance, you will be presented on Flat File Source Editor. You need to define Flat File Connection Manager.Because currently no Flat File Connection Manager, you click New button as presented on Figure 4.

Figure 4

Enter Connection Manager Name, locate flat file full path on File Name textbox and tick “column names in the first data row” as presented on Figure 5.

Figure 5

You choose Columns item on left pane, you can see how your flat file content looks like before importing into destination. You choose Comma for column delimiter because the source file in comma delimited format. See figure 6 below.

Figure 6

Click OK twice to go back to Data Flow designer, drag and drop SQL Server Destination component, connect green arrow from Flat File source into SQL Server Destination. Double click on SQL Server Destination item, you will be presented on SQL Destination Editor, click New button on connection manager. In Configure OLE DB connection manager, click New button then fill out Server name, direct to tempdb database and click “Test Connection” button in Connection Manager dialog box, and click OK twice to go back to SQL Destination Editor dialog box as shown in Figure 7.

Figure 7

Select Customer table on “Use a table or view” as shown in Figure 8.

Figure 8

Choose Mappings item on left pane as shown in Figure 9.

Figure 9

Click OK button to close the dialog box and right click on blank area and choose “Execute Task” button as presented on Figure 10.

Figure 10

Open SSMS, and do query customer table. As you see that there is empty string value for middlename of customerid 001 as shown in Figure 11.

Figure 11

Go back to the visual studio, double click on Flat File Source component and tick “Retain null values from the source as null values in the data flow” as presented on Figure 12. Click OK button and execute task once more.

Figure 12

After execution finished, go back to SSMS and re-execute the query. What do you find out? Yes, correct, there is NULL value on middlename column for John Smith as shown in Figure 13.