locked
SSIS Task Fails Because Variable Cannot be Found (BUT Variable is THERE) RRS feed

  • Question

  • I am going to try to be as complete as possible as I try to explain what is going on and what I have done to troubleshoot this. To start of this task has been working in a deployed state on SQL Server 2008 R2 for the past 3 to 4 years. The issue did not occur until I upgraded the package to run on SQL Server 2016.

    My Configuration:

    Microsoft Visual Studio 2015 Shell (Integrated)
    Version 14.0.23107.0 D14REL
    Microsoft .NET Framework
    Version 4.6.01586

    Installed Version: IDE Standard

    Microsoft Visual Studio Tools for Applications 2015 00322-10000-00000-AA531
    Microsoft Visual Studio Tools for Applications 2015

    Visual Basic 2015 00322-10000-00000-AA531
    Microsoft Visual Basic 2015

    Visual C# 2015 00322-10000-00000-AA531
    Microsoft Visual C# 2015

    SQL Server Data Tools 14.0.61712.050
    Microsoft SQL Server Data Tools

    SQL Server Integration Services
    Microsoft SQL Server Integration Services Designer
    Version 14.0.1000.169

    Here is a snapshot of my task (I identified the areas I will talk about below)

    My task iterates an Excel workbook. Each of the 4 pipes (1 thru 4) represent a sheet in the workbook. The pipes write a status to an in memory recordset object variable: obEmailBody

    There is data on all of the worksheets and the variable is populated after each pipe.

    My task fails in step 6 (I'll get back to step 5 in a second) with the Error: The variable name "User::obEmailBody" could not be found in the list of variables. Before you ask about scoping, yes it is scoped correctly. Sometimes the task fails after pipe 1 completes, sometimes after pipe 2 completes, sometimes after Pipe 3, sometimes after pipe 4 , and sometimes it runs just fine. This is ALL on the SAME input file.

    I added a script task before step 6. In this script task I added the following code so I could iterate through the variable and write the output to a text file:

      Dim path As String = "E:\SSIS\data.txt"
            Try
                Dim da As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
                Dim dt As DataTable = New DataTable
    
                da.Fill(dt, Dts.Variables("obEmailBody").Value)
    
    
                Using sw As StreamWriter = File.AppendText(path)
    
                    For Each row As DataRow In dt.Rows
                        sw.WriteLine(Dts.Variables("stWorksheetName").Value.ToString)
                        sw.WriteLine(row(0).ToString)
                    Next
                End Using
    
    
            Catch ex As Exception
                Using sw1 As StreamWriter = File.AppendText(path)
                    sw1.WriteLine(ex.Message.ToString)
                End Using
    
            End Try

    When the task fails it writes the following: Exception from HRESULT: 0xC0010014

    I can't figure out why the task fails intermittently and why it can't find a variable that is there.

    


    • Edited by Whalensdad Monday, June 11, 2018 5:21 PM
    • Changed type ArthurZ Monday, June 11, 2018 7:03 PM
    Monday, June 11, 2018 5:19 PM

Answers

  • I was working on a change this morning. I ended up using a temp table in the database. Instead of populating the in memory recordset, I'm writing to the temp table. I'm then populating a object variable by querying that table. From there the functionality is the same. I iterate that variable, which is working without any issues. The bug appears to be in the way the in memory recordset is maintained in memory. I'll put in a support item and reference this conversation. 
    • Marked as answer by Whalensdad Tuesday, June 12, 2018 1:26 PM
    Tuesday, June 12, 2018 1:26 PM

All replies

  • Hi Whalensdad,

    1st off an expression drives what route (pipe in your words) the package takes and that must explain why it fails on 3 or 4. But you do not tell what error gets generated when it does.

    Just to re-iterate, if the package fails above task 5 and 6 then this is what needs to be fixed.


    Arthur

    MyBlog


    Twitter

    Monday, June 11, 2018 7:11 PM
  • I guess I wasn't clear, it's not failing in the pipes (I used that term to indicate it goes down that path based on the expression that evaluates to which worksheet it is iterating). #6, the foreach is where it fails. It fails after any one of the paths, even though I'm passing in the same file.
    Monday, June 11, 2018 7:17 PM
  • OK, where and now you defined the
    obEmailBody
    variable?

    Arthur

    MyBlog


    Twitter


    • Edited by ArthurZ Monday, June 11, 2018 8:41 PM
    Monday, June 11, 2018 8:41 PM
  • At the end of each LOAD task there is a Recordset Destination. This is where the variable is populated.

    Let me add that I have ANOTHER task that is having the same issue. These tasks work correctly running under SQL Server 2008 R2/BIDS. It is ONLY after I upgraded them to SQL Server 2016/SSDT that this is occurring.


    • Edited by Whalensdad Tuesday, June 12, 2018 10:46 AM
    Tuesday, June 12, 2018 10:43 AM
  • I see a more complete picture now. I started to suspect it is a product bug then.

    If you can, open a support item with Microsoft Pro Services.

    To quickly overcome this nuance, I think you need to change the design so it does not use Multicast.

    Arthur

    MyBlog


    Twitter

    Tuesday, June 12, 2018 1:14 PM
  • I was working on a change this morning. I ended up using a temp table in the database. Instead of populating the in memory recordset, I'm writing to the temp table. I'm then populating a object variable by querying that table. From there the functionality is the same. I iterate that variable, which is working without any issues. The bug appears to be in the way the in memory recordset is maintained in memory. I'll put in a support item and reference this conversation. 
    • Marked as answer by Whalensdad Tuesday, June 12, 2018 1:26 PM
    Tuesday, June 12, 2018 1:26 PM