DTS and Nulls - Active X
-
I have a DTS Package...when I test it with a few records everything is fine. When I increase my scope I get tons of null records. Do I need to check everything for nulls and convert it to something standard ... does DTS Active X hate nulls?
-
I take it you have something like this:
DTSDestination("Col1") = DTSSource("myCol")
All you need to do is to assign a value to the source if it is null. For example, if the above source was a number you could use:
DTSDestination("Col1") = IsNull(DTSSource("myCol"),0)
This will return a value of 0 for every source that is null.
------------------------------The Users are always right - when I'm not wrong!
------------------------------
The Users are always right - when I'm not wrong! -
Can I do
IsNull(DTSSource("myCol"),"")
To default in a nothing with no problem
-
Since I made this change I get 'Unspecified error' when running the package:
Question: How do I find the true meaning of unspecified error messages
Here is what I put in:
DTSDestination("SSN") = IsNull(DTSSource("SSNorization#"),"NO SSN")
DTSDestination("DateDone")= IsNull(DTSSource("Processed_Date"),"NO DateD1")
DTSDestination("DateDoc") = IsNull(DTSSource("Date_Filled"),"NO DateD")
DTSDestination("StoreBasisOfCost") = IsNull(DTSSource("Store_Basis_of_Cost"),"")
DTSDestination("StoreAmount") = IsNull(DTSSource("Store_Store_Amount"),0) / -100.00
DTSDestination("StoreGrossAmount") = IsNull(DTSSource("Store_Gross_Amount"),0) /-100.00
DTSDestination("StoreIngrCost") = IsNull(DTSSource("Store_Item_Cost"),0) /-100.00
DTSDestination("PlanBasisOfCost") = IsNull(DTSSource("Plan_Basis_of_Cost"),0)
DTSDestination("PlanStoreAmount") = IsNull(DTSSource("Plan_Store_Amount"),0) /-100.00
DTSDestination("PlanGrossAmount") = IsNull(DTSSource("Plan_Gross_Amount"),0) /-100.00
DTSDestination("PlanIngrCost") = IsNull(DTSSource("Plan_Item_Cost"),0) /-100.00
DTSDestination("SubmitIngrCost") = IsNull(DTSSource("Sbmt_Item_Cost"),0) /-100.00
DTSDestination("SubmitUC") = IsNull(DTSSource("Sbmt_U&C"),".00") / 100
DTSDestination("DrugKey") = IsNull(DTSSource("NDC_Number"),"No NDC")
DTSDestination("MCSCCode") = IsNull(DTSSource("MCSC_Code_(4-10_of_P"),"NOMCSC")
DTSDestination("Plan_Discount_Amount")= IsNull(DTSDestination("Plan_Discount_Amount"),0)
DTSDestination("NetClaimAmount")= IsNull(DTSSource("Store_Item_Cost"),0) / 100
DTSDestination("NetClaimAmount")=(IsNull(DTSSource("Store_Item_Cost"),0) - IsNull(DTSSource("Plan_Discount_Amount"),0)) / 100
-
For all the ISNulls assigning non 0 I get the following error:
Wrong number of arguments or invalid property assignment: 'IsNull'
What do I do when I want to set the default value to other than 0.
-
Doing it that way does not alleviate the problems of null. You can put in a space between the doubole quotes to stop it.
However, you may want to check the restraints in the table you are trying to import into. Looks like something is stopping null values from ocurring.
------------------------------
The Users are always right - when I'm not wrong! -
There are no restraints I am aware of ... fields are money or character. Note: I get the Isnull error for all fields. Are you sure I can use this command passing it the DTSSource()?
-
ActiveX script in DTS does not use the T-SQL language, it uses VBScript or Javascript. So for using isNull in VBscript try following:
If isNull(DTSSource("myCol")) Then
DTSDestination("myCol") = "Your custom value"
Else
DTSDestination("myCol") = DTSSource("myCol")
End If
I hope this will solve your problem.
-Kamlesh
-
Whoops!
Apolgies, helpmeinohio.
Been working on sql past few weeks and totally forgot. See kamleshagnihotri post as is totally correct.
------------------------------
The Users are always right - when I'm not wrong! -
Unfortunately the ISNULL in DTS uses vbScript not SQL so the use should be like this
IF ISNULL(DTSSourceColumnName) THEN
DTSDestinationColumn = " "
ELSE
DTSDestinationColumn = DTSSourceColumn
END IF
Hope this helps!
-
Problem solved. Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply