로그인
 
홈라이브러리학습다운로드지원커뮤니티포럼
Resources for IT Professionals >
> 
> 
Modify resultset within a script task

답변됨 Modify resultset within a script task

  • 2012년 5월 4일 금요일 오후 12:23
     
      코드 있음
    로그인하여 투표
    0

    Hi all,

    I am not able to find how to modify the content of a resulset within a script task.

    This is what I tried, but the script is contained in a foreach loop and it crashes at the 2nd iteration :

    public void Main()
    {
    OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dt = new DataTable("dt");
            
DataView dv = null;
int myId = Convert.ToInt32(Dts.Variables["User::varMyId"].Value); 
string reference = Convert.ToString(Dts.Variables["User::varReference"].Value);
da.Fill(dt, Dts.Variables["User::varMyResultset"].Value);
dv = new DataView();
dv.Table = dt;
dv.RowFilter = "Reference= '" + reference + "'";
dr = dv[0].Row;
dr["MyId"] = myId;

Dts.Variables["User::varMyResultset"].Value = dt; // I guess this cause the crash at the 2nd iteration

// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;
    }

    I would like to have the resultset up to date with the myId's that I want to update at each iteration.

    Is it possible ?

    Thank you for your help.

    Best regards.


    Matteo, .NET Developer and System Engineer

     

모든 응답

  • 2012년 5월 4일 금요일 오후 2:07
     
     
    로그인하여 투표
    0

    Hello

    The result set comes from a execute sql task ?

    Should you not consider a DataFlow task with ADO or OLEdb data source,followed by the transformation, and send the output to a Recordset destination. The recordset destination is a variable that can be used by the next task in your control flow.

    Jan D'Hondt - Database and .NET development

     
  • 2012년 5월 4일 금요일 오후 2:09
     
     
    로그인하여 투표
    0

    This is exactely what I did. After what, I want to modify the content of the recordset within a script task and then use the modified recordset as input in a foreach loop.

    Regards.

    Matteo, .NET Developer and System Engineer

     
  • 2012년 5월 4일 금요일 오후 3:28
    중재자
     
     제안된 답변
    로그인하여 투표
    0

    Don't use a Script Task. 

    I can only think you're trying to nail a square peg into a round hole.  If you ever hear the words "modify a recordset", you should automatically think "Data Flow Task".

    Can you describe your package for us?

    Todd McDermid's Blog Talk to me now on

     
  • 2012년 5월 8일 화요일 오후 2:29
     
     
    로그인하여 투표
    0

    Hi Todd,

    Yes I understand that much of the work should be done within the Data flow, but sometimes, the business logic is too much complicated so that the need of using a Script Task is required. That is my case :

    My package is a package that transfers data from a database to another. The source database is a plain database having "Users" or "People" if you prefeir. The destination database must receive those users but structured in a hierarchical way, such as a family : for example :

    Sarah Smith belongs to the "Smith" family, and, because she's 20 years old, she may be the daughter of :

    • Bill Smith, 45 years old
    • John Smith, 48 years old
    • Don Smith, 49 years old  --> Best choice. final choice

    I know that it's kind of stupid, but I can't explain everything here. This is just an example, but my business logic works that way. The business rules that are coded cannot be done otherwise than within a scrip task.

    So... In my script task, I work on a ResultSet and I have to set the "ParentId" column value, if my algorithme finds the right person. And that's exactely what I am not able to. Here is the basic design of it :

    • Get a resultset with the users from the source database
    • Loop on each record in a script task
    •       Find the parent of the current user
    •       Set the "ParentId" of the resultset current row if found the parent
    • In a foreach loop
    •       Generate the update query. The "ParentId" should be knowns because it's been set in the recordset
    •       Update the database

    This is the best way I can explain my problem, but actually, it's much more complicated than that. The only thing I am sure is that I must change the content of the resultset before it is used in a Foreach loop as iterated variable.

    Regards.

    Matteo, .NET Developer and System Engineer

     
  • 2012년 5월 8일 화요일 오후 4:35
    중재자
     
     답변됨
    로그인하여 투표
    0

    You'll be much better off if you use a Script component inside a Data Flow.  Even if you can't use built-in components (and I think you should be able to) the Script component is a much better fit for your process.

    Here's a brief description of how I'd see you working with it. 

    Instead of pushing your resultset into an object, do the query in a Data Flow and route it into a Script.  The Script would be async - you'd read the rows in, and have to redefine the columns you're outputting in the script editor (not the code editor).  In the code editor, you'd fill in the ProcessInputRow override with code that "caches" the row data - stuff them in whatever memory structure you want: a DataTable, Hashtable, etc - whatever works best for your resolving.  Now override ProcessInput, leaving the "base" call alone (so it'll call your ProcessInputRow).  After the base call, check the buffer.EndOfRowset property - if it's true, you've cached everything, and can begin generating output.  Manipulate your in-memory cache however you want, then start calling OutputBuffer.AddRow and assign values to the OutputBuffer properties for the column values...

    (As a performance improvement, you can start generating output if you know (possibly by sort order) that you have enough info for a set of the rows you currently have to accurately generate the parent info.  That will reduce memory usage.)

    A side benefit of this - it will be A LOT FASTER than your current architecture.  Why?  Because there's a lot less setup and teardown involved here.  The Foreach Loop has overhead on each iteration that adds up quick.

    Todd McDermid's Blog Talk to me now on

    • 답변으로 표시됨 joujoukinder 2012년 5월 9일 수요일 오전 8:47
    •  
     
  • 2012년 5월 9일 수요일 오전 8:47
     
     답변됨
    로그인하여 투표
    0

    Hi Todd,

    Thank you for your response.

    I tried the solution you explained and it works well, thank you. I also have found another way :

    • Get the users from the source database and put them into a resultset
    • In a control flows script task, fill a datatable from the resultset and set a variable wich is my datatable
    • Loop on each record in a script task
    •       Find the parent of the current user (alghorithm) working with the datable set in the variable
    •       Set the "ParentId" of the resultset current row if found the parent in the corresponding users within the datatable
    •       Finally, use the datable to generate the UPDATE queries in another loop

    That works fine too!

    Regards.

    Matteo, .NET Developer and System Engineer

    • 답변으로 표시됨 joujoukinder 2012년 5월 9일 수요일 오전 8:47
    •  
     
  • 2012년 5월 9일 수요일 오후 4:36
    중재자
     
     
    로그인하여 투표
    0

    That may work fine - and that's good.  But you just nailed a square peg into a round hole :)

    What you've done is almost equivalent to being happy that you found a way to make a cursor do set-based operations.  If you find the time in the future, I'd encourage you to try the Data Flow for this scenario - if only to get used to the idea...

    Todd McDermid's Blog Talk to me now on

     
  • 2012년 5월 10일 목요일 오전 9:52
     
     
    로그인하여 투표
    0

    Todd

    I agree with you. But to a man with a hammer every problem looks like a nail.

    Jan D'Hondt - Database and .NET development