unable to cast com object of type 'system.__comobject' to class type 'system.data.sqlclient.sqlconnection

Question

0.00/5 (No votes)

See more:

Hi I have the following codes/scripts in my Script Component in data flow in my SSIS package.
However when I try to execute it, it shows me the above error message as the title.
Below are my codes:

C#

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.SqlClient;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;
    SqlDataReader sqlReader;

    public override void AcquireConnections(object Transaction)
    {
        connMgr = this.Connections.GsfConnection;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null); //error should happen here
    }

    public override void PreExecute()
    {
        base.PreExecute();
        SqlCommand cmd = new SqlCommand("EXEC usp_SSISPartNumber_Select " + "725", sqlConn);
        sqlReader = cmd.ExecuteReader();
    }

    public override void CreateNewOutputRows()
    {
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        while (sqlReader.Read())
        {
            {
                Output0Buffer.AddRow();
                Output0Buffer.NumberFamily = sqlReader.GetString(0);
                Output0Buffer.NumberInternal = sqlReader.GetString(1);
                Output0Buffer.ItemHeaderId = sqlReader.GetInt32(2);
            }
        }
    }

    public override void PostExecute()
    {
        base.PostExecute();
        sqlReader.Close();
    }

    public override void ReleaseConnections()
    {
        connMgr.ReleaseConnection(sqlConn);
    }  
}

I know that the problem happens at the "AcquireConnection" method, but I have not idea on how to solve it. Any help would be appreciated. Thank you.

Posted 24-Nov-15 23:33pm

Jamie888

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-11-24T23:57:00

We can't help you - we have no idea what datatype your manager.AcquireConnection is going to return.
So look at the intellisense for the method, and see what type it returns. If it's a generic type of some description then start by changing the code:

C#

object o = connMgr.AcquireConnection(null);

And put a breakpoint on it. Run your code, Execute the line using single step, and look at what type of value is in "o".

Be we can't do any of that for you!