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

Posted on 2004-11-29
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
    LVL 15

    Expert Comment

    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

    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

    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.
    LVL 18

    Author Comment

    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
    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

    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
    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

    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
    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

    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

    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!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    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…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now