SSIS C# Script Task Error

Question

All, I'm using SQL 2014 and Visual Studio 2013.

I have a script task that is not firing. It's being read, but not working. This task creates tables and inserts data into them. I need to do this with a script task as there are 100's of TSV files and the fields may change month to month and it's a pain maintaining individual nodes for each table.

If you look at the code snippet, the message boxes (1) do fire, but the script errors right after - I believe at (2):

The error message is:

I think this error refers to variables that are not accessible in the task or are misspelled, etc. I've checked these Ad nauseam - don't think that's it.

Any help is greatly appreciated. Thank you.

Could the issue be that I have an oledb connection AND a ADO connection happening at the same time? Could one step on the other? — Craig
– Craig, Commented Jun 20, 2018 at 21:54
Several comments Your error tells about missing element in collection, and looks like it occurs in accessing variable. If you comment out all references to variables, will the exception still fire? Try to set a breakpoint at the first line of script task and step it untill it crashes to identify the place. Your connection manager DBconn - is it ADO.NET or OLEDB? — Ferdipux
– Ferdipux, Commented Jun 21, 2018 at 6:05

Ferdipux · Accepted Answer · 2018-06-22 11:30:15Z

2

The problem in your code is that you are creating an ADO.NET (C# standard) connection in your script code, but the base of this - DBconn connection manager - is an OLEDB connection manager. These two connections could not be casted into one another.

Suggestions:

If possible, create DBconn connection manager as an ADO.NET. Then your code should work.
In case you have to keep DBconn as an OLEDB connection manager, you have to create a SqlConnection connection in script task based on DBconn. I have done that building connection string for ADO.NET from OLEDB conn string and creating a new SqlConnection with that connection string.

Below is a code sample for function generating Connection String.

using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.OleDb;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;

static string Get_ManagedConnString(string Src_Name, ConnectionManager CM)
    {
    if (CM.CreationName != "OLEDB")
        throw new Exception(string.Format("Cannot get Conn String from non-OLEDB Conn manager {0}", CM.Name));

    RuntimeWrapper.IDTSConnectionManagerDatabaseParameters100 cmParams_Src = CM.InnerObject as RuntimeWrapper.IDTSConnectionManagerDatabaseParameters100;
    OleDbConnection oledbConn_Src = cmParams_Src.GetConnectionForSchema() as OleDbConnection;
    OleDbConnectionStringBuilder oledbCSBuilder_Src = new OleDbConnectionStringBuilder(oledbConn_Src.ConnectionString);
    SqlConnectionStringBuilder sqlCSBuilder_Src = new SqlConnectionStringBuilder();
    sqlCSBuilder_Src.DataSource = oledbCSBuilder_Src["Data Source"].ToString();
    sqlCSBuilder_Src.InitialCatalog = oledbCSBuilder_Src["Initial Catalog"].ToString();
    if (oledbCSBuilder_Src["integrated security"].ToString() == "SSPI")
        {
        sqlCSBuilder_Src.IntegratedSecurity = true;
        }
    else
        {
        sqlCSBuilder_Src.UserID = oledbCSBuilder_Src["User ID"].ToString();
        sqlCSBuilder_Src.Password = oledbCSBuilder_Src["Password"].ToString();
        }

    return sqlCSBuilder_Src.ConnectionString;
    }

edited Jun 22, 2018 at 11:30

answered Jun 22, 2018 at 7:13

Ferdipux

5,2791 gold badge22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Craig Over a year ago

Thank you for the detailed response and workaround. This will suffice - Curious, would a third solution be to create another ADO connection and refer to that new connection in the C# script above (swap out DBconn with ,say, DBconnADO?

Ferdipux Over a year ago

@Craig, certainly, it will do the trick. For a 1-time package its Ok, but for long-time support - not; maintenance of such things in production could break upon time. That is why I prefer to define 1 Connection Manager for 1 source, and derive from that.

Collectives™ on Stack Overflow

SSIS C# Script Task Error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related