locked
DQS SSIS package hangs in VS 2010 RRS feed

  • Question

  • Hi all,

           I have a package that is used for DQS cleansing.  I have nearly 650,000+ records to clean however after about 350,000 records processed I get a symptom that hangs my project up.  For example, I will close my visual studio project/solution but once I try to go back to open my project I get the message "visual studio is waiting for an internal operation" in the lower right hand corner.  Once this happens I can't click or scroll anywhere in my project.

           I am using SQL 2012 to move data from one table to another but into another database within the SQL 2012 server/instance.  In addition, I'm using the DQS client to clean and validate data for last name, state and country.  My visual studio is version 2010.  I'm running this all on a virtual machine that has 8g of ram and 4 cores.  I do have the cumulative service pack installed for SQL 2012.

           At this point, I have to kill my VS2010 in task manager but can't seem to work my SSIS project any more.

    Thanks for any help or ideas,

    Michael

    Wednesday, June 27, 2012 3:13 PM

Answers

  • A hang when opening an SSIS package in BIDS means that validation is busy running usually.

    If you look in the solution explorer, and add the package into the solution, highlight but do not Open the package. Then you can set the Work Offline option before opening the SSIS package.

    In the menu toolbar (up top) choose SSIS > Work Offline. That eliminates the validation step where the tasks all connect to SQL Server and run their validation step.

    Then you can disable some of the tasks in the SSIS package, and one by one trial and error, enable one and uncheck Work Offline, then check it back and enable another one. The goal is to figure out which task/component is hanging the validation in Visual Studio.

    Hope that helps - its not easy, but trial and error can narrow down which piece is the problem.

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    • Marked as answer by M_deVera Friday, June 29, 2012 8:18 PM
    Thursday, June 28, 2012 12:12 AM

All replies

  • I take it this happens when you use SSDT (Visual Studio) to debug the SSIS Package (running it interactively?)

    Do you see anything progressing in the current activity of DQS? You can open the Data QUality Client, look at the Activity Monitoring button, look for your SSIS project (it will be something like PackageName.DQS Cleaning.<Time><Guid><Guid>

    When you highlight that Activity in the upper grid, the lower grid will show the Activity Step details for that one, and show if it is in the middle of running the DQS Cleansing.

    Is the DtsDebugHost.exe still active in the list of processes in Task Manager when the hang happens? It is taking RAM and is CPU still consumed?

    Also watch for spooling to disk when using SSIS. If SSIS runs out of memory (Watch Available Memory numbers in task Manager for example) SSIS will start spooling the dataflow buffers into the TEMP folder for your windows account. Usually that's C:\Users\jasonh\AppData\Local\Temp\DTS{<guid>}.tmp  as per http://support.microsoft.com/kb/972365

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    Wednesday, June 27, 2012 7:34 PM
  • Jason,

    Thank you for the tips on how to troubleshoot!  I really appreciate it.  After I get up to about 350,000 records in my destination table, I gracefully close out of my solution and try to re-open my solution/project but this is where I hang.  I can't even click on any of the controls to start a debug.  In the lower right hand corner I get a message stating "Visual Study is Busy".  I have waited for more than hour and I still cant get my solution/project to un-lock.

    My graceful closing definition....I have been running my task in my cleansing DQS project for every top 30000 records in my select statement.  Once I see a completion of my task and after my table is filled with 350,000 + records I then close my project and try to re-open my solution/project. 

    Thanks Michael.  If you have any more tips please let me know.  I appreciate your support.

    Wednesday, June 27, 2012 8:05 PM
  • A hang when opening an SSIS package in BIDS means that validation is busy running usually.

    If you look in the solution explorer, and add the package into the solution, highlight but do not Open the package. Then you can set the Work Offline option before opening the SSIS package.

    In the menu toolbar (up top) choose SSIS > Work Offline. That eliminates the validation step where the tasks all connect to SQL Server and run their validation step.

    Then you can disable some of the tasks in the SSIS package, and one by one trial and error, enable one and uncheck Work Offline, then check it back and enable another one. The goal is to figure out which task/component is hanging the validation in Visual Studio.

    Hope that helps - its not easy, but trial and error can narrow down which piece is the problem.

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    • Marked as answer by M_deVera Friday, June 29, 2012 8:18 PM
    Thursday, June 28, 2012 12:12 AM
  • Thank you so much Jason!  I will peck away at each task to figure out which task needs to work offline!
    Thursday, June 28, 2012 1:07 PM
  • Jason,

    Is there a way to start your solution in work-offline mode?  At times I will start a solution/project that will hang and when I'm caught up in this situation I don't have an opportunity to select work-offline under the SSIS menu.

    Thanks,

    Michael

    Monday, July 2, 2012 9:54 PM
  • When you set it SSIS > Work Offline it sticks I think.

    But I know what you mean - if you open any package and forget this, you can't really fix it and cancel the validation. The shell will get stuck for 30 seconds minimum if it can't connect to your SQL Server readily.

    Thx, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    Tuesday, July 3, 2012 3:59 AM