Reset Pointer on Table Variable?
In my SSIS package I have a table variable that I populate in a dataflow. Then in a subsequent Dataflow, I have two parallel script components. Both use identical code to populate a DataTable from the table variable. However, it only works in one of them. The other one ends up with a DataTable with zero rows. Is this because after populating my DataTable, I need to reset some pointer on the Table Variable so that it can be used again? I guess I can fix this by populating two identical table variables, but I'd rather not if I don't have to, obviously. Anyone care to take a shot in the dark at this? here is the pertinent code that populates the DataTable: Code Snippet Public Class ScriptMain Inherits UserComponent Dim AdvertiserRepHistory As New DataTable Dim intRC As Int32 Public Overrides Sub PreExecute() MyBase.PreExecute() Dim vars As IDTSVariables90 VariableDispenser.LockOneForRead("AdvertiserRepHistory", vars) Dim rs As Object = vars(0).Value vars.Unlock() Dim adapter As OleDbDataAdapter = New OleDbDataAdapter() adapter.Fill(AdvertiserRepHistory, rs) intRC = AdvertiserRepHistory.Rows.Count End Sub
August 22nd, 2008 12:57am

Say, it is possible (perhaps even likely) that since the scripts are parallel, the locking of the variable by one prevents the other from being able to read it, and yet does this without triggering an error? Is there a way to read the variable without locking it? Or is there a way to check if it is locked and wait until it is no longer locked? Shot in the dark...
Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2008 6:27pm

I'm having a similar problem. Duplicating the object into another variable does not work for me either. Have you found a solution for this?
July 19th, 2011 10:33am

Aside from the fact that both of you are doing it very, very wrong... You can't "duplicate" the object - because all you end up with is two references to the same object. ADO recordsets have one enumerator, which gets "shared" between all entities wanting to iterate over it. That sucks, I know. In order to make your code work, you're going to have to LOAD two separate recordsets into two separate variables. But don't do that. Instead, you should be loading that data into a RAW file, a SQL table, or something else. Stuffing rowsets in Object variables is not a good practice in SSIS. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
July 19th, 2011 1:20pm

I am agree with Todd, why you don't use RAW files? what you want to do exactly? passing data from first data flow task to second one? if yes, RAW Files is good option. or you may want to do something else?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com
July 20th, 2011 2:13am

Well all I remember from 3 years ago is that I did end up solving the problem by using 2 different object variables. I don't remember if I had a good reason for using the object variables instead of a staging table anymore; and I'm not at the same shop anymore so I can't look to refresh my memory. ...probably not though. : )-Tab Alleman
Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2011 1:53pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics