locked
Using Cursors in Data Flow Task & OLE DB Command Task RRS feed

  • Question

  • <I couldnt locate any thread here that dealt with this issue; thus am starting a new one. Apologies in advance if something exists>

    Hi,

    I have a View which is populated on the same SQL Server as my Destination table. I need all the values in the view to update into my destination table based on the Proj_NBR column that joins them both.

    I am a bit naive on how to use the FOR LOOP or the FOR EACH container usage; that would help me a great deal. I am stuck on how to set my counter for the Initiate and the validation and the expression for the loop.

    The workaround that I worked on this was a cursor [Stop glaring; it is a relatively small amount of jus over 25 rows and like i already said; jus a workaround till I can get the solution]. But when i run the Execute SQL task for my Cursor; it says that the Update statement is incorrect as such and conks. The OLE DB Command task is the only one that actually executes; but it returns the following error... for each row in the cursor which is populated from the view [Which is why am guessing that am the right path; please correct me if am delusional in my thinking]

    Error: 0xC0202009 at Actual Update, Actual Update [34]: An OLE DB error has occurred. Error code: 0x80040E14.
    An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80040E14  Description: "A cursor with the name 'curActual' does not exist.".

    Am I correct in trying to execute the Cursor commandline T-SQL to update the destination table with each Proj_NBR found in my view? Please HELP!!!

    * No Custom coding ALLOWED * We are showing off the SSIS out-of-box functionality and would not like to code.

    Thank you,
    Regards,
    sAm
    Wednesday, July 22, 2009 5:42 AM

