Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSIS - Bug?!

คำตอบ SSIS - Bug?!

  • Friday, March 02, 2012 10:11 AM
     
     
    Sign In to Vote
    0

    Hello guys, hope you can help me with this.

    I have a package, where among other things, I have 3 "SQL Tasks" to do updates to a unique table.

    UPDATE TABLE_NAME
    SET ImportType = NULL, [Status] = 0
    WHERE ImportType = 'OPEN' and [Status] = 1

    UPDATE TABLE_NAME
    SET ImportType = NULL, [Status] = 0
    WHERE ImportType = 'PEND' and [Status] = 1

    UPDATE TABLE_NAME
    SET ImportType = NULL, [Status] = 0
    WHERE ImportType = 'CLOS' and [Status] = 1

    All the 3 do their job when running in debug mode (VS 2005) and also in SQLServer 2005. When running the package inside a scheduled job the last update don't work. I don't get any error...

    Any idea?

    Thanks!

     

All Replies

  • Friday, March 02, 2012 10:21 AM
     
     
    Sign In to Vote
    0

    I think  you need to make use of profiler with statement level events selected and see what statements are being executed. Also you can make use of data profiler feature in SSIS to see the rows that are being passed for this query. I somehow feel that for some reason, no data is meeting the WHERE condition and hence the update is not happening.

    Let me know more information if you need further help.

    Regards,

    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

     
  • Friday, March 02, 2012 11:15 AM
     
     
    Sign In to Vote
    0
    Hello and thanks for your feedback. I´m new with SSIS and not aware of the VS features that can help me with my analysis. I have already change the data manually in the table and it works in debug mode and in sql, but not in scheduled job, for any reason ...
     
  • Friday, March 02, 2012 3:02 PM
     
     
    Sign In to Vote
    0
    What connection type do you use for the Execute SQL Task ? What type of authentication or credentials do you use in your connection?

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

     
  • Friday, March 02, 2012 3:31 PM
     
     
    Sign In to Vote
    0
    I have an OLE DB connection for all, with Windows Authentication. Just to remember that only the last update doesn't work ...
     
  • Friday, March 02, 2012 3:32 PM
     
     
    Sign In to Vote
    0
    I have an OLE DB connection for all, with Windows Authentication. Just to remember that only the last update doesn't work ...
    That is your problem. When executing the package under SQL Job Agent, it uses completely different user account for the execution. I would recommend you change the used authentication to a SQL Server user.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

     
  • Friday, March 02, 2012 5:42 PM
     
     
    Sign In to Vote
    0
    COZYROC, I don't think that is the problem ... As I said before, the other updates works fine when running from a Scheduled JOB, only the last one don't ... 
     
  • Friday, March 02, 2012 5:45 PM
     
     
    Sign In to Vote
    0
    COZYROC, I don't think that is the problem ... As I said before, the other updates works fine when running from a Scheduled JOB, only the last one don't ... 
    What about the permissions assigned of each user account - the one used under SQL Job Agent and the other you use in BIDS? If you don' have the same set of permissions, this would explain why it doesn't work.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

     
  • Friday, March 02, 2012 5:48 PM
     
     
    Sign In to Vote
    0

    I am going to ask the glaringly obvious:   Table_Name represents the same table in all updates.

    Also, are these three updates run in one Execute SQL task or more than one.

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

     
  • Friday, March 02, 2012 5:51 PM
     
     
    Sign In to Vote
    0
    Btw I now realize there is no semicolon at the end of each UPDATE statement. If it is all executed in one Execute SQL Task, I think this is required.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

     
  • Friday, March 02, 2012 6:07 PM
     
     
    Sign In to Vote
    0
    The table is always the same and the updates are in different SQL Tasks.
     
  • Friday, March 02, 2012 6:48 PM
     
     
    Sign In to Vote
    0

    Do all of the tasks execute?  is there something that might prevent that execute sql task from firing.

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

     
  • Friday, March 02, 2012 6:53 PM
     
     
    Sign In to Vote
    0
    My package have 3 conditions that ends, each one, with one of those Updates.
     
  • Friday, March 02, 2012 8:25 PM
    Moderator
     
     
    Sign In to Vote
    0

    Here is what I say:

    1. Add a very good logging and you will see errors/warnings
    2. Also, I always place updated/etc to different tables into its own DFT.

    So I suggest you follow my advice, redesign the package, then see.

    Arthur My Blog

     
  • Saturday, March 03, 2012 7:57 AM
     
     Answered
    Sign In to Vote
    0

    I'm going to go for the glaringly obvious here and say that at the time the last SQL task is executing, there is nothing in the table with  ImportType = 'CLOS' and [Status] = 1

    Permissions shouldn't be an issue as other tasks execute. Syntax is fine and it is in a separate sql task so that only realy leaves 2 things: 

    1: nothing matches the criteria at the time the task is executed

    2: the connection or resultset is set incorrectly

    as it seems to work in debug mode or as queries in SQL server I think that the issue is (1) timing of exactly when the task is executed compared to other tasks happening in the package - at the time of execution of the task nothing matches the criteria

    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

     
  • Thursday, June 14, 2012 7:18 AM
     
     
    Sign In to Vote
    0

    Only SQL Server Profiler can save you....

    Check what statement is issued to server when third step is being executed. You can easily check that query / statement in SSMS (SQL Server Management Studio).