SSIS Script Component runtime error

Question

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)).

enter image description here

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)

So what is the code that was in PostExecute that is causing the data flow to fail? — billinkc, Jul 18 '13 at 01:54
The issue said : at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.Unlock() at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.UnlockReadOnlyVariables() at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute(); — morgan117, Jul 18 '13 at 02:12
But I don't have any code in my PostExecute, seems it's in the backstage. — morgan117, Jul 18 '13 at 02:13
So that's literally the only thing in your script task? You do nothing in your `Input0_ProcessInputRow` method? — billinkc, Jul 18 '13 at 02:20
The thing is whatever I write in Input0_ProcessInputRow (even if nothing), the issue still exists. — morgan117, Jul 18 '13 at 02:24
The issue disappear only when I remove all the variables in the "ReadOnlyVariables" Properties in the script component. — morgan117, Jul 18 '13 at 02:29
What else is your package doing? Are there other things in the Control Flow besides this Data Flow? You state it runs on other machines but not yours. In theory, you could have some defect with your specific combo of SQL Server and OS. On your machine, open Visual Studio/BIDS/SSDT, under the Help Menu, click About. What number is reported under `SQL Server Integration Services`? Run `dxdiag` (Start menu, Run, type dxdiag), click No on the verify drivers screen and what is reported back on the first screen? Compare that with a working machine's values — billinkc, Jul 18 '13 at 02:39
The variables is got from the database (sql server), that's the only thing more from my package. My SSIS version: Microsoft SQL Server Integration Services Designer Version 10.50.1600.1 . I think my PC is almost the same with other working machine. OS: Windows Xp． SQL　Server: 2008 R2 — morgan117, Jul 18 '13 at 03:13
You are on [SQL Server 2008 R2 RTM](http://sqlserverbuilds.blogspot.com/#sql2008r2) (release to manufacturing) which is 3 years and 49 public patches out of date. I strongly suspect the issue is going to be related to this but feel free to check the working machine's version. Even if it's not that, there are so many bug fixes between then and now you *really* want to patch. — billinkc, Jul 18 '13 at 03:33
Try to rebuild and save the script, run package again - it helped me a lot of times — makciook, Jul 18 '13 at 12:54

billinkc · Answer 1 · 2013-07-18T02:30:17.063

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.

data flow task

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++;
        }
    }

}

I really appreciate your help, but as I said, this issue is just appear in my PC, cann't reproduce in anyone else's PC, so do you think is there any config/regedit that can influence this？ — morgan117, Jul 18 '13 at 02:41

SSIS Script Component runtime error

1 Answers1

Linked