none
Package runs as expected in SSDT Design Debugger, but not after deployment to SSIS Catalog

    Question

  • Hello

    I am using Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) for this project.

    My SSIS package runs as expected and completes successfully when I execute it in the SSDT Design Debugger. I deploy the SSIS project to the SSIS Catalog using the IS Deployment Wizard. I execute the package in SSMS, and it completes successfully, but it does not run as expected.

    Here's what the package does (in this order):

    • Execute SQL Task - Direct input select statement returning full result set mapped to object variable objDRN
    • Foreach Loop Container - Foreach ADO Enumerator using object variable objDRN as collection source and mapping one column (Index 0) to string variable strDRN (Note: Column DRN is datatype NVARCHAR(10) in the database table)
    • Script Task - (Inside loop container) VB 2010 script that l if a file exists with the same name as the value of strDRN and returns True or False to boolean variable bolFlagExists
    • Execute SQL Task - (Inside loop container; Precedence Constraint = Success AND bolFlagExists == True) Direct input update statement using string variable strDRN as an Input parameter (Data Type = NVARCHAR) to update record in table

    To reiterate my issue, this package finds the flag file, updates the table, and completes successfully when I execute it in the SSDT Design Debugger. The deployed package executed in SSMS finds the flag file, bypasses the update, and completes successfully.

    I am baffled. Let me know if I need to supply more details in order to resolve this issue. Thank you!

    Thursday, August 14, 2014 2:42 AM

Answers

  • Hi kaljon,

    How did you execute the SQL Server Integration Service(SSIS) package remotely? Which account was being used to run the package?
    In short, the issue might be caused be the execution account does not have permision to update the destination table.

    If the package was running remotely by using SQL Server job, and the package was running under SQL Server Agent service account, please ensure the SQL Server Agent Service account has permission to connect to the destination server and update the destination table.
    If the package was running remotely by using a web service, please ensure the application identity for the web service has the appropriate permisstion too.

    It is also suggested to break down the issue by following these steps:
    1. Design and deploy a package with only the first Execute SQL Task, start SQL Server Profile to trace the connection and command that is sent to the server.
    2. Design and deploy a package with the first Execu SQL Task, Foreach Loop Container and store each value in a text file. Check the result in the text file.
    3. Design and deploy a package with the first Execu SQL Task, Foreach Loop Container, Script task and store each value in a text file. Check the result in the text file.
    4. Finally, with the original package, and start SQL Server Profile to trace the last Execute SQL Task.

    Thanks,
    Jinchun Chen

    Monday, August 25, 2014 6:04 AM
    Moderator

All replies

  • Try putting a breakpoint in the OnPreExecute event of Execute SQL Task and check the value of bolFlagExists variable. Make sure its boolean True and not string "True".


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, August 14, 2014 3:05 AM
  • Thank you for the suggestion.

    I placed a breakpoint at the OnPostExecute event of the Script Task and at the OnPreExecute event of the Execute SQL Task. Both breakpoints showed bolFlagExists withType = Boolean and Value = True.

    I also placed a breakpoint at the OnPostExecute event of the Execute SQL Task. This breakpoint showed ExecuteStatus = Completed and ExecuteResult = Success. This would indicate that the Update statement (UPDATE dbo.DataRequests SET Ready = 'Y' WHERE DRN = ?)  executed successfully, yet the value of column Ready for the record where DRN = strDRN remains 'N'; as if the SQL statement did not run.

    Any more suggestions?

    Thursday, August 14, 2014 4:15 PM
  • UPDATE

    I have an interesting wrench to throw into this issue.

    First, let me give a little info on the server configuration (the names are changed to protect the innocent):

    • ServerA - This is my development box. This is where I create and debug SSIS projects. I deploy projects from this box to the TEST box.
    • ServerB - This is our TEST box. SSIS projects are deployed to this box for further testing.

    Testing a package deployed to ServerB: From my development box (ServerA), I connect to the SQL Server instance on ServerB using SSMS. I navigate the Integration Services Catalog and execute the package. Everything I've described in this thread uses scenario.

    Here's the wrench:

    I logged into the ServerB console directly, opened SSMS, and executed the package. The package completed successfully AND ran as expected (The update statement executed successfully).

    Why does this happen? And how do I resolve this? I need to be able to execute the package remotely.

    Thursday, August 14, 2014 5:32 PM
  • Apparently, the TechNet brain trust cannot handle this question. Time to pose this question to other boards.
    Thursday, August 21, 2014 4:16 PM
  • Hi kaljon,

    How did you execute the SQL Server Integration Service(SSIS) package remotely? Which account was being used to run the package?
    In short, the issue might be caused be the execution account does not have permision to update the destination table.

    If the package was running remotely by using SQL Server job, and the package was running under SQL Server Agent service account, please ensure the SQL Server Agent Service account has permission to connect to the destination server and update the destination table.
    If the package was running remotely by using a web service, please ensure the application identity for the web service has the appropriate permisstion too.

    It is also suggested to break down the issue by following these steps:
    1. Design and deploy a package with only the first Execute SQL Task, start SQL Server Profile to trace the connection and command that is sent to the server.
    2. Design and deploy a package with the first Execu SQL Task, Foreach Loop Container and store each value in a text file. Check the result in the text file.
    3. Design and deploy a package with the first Execu SQL Task, Foreach Loop Container, Script task and store each value in a text file. Check the result in the text file.
    4. Finally, with the original package, and start SQL Server Profile to trace the last Execute SQL Task.

    Thanks,
    Jinchun Chen

    Monday, August 25, 2014 6:04 AM
    Moderator