Getting DTS to use destination table's default values for NULL values coming from source....

OK, we're using a DTS package to load a table with data from a tab delimited text file that is downloaded nightly.  The tab delimited file might contain empty values (two tabs next to each other) which will then try to load a NULL value into the destination table... but, we don't want to allow nulls in the destination table... rather, we'd like to insert whatever the table column's default constraint is... so, for a date field, we might want to insert '1950-01-01' as the default date whenever the source file has a null value in it.

Now, I know that we can highlight the mapping transformation that maps the column in the text file to our database column, edit it, turn it into an activeX script and write some code that says   "If IsNull(DTSSource[mydate]) then DTSDest[MyDate] = '1950-01-01' Else DTSDest[MyDate] = DTSSource[MyDate]"  or whatever the actual syntax is... the bad part about this that we'd have to edit about 100 of the mapping transformations and write that specific code....

All we really want to say is  "whatever the field is, if the source is null, use the dest's default constraint".... does anybody know if there is a property you can set on the package that will allow you to turn this on, or if there is some shortcut to writing all 100 individual field null check transformations?  Maybe something like "only include column in the load if it's not null... (because by not including the column in the insert, the default would be automatically used)"

LVL 18
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Set a default for each of the fields you are interested in, in the table, then change the field so it is no longer nullable.  SQL will then insert the default value.
mdouganAuthor Commented:
Well.. that's exactly what I did... but they still kick out on the first null... because as far as I know, column defaults are only used if you try to insert a row and do not reference that column at all in the insert.... however, if you reference that column and try to put a null in it, then it generates an error.

So, my guess is that DTS is generating a SQL Statement like:


Which will generate an error... however, if it created this statement


Then, the MYDATE column would pick up whatever default was defined in the destination table... but the problem is that some rows have a date, others are null... some rows the MYNUM field will be null, some not...  so, I was hoping that you could define at the field level surpress column if NULL or something along those lines
I'm sorry, I misunderstood your original post and thought I was answering correctly.

I think the best way is probably to use an ActiveX script.  The good news is that you can include all of the columns in a single activeX script that handles the mappings.  I think you assign the value DEFAULT when it is null, sorry I don't know another way for you.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mdouganAuthor Commented:
No worries... I'd wondered about that, whether that was a possibility or not, one ActiveX script that references all columns will be a bit better than individual scripts.  I'll wait to see if there are any other suggestions, otherwise, I'll give you credit for that.  Never knew that there was a keyword for DEFAULT like NULL... I'll have to try that... it could be useful, thanks!
Anthony PerkinsCommented:
If speed is not an issue, you could try using and ActiveX Script with a function such as this:

Function Main()
      If Not IsNull(DTSSource("SourceCol")) Then
         DTSDestination("DestinationCol") = DTSSource("SourceCol")
         DTSDestination("DestinationCol") = "Default values goes here"
      End If
      Main = DTSTransformStat_OK
End Function

Of course that means you have to hard-code your default values, which may not be such a good idea.  You could of course get a macro to record these functions and then insert them in one by one, either by browsing or copy and paste.
Why not load the data into a "temporary table"  (not a real temp table merely a table with the same schema except that it allows nulls and no defaults), you could then use the bulk insert task to accomplish this which will load the data much faster. Then using an Execute SQl task you can call a stored procedure which then loads the data from the "temp" table to the actual table, thus taking care of defaults etc.  When this task is finished you can then call an Exec SQL task to truncate the first table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
I agree with the above.  Whenever you are importing, it is always best to import into a temporary table first in order to validate the data first.  But I expect you knew that already <g>
mdouganAuthor Commented:
Hi, sorry for the delay, but I was trying out the various suggestions:

anthony, nice to hear from you (I haven't been on much lately)... yes, that code would definitely work, but that is what I was trying to avoid.... as there are between 100-200 fields, and I don't want to code all those transforms seperately.

KarinLoos, yours sounded really promising, so, I tried it out, but the step that loads the data from the "temp" table which allows nulls to the actual table still chokes on the error of trying to insert nulls into a column that doesn't allow it... I used an Execute SQL task, but rather than call a stored proc, I executed an "insert into actual_table select * from temp_table "  thinking that if I didn't specify the column names, maybe it would use the defaults... but it doesn't...  unless you can suggest a different approach for this step?...

SRigney, I tried combining your suggstion with KarinLoos, and instead of doing the select * I did this:

INSERT INTO actual_table
bla, bla, bla
from temp_table

Hoping that DEFAULT was a keyword that could be used to tell it to use whatever the default is on the column if NULL, but it didn't recognize that syntax.... I've already used the default keyword to create the defaults... I just want to pull the defaults out... I know I could write the default value in for each column, but again, that's what I'm trying to avoid.....

Anthony PerkinsCommented:
I am afraid, I am drawing a total blank on this.  And yes, you cannot use DEFAULT in the way you are attempting.

However, in the "Very Ugly Code" department you can try something like this:
You need to update the Columns that are null in the Temp_Table with the default value in the Actual_Table.
This can be done with the following function:

CREATE FUNCTION dbo.udf_GetDefault
            (@Column_Name sysname)

RETURNS varchar(50)


Return (Select Column_Default From INFORMATION_SCHEMA.COLUMNS where table_Name = 'actual_table' And Column_Name = @Column_Name)

You can then write some excessively boring dynamic SQL, such as this:

Declare @SQL varchar(8000)

Set @SQL = 'Update Temp_Table Set '

Set @SQL = @SQL + 'Col1 = IsNull(Col1, ' + dbo.udf_GetDefault('Col1')
Set @SQL = @SQL + 'Col2 = IsNull(Col2, ' + dbo.udf_GetDefault('Col2')
Set @SQL = @SQL + 'Col3 = IsNull(Col3, ' + dbo.udf_GetDefault('Col3')
Set @SQL = @SQL + 'Col4 = IsNull(Col4, ' + dbo.udf_GetDefault('Col4')
Set @SQL = @SQL + ')'

Select @SQL
exec (@SQL)

Select * from Temp_Table     -- See if it id what it was supposed to

Make sure you only include the columns that allow defaults or modify the above function and script.

After reviewing this crappy suggestion, you may want to go back to the modify the ActiveX script idea ... <g>

P.S.  Good to hear from you again.
The default constraint will only be applied if you dont specify the field in the insert statement (and then only if the column doesnt allow nulls). With other words
Column Doesnt allow nulls  + COlumn has Default definition -->   1.  Will insert the Default when NO entry for the column
                                                                                     -->    2.  Will return ERROR when trying to insert a Null
The only way thus to achieve your result would be loading the data via a "temp" table
Executing a stored procedure to insert into the Main table making use of code like anthony posted above

mdouganAuthor Commented:
I ended up using part of KarinLoos suggestion, about first importing to a temp table that allows nulls, then selecting each column into the actual table using an isnull but hard-coding the default... that was before seeing Anthony's suggestion, which I think is a good idea... maybe if I revisit the code, I'll switch over to something like that....

I will probably go back at some point and write a single transform method that does the same thing, but against the source file, as I'm not crazy about doing twice the number of inserts that I need to.  

I've split the points between the two... thanks for the great advice!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.