Post reply

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0202009.

  • February 12, 2007 at 2:00 am

    #115519

    I am having problem while inserting data,

    for ex: i have two tables in two diffrent databses both are same structure tables, first time packege was excute sucessfully but second time it shows some error like:

    [Currency Destination [40]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "The statement has been terminated.". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Violation of UNIQUE KEY constraint 'AK_DimCurrency_CurrencyAlternateKey'. Cannot insert duplicate key in object 'dbo.DimCurrency'.".

     

     

    Kindest Regards,

    Sarath Vellampalli

  • February 12, 2007 at 3:49 am

    #689109

    hi

    r u inserting the same record once again. that might be causing the unique constraint violation.

    "Keep Trying"

  • February 12, 2007 at 4:02 am

    #689111

    hi Chirag,

    yes iam trying to insert same record once again because, one new record will be added to that table thats way iam excuted that same package. on that time iam getting this problem.in my case daily once excute that package, on that time it will be check all records add insert newly added record rest of all are no need to insert.

    which task is used for checking all records in the table

     

    can u send me solutions on the above threads

    Kindest Regards,

    Sarath Vellampalli

  • February 12, 2007 at 10:28 pm

    #689369

    hi

    i did not understand fully what u meant by your statement

    "yes iam trying to insert same record once again because, one new record will be added to that table thats way iam excuted that same package. on that time iam getting this problem".

    Anyway there is a Lookup transform in DataFlow task by which you can lookup records in a table.

    Hope This helps

    "Keep Trying"

  • February 13, 2007 at 12:56 am

    #689381

    use Slowly Changing Dimension. That should be what you are looking for.

    Jakub

  • February 13, 2007 at 2:48 am

    #689391

    thanks Jakub,

    i am trying to do but i don't no how to use Slowly Changing dimension task.

    can u send me the stps to use

     

    Kindest Regards,

    Sarath Vellampalli

  • February 13, 2007 at 3:47 am

    #689400

    Hi,

    this is quite a complex taks and you can do a lot with it. If you need to insert new records in your destination table and update existing ones (the most common goal I think) then the solution below is for you:

    1) add SCD task to the data flow 

    2) connect to data output from your source task

    3) double click on the SCD task that should run the wizard

    4) in the wizard select destination connection and a table and  change "Key Type" for columns acting as part of unique key in the destination table to "Business Key"

    5) on the next screen choose all "Dimension Columns" you want to be updated and select "Change Type" as of "Changing Attribute". Please make sure none of attributes is marked as "Fixed Attribute"

    6) On the next screens tick in "Changing Attributes" and don't tick "Enable inferred member support"

    Go to finish.

    The wizard should then create Insert and Update tasks itself.

  • February 13, 2007 at 4:23 am

    #689404

    thanks Jakub, Its working

    Kindest Regards,

    Sarath Vellampalli

  • February 13, 2007 at 4:45 am

    #689406

    you are welcome, Sarath

  • February 13, 2007 at 4:47 am

    #689408

    Hi Jakub,

    It is working but in my databse i have one table in that ID field is primary key and its identity property is set to "YES" in this case iam not able to set the ID field as "Business Key" . Because it was not showing in INPUT Columns in Slowly Changing Dimension Wizard.

     

    can u help on these cases. 

    Kindest Regards,

    Sarath Vellampalli

  • February 13, 2007 at 4:57 am

    #689409

    Sarath

    But in this case it seems using SCD is not relevant or you should reconsider what in fact is your business key in this case. As per definition identity column is not a part of Business Key.

  • February 13, 2007 at 5:11 am

    #689412

    Jakub,

    Can u send me the another way, insted of useing SCD there is any other Task is there. because in my database i am haveing all primary key fields as identity columns, so can u help on this.

    Kindest Regards,

    Sarath Vellampalli

  • February 13, 2007 at 5:36 am

    #689418

    Sarath,

    I don't know your database but seems to me you should forget identity columns when you want to update or insert records to your tables (assuming we talk about dimension tables). Just identify key columns like CurrencyId, Produc4tId etc. and treat them as business keys. The rest (ExchangeRate, ProductStatus) treat as changing attributes or attributes that don't change.

    Perhaps you may also need to review your database design in terms of data integrity.

     

  • February 13, 2007 at 6:02 am

    #689423

    Hi Jakub,

    this is my OLTP database example table, and i am having OLAP database with same table in this table ID field is identity column how can i set ID field as business keys in SCD.

    below is my ex: table

    ID        int                 NotNull

    Name   nvarchar(50)    NotNull

    RollNo   nvarchar(50)    NotNull

    Steps Are:

    1. drag and drop SCD

    2. Dublclick on SCD, it will open SCD wizard

    3. on that click next

    4. on select a dimension table and keys page, select Connction string after that select table or view

    5. After that input columns,dimesion columns,key type here how can i select ID as a  business keys,here only to columns are showing that is Name and RollNo but i want to Update Name and RollNo columns.how can I?

    Kindest Regards,

    Sarath Vellampalli

  • February 13, 2007 at 6:41 am

    #689433

    Sarath

    Looks that in SCD you can't set identity field as a Business Key what is even quite logical . Assuming you transfer data from OLTP to OLAP my suggestion is that you should set ID in the target tables not as identity with auto increment but as normal field populated from OLTP.

    Jakub

     

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply