• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 838
  • Last Modified:

Insert Error on SQL DTS Import

DTS Package
SQL Server 2000 (Working with Test Database)
Pump Data From: StageFactCustSales to FactCustSales
Problem field: CustomCategoryDesc varchar(25) NOT NULL

I have a very large dts package that fails during the data pump step. It takes data from stage tables and imports them into fact tables.
The tables are identical except the stage table does not have an identity defined whereas the fact table does. Here is the error I am receiving:

Error Source:  Microsoft OLE DB Provider for SQL Server
Error Description:  The number of failing rows exceeds the maximum specified.
Insert error, column 26 ('CustomCategoryDesc', DBTYPE_STR), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.
Unspecified error

The source data (StageFactCustSales) in the CustomCategoryDesc field does not have any <NULL> values, it does have empty values though (where CustomCategoryDesc = ""). To my understanding empty strings are not considered NULLS (correct me if I'm wrong).

Lord knows I've been searching to see if anyone else has had the same problem and how it was resolved. I have only found one other similar issue; however it was regarding an identity column and this is not the same instance since CustomCategoryDesc is not an identity column.

**We even had a contractor come in and spent 8 hours trying to discover our problem to no avail. And yes, he still made $85 an hour.
Kudos to anyone who can offer suggestions!

-- Traci


0
SasDev
Asked:
SasDev
  • 6
  • 5
  • 2
  • +2
1 Solution
 
Chris MangusDatabase AdministratorCommented:
I don't believe you can insert a blank in a key field.
0
 
UnifiedISCommented:
Are you sure it's the null issue and not an index constraint?
Change your column (CustomCategoryDesc) on the fact table so it allows nulls and see if it works
Yes, null is different from an empty string
0
 
UnifiedISCommented:
I think you can insert an empty string in a key field once but the second will cause a duplicate error
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SasDevAuthor Commented:
CustomDategoryDesc is not an indexed field and it is not a key field. I also tried to set it to allow nulls...(although in the end result i really do not want it to allow nulls) and it failed to save the modification. It took a very long time to do the save beacuse we have millions of fields (maby the save just timed out?). Any other suggestions?
Thanks for the help!
-- Traci
0
 
SasDevAuthor Commented:
I meant we have millons of rows (sorry)
0
 
SasDevAuthor Commented:
More Info: When I right-click and execute the step it doesn't process for a while and then fail, it immediatly fails.  
0
 
UnifiedISCommented:
Can you limit your source data by excluding nulls or selecting only some records?
Can you show us the sql?
0
 
UnifiedISCommented:
In both the insert and select are you explicitly naming the columns (as opposed to select *).
0
 
SasDevAuthor Commented:
Here is the code within the dts transformation (AxtiveX Script)
'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************
'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()
      DTSDestination("audit_id") = DTSSource("audit_id")
      DTSDestination("ItemTax") = DTSSource("ItemTax")
      DTSDestination("Commission") = DTSSource("Commission")
      DTSDestination("CreditInvoiceFlag") = DTSSource("CreditInvoiceFlag")
      DTSDestination("BuyerCost") = DTSSource("BuyerCost")
      DTSDestination("InternalCost") = DTSSource("InternalCost")
      DTSDestination("Source") = DTSSource("Source")
      DTSDestination("CustomCategoryDesc") = DTSSource("CustomCategoryDesc")
      DTSDestination("CustomCategoryStyle") = DTSSource("CustomCategoryStyle")
      DTSDestination("CustomCategoryCode") = DTSSource("CustomCategoryCode")
      DTSDestination("ShippingWarehouse") = DTSSource("ShippingWarehouse")
      DTSDestination("CostAllowances") = DTSSource("CostAllowances")
      DTSDestination("ExtendedCost") = DTSSource("ExtendedCost")
      DTSDestination("ExtendedRetail") = DTSSource("ExtendedRetail")
      DTSDestination("OtherCharges") = DTSSource("OtherCharges")
      DTSDestination("TelxonFees") = DTSSource("TelxonFees")
      DTSDestination("FreightServiceFees") = DTSSource("FreightServiceFees")
      DTSDestination("SalesTax") = DTSSource("SalesTax")
      DTSDestination("Deposits") = DTSSource("Deposits")
      DTSDestination("MarketingAllowances") = DTSSource("MarketingAllowances")
      DTSDestination("ExtendedPromotions") = DTSSource("ExtendedPromotions")
      DTSDestination("ExtendedPrice") = DTSSource("ExtendedPrice")
      DTSDestination("QuantitySold") = DTSSource("QuantitySold")
      DTSDestination("QuantityOrdered") = DTSSource("QuantityOrdered")
      DTSDestination("UnitOfIssue") = DTSSource("UnitOfIssue")

      DTSDestination("FiscalDateWK") = IsNullDefault(DTSLookups("GetFiscalDateWK").Execute(DTSSource("FiscalYear"), DTSSource("FiscalMonth"),DTSSource("FiscalWeek")),0)

      DTSDestination("SalesRepWK") = IsNullDefault(DTSLookups("GetSalesRepWK").Execute(DTSSource("SalesRep")),0)

      DTSDestination("ItemWK") = IsNullDefault(DTSLookups("GetItemWK").Execute(DTSSource("Item"),DTSSource("ChargeType")),0)

      DTSDestination("InvoiceLine") = DTSSource("InvoiceLine")
      DTSDestination("Invoice") = DTSSource("Invoice")

      DTSDestination("CustomerWK") = IsNullDefault(DTSLookups("GetCustomerWK").Execute(DTSSource("Customer")),0)

      DTSDestination("SalesDateWK") = IsNullDefault(DTSLookups("GetSalesDateWK").Execute(DTSSource("SalesDate")),0)

      Main = DTSTransformStat_OK

End Function

Function IsNullDefault(vValue,vDefault)
      If IsNull(vValue) or IsEmpty(vValue) Then
            IsNullDefault = vDefault
      Else
            IsNullDefault = vValue
      End If
End Function

0
 
UnifiedISCommented:
You said this was test data right?
What happens if you try to insert the data with a query:
insert into FactCustSales (CustomCategoryDesc)
SELECT CustomCategoryDesc FROM StageFactCustSales

I wonder if Column 26 in the error message is a clue
Do any of the other functions that are called use CustomCategoryDesc?
0
 
nmcdermaidCommented:
Along the lines of UnifiedIS suggestion....

I suggest you order the input data by some key field, then put a filter on that field. Its painful but this way you can find the exact row that is causing the error. When you see the actual row you might slap your forehead.

The very first thing I would do is actually limit it to the first row and see if the error still occurs.

..... why is it that that you can be an incompetent contractor and still make a living.....


also, if you untick 'check constraints' (which I think is on the data pump somewhere) but leave the field as non nullable, do you still get an error?
0
 
SasDevAuthor Commented:
FIX: Since this was a test database I ended up blowing away the table and importing it from the live database. Executed the step again with success.
Thank you for all of your suggestions and help!
0
 
SasDevAuthor Commented:
This problem ended up being caused by a transformation configuration error via ETL program.
0
 
Chris MangusDatabase AdministratorCommented:
No objections.
0
 
modus_operandiCommented:
Closed, 500 points refunded.
modus_operandi
EE Moderator
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now