Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2004-11-29
Medium Priority
Last Modified: 2008-01-09
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)"

Question by:mdougan
  • 4
  • 3
  • 2
  • +1
LVL 15

Expert Comment

ID: 12698793
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.
LVL 18

Author Comment

ID: 12699113
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
LVL 15

Expert Comment

ID: 12699335
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.
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

LVL 18

Author Comment

ID: 12699778
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!
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12702427
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.
LVL 13

Accepted Solution

KarinLoos earned 1000 total points
ID: 12704709
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.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12706006
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>
LVL 18

Author Comment

ID: 12710768
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.....

LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 12711853
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.
LVL 13

Expert Comment

ID: 12713784
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

LVL 18

Author Comment

ID: 12716219
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!


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question