Answers

  • OK - the main mental hurdle you'll need to overcome is that the Control Flow tab is all about orchestrating what happens when - it may have something to do with manipulating data, but not at the row level.  You can issue set-based operations with the Execute SQL Task in the Control Flow, but if you find you need to do row by row operations, the Data Flow more suitable for that.  The Data Flow tab is accessible whenever there are any Data Flow tasks in your Control Flow.  Each Data Flow can be thought of as an in-memory dataset (or sets).  The purpose of a Data Flow is to read one (or more) source "tables", manipulate the set in some manner (in memory), then dump the result to one (or more) destination "tables".  (I put tables in parentheses because SSIS isn't limited to using RDBMSs as sources or destinations.)  The Control Flow "parts" are connected to each other by "precedence constraint" arrows which dictate the order in which "parts" have to executed in.  The Data Flow "parts" are connected to each other by "data flow" arrows which are a visual way of saying "the data from this part gets fed into to this part".

    In your case, it appears as though using one Data Flow Task in your Control Flow should do.  No looping constructs required (yet... there may be some other constraints which dictate that, but I don't see any).  Inside the Data Flow Task, you'll want to add a "source" - likely an OLE DB Source to query your view.  Out of that source will come the entire rowset of your view - unless you specify a SQL Statement in that Source to restrict rows or columns.  (Standard T-SQL applies - even parameterized.) 

    I'm assuming you may have new rows to insert into your destination.  If not, skip this paragraph and the next.  You would then route that data into a Lookup component, which you would configure to do a lookup on your "destination" table - by whatever your key fields are.  The lookup has a "match" output where it will send all rows that it was able to find a match to.  In 2008, you can send "misses" to a "no match" output if you like, or you can let them go out the "error" output.  2005 just has the "error" output for misses.  At this point, you've got two "flows" going - one with rows you'll have to add to your destination table, and one with rows you'll have to update in your destination table.

    Take your "no match" or "error" output arrow and put it into an OLE DB Destination component.  That will add rows into your destination.

    Take your "match" output and put it into an OLE DB Command component.  Type in an UPDATE statement with "?" placeholders, and map the columns to the (stupid) "param0", "param1" columns.  Be careful doing that - the only clue you get as to what parameter is which is which is the data types (by looking at the tooltip).

    You're done...

    Let me know how that goes - I'm sure you'll have some wrenches to throw into that...


    Todd McDermid's Blog
    • Marked as answer by Sam Aaron Wednesday, July 22, 2009 9:23 PM
    Wednesday, July 22, 2009 4:58 PM

All replies

  • First - you may be going about this the wrong way, attempting to use the Foreach Loop construct.  Can you explain what the Foreach is doing?  It appears as though you're shoe-horning SSIS to operate in a paradigm it's not used to.  Forgive me for saying so - but it tends to be those with T-SQL backgrounds who attempt to do so!  SSIS has a "frame of mind" learning curve in it...

    Forgive me - I'm a little confused as to what you're doing that works and what doesn't.  I'm not sure if your error is from the Execute SQL Task or the OLE DB Command.

    One thing that may be causing your problem is the managed connection pool.  When you create a Connection Manager to refer to your SQL Server, you're actually creating a managed connection - which means that each task you use the "connection" in may get a different actual connection to the database.  For example, you can't count on being able to "see" a temporary table in Task "2" that you created in Task "1".  Unless you mark the Connection Manager with the property "Retain Same Connection".  That may help you here - but I'm not entirely sure where your "curActual" cursor is defined or used...
    Todd McDermid's Blog
    Wednesday, July 22, 2009 3:24 PM
  • Hi Todd,

    Am glad that you brought me into the perspective straighton; Its true that I do come from a more T-SQL mindset. So, lemme present you with my problem without involving too much into Cursors or why I would go about them.

    I have a View which is brought together after Business Logic on our source to obtain the Totals [Actuals & Estimated]; I need to update my destination SQL table with these Totals based on their corresponding Proj_NBR.

    So, basically; I need to do an UPDATE on my destination with Values from the View to my destination SQL table by Proj_NBR.

    Like I mentioned earlier; we are attempting everything completely from SSIS Out-of-box; custom components are out. I was pushed into a corner for delivering on this and hence the move to use Cursors.

    Hope this helps you in helping me :)
    Thanks,
    sAm

    The error message in my previous posting was from the OLE DB Command task.
    Wednesday, July 22, 2009 4:21 PM
  • OK - the main mental hurdle you'll need to overcome is that the Control Flow tab is all about orchestrating what happens when - it may have something to do with manipulating data, but not at the row level.  You can issue set-based operations with the Execute SQL Task in the Control Flow, but if you find you need to do row by row operations, the Data Flow more suitable for that.  The Data Flow tab is accessible whenever there are any Data Flow tasks in your Control Flow.  Each Data Flow can be thought of as an in-memory dataset (or sets).  The purpose of a Data Flow is to read one (or more) source "tables", manipulate the set in some manner (in memory), then dump the result to one (or more) destination "tables".  (I put tables in parentheses because SSIS isn't limited to using RDBMSs as sources or destinations.)  The Control Flow "parts" are connected to each other by "precedence constraint" arrows which dictate the order in which "parts" have to executed in.  The Data Flow "parts" are connected to each other by "data flow" arrows which are a visual way of saying "the data from this part gets fed into to this part".

    In your case, it appears as though using one Data Flow Task in your Control Flow should do.  No looping constructs required (yet... there may be some other constraints which dictate that, but I don't see any).  Inside the Data Flow Task, you'll want to add a "source" - likely an OLE DB Source to query your view.  Out of that source will come the entire rowset of your view - unless you specify a SQL Statement in that Source to restrict rows or columns.  (Standard T-SQL applies - even parameterized.) 

    I'm assuming you may have new rows to insert into your destination.  If not, skip this paragraph and the next.  You would then route that data into a Lookup component, which you would configure to do a lookup on your "destination" table - by whatever your key fields are.  The lookup has a "match" output where it will send all rows that it was able to find a match to.  In 2008, you can send "misses" to a "no match" output if you like, or you can let them go out the "error" output.  2005 just has the "error" output for misses.  At this point, you've got two "flows" going - one with rows you'll have to add to your destination table, and one with rows you'll have to update in your destination table.

    Take your "no match" or "error" output arrow and put it into an OLE DB Destination component.  That will add rows into your destination.

    Take your "match" output and put it into an OLE DB Command component.  Type in an UPDATE statement with "?" placeholders, and map the columns to the (stupid) "param0", "param1" columns.  Be careful doing that - the only clue you get as to what parameter is which is which is the data types (by looking at the tooltip).

    You're done...

    Let me know how that goes - I'm sure you'll have some wrenches to throw into that...


    Todd McDermid's Blog
    • Marked as answer by Sam Aaron Wednesday, July 22, 2009 9:23 PM
    Wednesday, July 22, 2009 4:58 PM
  • Hi Todd :)

    I couldn't throw any wrenches into that... no matter how much I tried to!!!! :)

    To say that that was brilliant would be an understatement... but to say that it was absolutely PERFECT would be to barely scratch the surface!! :)

    So; am not gonna say anything more than a Whole Hearted THANKS!!!

    Its not jus to the solution you provided but to the insights you provided so aptly suited to my situation leaving me with no ambiguity on the steps to solution.

    I did the following:

    * In a Data Flow Task; I loaded the view using a OLE DB Source by specifying only the columns I needed for my task.
    * In a Lookup Task; I gave my destination as LookUp and looke up on the Project_NBR
    * Picking up the matches for updates into an OLE DB Command Task.
    * I gave in the following code for the UPDATE:

    UPDATE <Destination_TBL>
    SET AMT = ? WHERE PROJECT_NBR = ?
    Param_0 was set to Amt , Param_1 was set to NBR [Believe me this is a tricky part; I had to follow the BOL steps which is pretty elaborate]

    Thats it!!! :) Worked like a charm. Ran a few tests on this for our requirements and it worked through them all!!! :)

    Once again, I thank you for your patience to walk me through the process in such a clear & concise explanation.

    God Bless You Todd!!!

    Regards,
    sAm
    Wednesday, July 22, 2009 9:23 PM