Post reply

ADO.NET connection manager can't run Execute SQL Task

  • April 28, 2014 at 1:56 pm

    #286320

    Hello,

    I am working on a SSIS package. I wish to use the Connection Manager (CM)'s Connection String for my DB reading in my script tasks. After doing some research it seems this is best done if the CM is ADO.NET.

    So far I have got an OLE CM to work for Execute SQL tasks that take place before my first Script Task. However, when the CM is ADO.NET I get strange errors. For example, I have an Execute SQL task that feeds a Foreach loop. The SQL for it is simple: select top 1 staff_id from staff_table. This Execute SQL task will stuff the value into a parameter of type object named objStaff_ID. The Foreach loop uses this object variable. When I run the package using the ADO.NET CM for the first Execute SQL task, I get this fun error message: "Failed to create an IDataAdapter object. This provider may not be fully supported with the query, 'ResultSet' property not set correctly, parameters not set correctly, or connection not established correctly."

    I haven't figured out how to avoid this error so far. So, first question is how do I use an Execute SQL task to feed a Foreach loop using a ADO.NET CM? The second question is it generally considered a reasonable practice to employ ADO.NET CMs? I really like the potential of using a CM for DB access in a Script Task, which means I only have to configure the CM for everything to run.

    Thanks,

  • April 28, 2014 at 2:17 pm

    #1709268

    are you passing the value of the select statement to a result set or a Output parameter? Using a result set would be the best option to pass it to a for each loop and then to individual parameters...

    Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

  • April 28, 2014 at 2:25 pm

    #1709272

    Thanks, a4apple. During the course of trying to get it to work I had parameter mapping in the Execute SQL task as well as a result set. I think this was the problem. I removed the parameter mapping and suddenly it works.

    Appreciate the help!

Viewing 3 posts - 1 through 2 (of 2 total)

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