locked
Getting "SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED" when set as TransactionOption="Required" RRS feed

  • Question

  • Hi All,

    I've very simple ssis package which has sequence container in the Control Flow Then Inside the sequence container i'm using "DataFlow Task".Within the DataFlow Task ( Data Flow) i've 2 data flows , 1 has ADO.NET Source which is reading data from MSSQL DB & pass it to Data Conversion,then it'll update the destination table using ADO.NET Destination.The second DataFlow Task has ADO.NET Source whic read data from MSSQL DB via Sql Command , The Sql query is this

    "SELECT EXP_Discounts.* FROM  EXP_Discounts INNER JOIN EXP_DiscountsByLocations ON EXP_Discounts.Id = EXP_DiscountsByLocations.DiscountId WHERE EXP_DiscountsByLocations.LocationId = " + (DT_WSTR, 8)  @[User::LocationId]

    I've Int16 variable which i'm using for this query ( EXP_DiscountsByLocations.LocationId is SmallInt DataType Field ),the query is evaluating is successfull & every thing is works fine with TranactionOption="Supported". But the problem is when set to package & sequence container property TranactionOption="Required" it'll giving this warning "SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED" & package is not executing & all stops.

    So can anyone encounterd this type error or any suggetion u guys ? :)

    Thanks & Regards,


    Dimuthu

    Friday, September 14, 2012 1:59 AM

Answers

  • Hi friends,

    Finally it's done , what i did was

    1) change as TransactionOption = Required in the pakage level and all other levels such as sequence container,data flow its value is "Supported "

    2) change all Ado.net Data sources "Use Bulk Insert When Possible = False "

    3 ) change the settings on source data base server & destination data base server as follows

    Go to Administrative Tools-->Componenet Services -- > Computers -- >My Computer --> Distributed Transaction Coordinator , Then Right Click it and goto properties then switch to security tab ,Do the need full as follows

      * Check True Network DTC Access

     *  Check True Allow Remote Clients

     * Check True Allow Inbound

     * Check True Allow Outbound

     * Choose True No Authuntication required

     * Check Enable SNA LU 6.2 Transactions

    ***  Then confirm it’ll require the service restart.

    Please Refer this link if you need more infor http://blogs.technet.com/b/meacoex/archive/2011/03/20/bringing-the-transaction-mode-with-ssis-into-reality.aspx

    Thanks & Regards,



    Dimuthu

    • Marked as answer by Dimuthu Nuwan Thursday, September 20, 2012 4:56 AM
    Thursday, September 20, 2012 4:56 AM

All replies

  • Choose one or the other:

    Package - transaction required, Sequence Container - Transaction Supported; or

    Package - transaction not supported, Sequence Container - Transaction required

    Thing is if the package is at the tran required then all others need to join it if the setting is "supported" (default).


    Arthur My Blog

    Friday, September 14, 2012 2:21 AM
  • Hi,

    I would check the MaximumErrorCount in the package properties windows in BIDS. By default it is "1".


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Friday, September 14, 2012 2:25 AM
  • ys i set package transaction = required & Sequence container = Supported , but still same error..See the images below


    Dimuthu

    Friday, September 14, 2012 2:40 AM
  • ys set it as follows ,but still same error Sean ,see the pics

    Dimuthu

    Friday, September 14, 2012 2:42 AM
  • Did you check whether the Transaction Manager is available.I guess this could occur because the MSDTC Service is not running or running under invalid account.
    Friday, September 14, 2012 5:20 AM
  • Please find the below link for reference interms of transactions in ssis.

    http://microsoft-ssis.blogspot.in/2011/01/ssis-transactions.html

    Friday, September 14, 2012 5:36 AM
  • ys icheck this MSDTC is running and it's under "Network Services" account, so is this correct ?

    Dimuthu

    Friday, September 14, 2012 6:33 AM
  • Yes, if the MSDTC is running then all should be fine, I suggest you start with a new package and make it done using this post as a tip

    http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/


    Arthur My Blog

    Tuesday, September 18, 2012 2:47 PM
  • Hi friends,

    Finally it's done , what i did was

    1) change as TransactionOption = Required in the pakage level and all other levels such as sequence container,data flow its value is "Supported "

    2) change all Ado.net Data sources "Use Bulk Insert When Possible = False "

    3 ) change the settings on source data base server & destination data base server as follows

    Go to Administrative Tools-->Componenet Services -- > Computers -- >My Computer --> Distributed Transaction Coordinator , Then Right Click it and goto properties then switch to security tab ,Do the need full as follows

      * Check True Network DTC Access

     *  Check True Allow Remote Clients

     * Check True Allow Inbound

     * Check True Allow Outbound

     * Choose True No Authuntication required

     * Check Enable SNA LU 6.2 Transactions

    ***  Then confirm it’ll require the service restart.

    Please Refer this link if you need more infor http://blogs.technet.com/b/meacoex/archive/2011/03/20/bringing-the-transaction-mode-with-ssis-into-reality.aspx

    Thanks & Regards,



    Dimuthu

    • Marked as answer by Dimuthu Nuwan Thursday, September 20, 2012 4:56 AM
    Thursday, September 20, 2012 4:56 AM