SSIS Script Component runtime error

2019-08-30 16:41发布

问题:

I got a valid issue when I run my package. It runs failed in my PC and success in anyone else. The error is caused by Script Component (turned red), and it is in Post Execute phase, not the post execute in the script componet, but in the runtime of package. The error is:

Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Error: 0xC0047062 at Data Flow Task, Script Component [263]: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{22992C1D-393D-48FB-9A9F-4E4C62441CCA}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

I guess the issue is related to variables, because when I remove all the code related to variables, the package run successfully. The code in script component:

private int scheduled550;
private int scheduled620;
private int scheduled720;
private int scheduled820;
public override void PreExecute()
{

    base.PreExecute();
    scheduled550 = Variables.Count550;
    scheduled620 = Variables.Count620;
    scheduled720 = Variables.Count720;
    scheduled820 = Variables.Count820;

}

public override void PostExecute()
{
    base.PostExecute();

}

Did anyone ever encounter the same issue? Can anyone tell me what will POST Execute phase do? Thanks

More info: I have tried to re-install SQL Server, but this is not help. And not all the script component with variables failed running in my SSIS (not in the same package with the error one)

回答1:

All the tasks/containers in an SSIS have the same lifecycle. You can see some of this by watching the Event Handlers fire. In a script component, inside a Data Flow Task, is going to under go various steps. Part of that is Validation (this contract says I should have a column from this table that is an integer type- can I connect, does it exist, is it the right type, etc).

After validation, tasks will have setup and tear down steps to perform. Since you appear to be working with SSIS Variables in your script, part of that pre/post execute time is spent allowing the translation of Variable (SSIS) to variable (.net) and back again.

Until I see the specific code in your PostExecute method that was causing the failure, I can't state what the code issue might have been.

I cannot recreate your issue. While this is the 2012 release of Integration Services, the Script Component as you are using it will behave the same. I did not send my output to Excel but that should not matter given it's the Script that is failing.

My Script component. I have selected my Variable, User::Count550 as a ReadOnly variable in the menu before editing my code.

public class ScriptMain : UserComponent
{
    private int scheduled550;

    public override void PreExecute()
    {
        base.PreExecute();
        this.scheduled550 = Variables.Count550;
    }

    public override void PostExecute()
    {
        base.PostExecute();
        //this.Variables.Count550 = scheduled550;
        //this.VariableDispenser.LockForWrite("Count550");
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (true)
        {
            this.scheduled550++;
        }
    }

}


标签: ssis