none
Script Task code that has worked for years, no longer works in SSIS for SQL 2016 RRS feed

  • Question

  • Hi 

    This code :

                OleDbDataAdapter da = new OleDbDataAdapter();
                 DataTable dt = new DataTable( );

                da.Fill(dt, Dts.Variables["User::Versions"]);

    Has worked for years, but now throws an error that the object type is not ADODB.RecordSet???

    How do we make it work now?

    Any help much appreciated 


    CRM 4, SQL Server and .Net developer using C#

    Saturday, October 19, 2019 11:58 PM

Answers

  • Hi Yitzhak

    The only way I know of handling multiple rows in a dataset, in SSIS, is to use a Foreach loop task set to ADO Enumerator, is that what you are suggesting? 


    CRM 4, SQL Server and .Net developer using C#

    • Marked as answer by MayBoy Monday, October 21, 2019 1:40 AM
    Sunday, October 20, 2019 8:03 PM

All replies

  • Hi MayBoy,

    What is the change?

    Did you upgrade your SSIS environment to 2016 version?

    Sunday, October 20, 2019 12:25 AM
  • What is in Dts.Variables["User::Versions"]?

    My thought is that it contains the name you would like to use for your table, but since you're passing it as an object, that overload expects an ADO record set.  To resolve you would use

    da.Fill(dt, Dts.Variables["User::Versions"].ToString());

    Sunday, October 20, 2019 2:29 AM
  • Hi 

    This code :

                OleDbDataAdapter da = new OleDbDataAdapter();
                 DataTable dt = new DataTable( );

                da.Fill(dt, Dts.Variables["User::Versions"]);

    Has worked for years, but now throws an error that the object type is not ADODB.RecordSet???

    Please check if below blog helps:

    https://prathy.com/tag/ssis-object-variable/



    Regards,
    Vaibhav

    Sunday, October 20, 2019 11:17 AM
  • No, the environment was built in SQL 2016, but I hve used this code and technique on all previous versions without issue, this appears to be an articact of creating the package in a project that is tagged (SQL Server 2016)

    CRM 4, SQL Server and .Net developer using C#

    Sunday, October 20, 2019 6:33 PM
  • Thanks for the suggestion, but unfortunatly gives the same exception

    CRM 4, SQL Server and .Net developer using C#

    Sunday, October 20, 2019 6:34 PM
  • Hi MayBoy,

    Did you have a chance to try Vaibhav's suggestion: SSIS Object Variable?

    As an alternative, you may change your SSIS package design to eliminate a Script Task completely out of the picture while accessing your data source. SSIS has lots of source adapters.

    Sunday, October 20, 2019 7:05 PM
  • Hi Yitzhak

    The only way I know of handling multiple rows in a dataset, in SSIS, is to use a Foreach loop task set to ADO Enumerator, is that what you are suggesting? 


    CRM 4, SQL Server and .Net developer using C#

    • Marked as answer by MayBoy Monday, October 21, 2019 1:40 AM
    Sunday, October 20, 2019 8:03 PM
  • Hi MayBoy,

    Please share your SSIS package overall design. A screen shot of it will be helpful too.

    It is hard to suggest anything without that knowledge.

    Sunday, October 20, 2019 8:26 PM
  • Hi MayBoy,

    Please refer to the following link:

    System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, October 21, 2019 2:24 AM