SSIS Web Service Script Component (The given key was not present in the dictionary)

Question

I'm sorry if this question has been answered already, but I've done some research and have yet to find something that makes sense.

I'm creating an SSIS script component that connects to a web service and is to dump data into a SQL table. Though the script component is still a work in progress I currently get the following error: at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at ScriptMain.CreateNewOutputRows() at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers, OutputNameMap OutputMap) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

Here's the core of the code that I'm using for the script component:

  public Dictionary<string, int> agentStatisticsColumns = new Dictionary<string, int>();

public override void CreateNewOutputRows()
{

    //setSessionParameters sessionParams = new setSessionParameters();
    //sessionParams.viewSettings = new viewSettings
    //{
    //    appType = "Custom",
    //    forceLogoutSession = false,
    //    idleTimeOut = 600,
    //    rollingPeriod = rollingPeriod.Minutes5,
    //    shiftStart = 28800000,
    //    statisticsRange = statisticsRange.CurrentDay,
    //    timeZone = -25200000
    //};


        getStatistics statistics = new getStatistics();

        statistics.statisticTypeSpecified = true;
        statistics.statisticType = statisticType.AgentStatistics;
        statistics.columnNames = null;

        getStatisticsResponse resp = new getStatisticsResponse();

        statistics statistics_return = resp.@return;

        int usernameIdx = agentStatisticsColumns["Username"];

        foreach (row r in statistics_return.rows)
        {
            Output0Buffer.Username = r.values[usernameIdx];
            //string log = r.values[usernameIdx];

            //Output0Buffer.Username = log;
        }

}

Any help would be greatly appreciated!

Personally I think that if you are just loading web service data into a table, it'll be a lot easier if you just build a console app to do it. The SSIS scripting task is missing a lot of useful debugging tools, and there are some gotchas when deploying (it might be fixed but last time I used it you need special admin rights to the temp drive on C drive on the deployed server). Anyway.. can you tell what line the error occurs on? — Nick.Mc, Aug 15 '17 at 01:55
Thanks for your reply. The error seems to be occurring on the int usernameIdx = agentStatisticsColumns["Username"]; line. I've been thinking of not using SSIS, but I'm coming from a failed attempt in establishing this web service through Power BI. The end goals is to create a visual in Power BI with the data exported to SQL Server. Hence why I'm going with SSIS, but if I can't solve this I might have to look for Plan C. — darksideguy, Aug 15 '17 at 02:01
Doing it in power bi is the least practical and maintainable way. SSIS is a better approach but the script task suffers from a lack of usability. A C# console app is the most robust approach but you have to be comfortable with using that in your system build. Anyway... your error is saying that it can't find "Username" in the `agentStatisticsColumns` array. Which is no suprise since I don't see it loaded anywhere in your code. Is this meant to represent the data coming from the web service? — Nick.Mc, Aug 15 '17 at 02:15
Yes, username is a field of the getstatistics method of the webservice. — darksideguy, Aug 15 '17 at 02:21
As it stands in that code, the variable `agentStatisticsColumns ` has nothing in it. There's no code loading anything into it. You need some code that uses `getStatisticsResponse` (which does have something in it) to populate `agentStatisticsColumns` somehow. It would help if you could inspect the contents of `getStatisticsResponse` at runtime. Not sure if you can do that in the SSIS script editor — Nick.Mc, Aug 15 '17 at 02:50

darksideguy · Answer 1 · 2017-08-15T04:26:07.153

You are absolutely right, and that's a dumb oversight from my part. I need to incorporate the field: public static WsSupervisorService supervisorService = null;

With that I'm able to wrap the getStatisticsResponse resp = supervisorService.getStatistics(statistics); with an actual VALUE.

Thanks Nick!

 public Dictionary<string, int> agentStatisticsColumns = new Dictionary<string, int>();
    public static WsSupervisorService supervisorService = null;
    public static void Main(string[] args)
    {
        supervisorService = new WsSupervisorService();
        setSessionParameters sessionParams = new setSessionParameters();

        sessionParams.viewSettings = new viewSettings
        {
            appType = "Custom",
            forceLogoutSession = false,
            idleTimeOut = 600,
            rollingPeriod = rollingPeriod.Minutes5,
            shiftStart = 28800000,
            statisticsRange = statisticsRange.CurrentDay,
            timeZone = -25200000
        };

        getStatistics statistics = new getStatistics();
        statistics.statisticTypeSpecified = true;
        statistics.statisticType = statisticType.AgentStatistics;
        statistics.columnNames = null;

        try
        {
            getStatisticsResponse resp = supervisorService.getStatistics(statistics);
        }
        catch (Exception)
        {

            throw;
        }
    }

Thanks for returning with a solution. It would be really handy if you could post your final code in here. Then I'm sure it's OK to accept your own answer — Nick.Mc, Aug 15 '17 at 04:19

SSIS Web Service Script Component (The given key was not present in the dictionary)

1 Answers1