SSIS - Upgrade from 2008 to 2012 causes issues with Recordset objects

• Bill N

  SSC Journeyman

  Points: 82

  More actions

  January 19, 2017 at 3:16 pm

  #317567

  Hello,

  I should start by stating that I'm relatively new to SSIS, so my practices might not be the best... if any of my implementation sounds questionable, you're welcome to raise concern.

  I have a package in my SSIS project that was recently converted from 2008 to 2012. The package runs successfully in 2008. The conversion was successful and none of the tools show any problems (no red Xs and no yellow !s). However, when I run a particular package, it fails on the load to a recordset destination with an error similar to "unable to access this recordset object, it is either locked or unavailable." I apologize for not having the exact error as I don't have access to the project at my current location.

  It should be noted that this is not the first time the recordset is loaded into during the package. The way the package is designed, it is a series of transforms encapsulated into data flows run in sequence. This is deliberate as the client may want to change the order of the transforms occurring and/or disable particular transforms at any given time. After initially loading the data into a recordset object (stored as a variable in the package scope), each data flow loads the data in from a script designated as a data source, performs the transform that pushes some of the data out to an external table and sends the rest back into the recordset object for the next transform to load.

  The error occurs on the destination load on the first transform. That is to say, it reads from the table and writes to the recordset object successfully. It then loads from that recordset in the first transform task, performs the transform, and fails on the load back into the recordset object. There are no metadata issues on the export.

  Again, this task works perfectly in 2008, but in 2012 it appears that the object is not properly being cleaned up. I'm not sure if there is modification required in the script, but I'm not sure what exactly would be expected. Should I attempt to close the recordset? Should I set the variable to nothing? Am I barking up the wrong tree? It would be nice if SSIS gave more descriptive errors, but I'm hoping maybe someone around here knows this issue and knows how to resolve it.

  Thanks!