Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Retain NULL values vs Keep NULLs in SSIS Dataflows - Which To Use?

By Paul Ibison,

There is some confusion as to what the various NULL settings all do in SSIS. In fact in one team where I worked we had created 15 packages before realising the full implications of the various default settings. Anyway, hopefully this article will help to clarify a bit......

Suppose you are doing a bog-standard import of a text file like the one below. We have a data flow task with a flat-file source going to a OLEDB destination. The flat file contains 3 columns: MyDate, MyInt and MyText. In the file there are 2 rows - one containing normal values and the other one just containing blank column values. So, what happens in the case of a blank value?


Data flow

By default, what will happen is shown in the first dataset below (2nd row). A blank date will become "1st Jan 1753", an integer will become "0" and text will become "". Is this what you want? Usually not - especially if "0" is also a legitimate explicit numerical value in the text file!

Imported Data


So, we can designate that the null values from the flat file (really an absence of a value) will become a null in the resulting OLEDB destination. There is a little checkbox on the flat file source which controls this:

Transformation

The imported dataset will now look like this:

NULL Data

So far so good.

However there is another similar setting available here. This is a checkbox on the OLEDB destination labelled as "Keep NULLs" - what is that for?

Basically this controls what happens when you have defaults on the destination table. If there is a default constraint on each column –

eg

the date should be today's date,

the number should be -1

the text "n/a"

- do we want these default values to be overwritten by NULL values or to simply apply their specified defaults?

The relevant option is shown below. In this particluar case the defaults won't get applied and will be overwritten by NULLs from the source file.

Settings

If it was unchecked, and the defaults mentioned above existed, we would arrive at the values below.

Imported Data

Hopefully this makes these 2 setting and the various permutations s a little clearer Happy Face Paul Ibison, http://ssisblog.replicationanswers.com

Total article views: 4115 | Views in the last 30 days: 24
 
Related Articles
ARTICLE

Automate Default Constraints

One procedure that will take care of all default constraint values in the destination.

FORUM

Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

FORUM

SQL server become unresponsive

SQL server become very very slow

FORUM

DBCC SHRINKFILE shrinks below specified file size

Initial size vs. default size

FORUM

Dynamic Excel destination

Dynamic Excel destination

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones