SSIS Null inserts in tables

Question

I have a tab delimited text file. I am loading the text file intoaSQL Serve table. There are fields where there is no data and hence has to be inserted as NULL into the table. But however a 0 (zero) is inserted in its place. Note the default value for tht field is NOT set to any value. Can anyone let me know why this is happening?

Answer 1

use a derived column and write an expression to load "0" as default value if data is not there.. something like below..

Trim(coulnm1)=="" ? NULL , column1

---

Let us TRY this |

---

My Blog :: http://quest4gen.blogspot.com/

Answer 2

I think the question asked needs a solution for doing exactly the opposite of what is proposed in top: Inserting NULL instead of 0.

You have 2 choices:

1- Replace ADO .NET Destination with OLE DB Destination;

2- Add a Derived Column transformation after Flat File Source to convert 0 to NULL using the following SSIS expression:

column1 == 0 ? NULL, column1

Cheers!

---

Please mark as answer if this helps. Thank you.

http://thebipalace.wordpress.com

Answer 3

The zeroes are inserted as the Flat File Source replaces null values by 0 when fetching the data. If you do not want this, simply select the checkbox at
"Retain null values from the source as null values in the dataflow". That should solve your problem.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 4

I am using the OLE DB destination as the end point in data flow task. The problem is that when i view the data in the data viewer just after the Flat file source, a zero is read instead of NULL. In such a case i cannot use a derived column transformation too, as there is no blank record coming in. I guessing if there are any other propert setting that needs to be done.

Answer 5

I am using the OLE DB destination as the end point in data flow task. The problem is that when i view the data in the data viewer just after the Flat file source, a zero is read instead of NULL. In such a case i cannot use a derived column transformation too, as there is no blank record coming in. I guessing if there are any other propert setting that needs to be done.

Did you try my suggestion to set the "Retain null values from the source as null values in the dataflow" in the Flat File Source?

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 6

Thanks Koen. It worked!!! I had missed to check it.

Answer 7

Thanks Koen. It worked!!! I had missed to check it.

Great! Glad you could solve your problem!

---

MCTS, MCITP - Please mark posts as answered where appropriate.