April 18, 2012 at 11:39 pm
Hello,
i have a package which runs for about 6 hours. I looked at it every once in a while, it was going well. In the morning i saw that every task was green and it said "Execution completed", but when i clicked on it to stop the process, i got this message - "cannot detach from one or more processes. Would you like to terminate them instead?" So i chose yes.
The question is is it safe to continue with my work? Did the termination affect my DB somehow?
Again, all the tasks were already completed.
Will appreciate a quick response as i don't know whether to proceed or execute the package again..
thank you!
April 19, 2012 at 12:23 am
This is probably more a problem with the SSIS designer than with your package.
It is not really recommended to run a package with production purposes in the SSIS designer. Run it with DTEXEC instead. Turn on logging if you haven't already and see if the problem persists with DTEXEC.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2012 at 12:32 am
I have no idea what DTEXEC is, and what do you mean by "run a package with production purposes"? Sorry i'm just a newbie at this.
I didn't build this package, it exists for a long time and has been running fine, it's the first time it happens.
Can it be that i got this message because the server which this package was running on disconnected? Cause that could've happened
My main concern is whether the package data transfered ok to SQL
April 19, 2012 at 12:38 am
Mikalina (4/19/2012)
I have no idea what DTEXEC is, and what do you mean by "run a package with production purposes"? Sorry i'm just a newbie at this.I didn't build this package, it exists for a long time and has been running fine, it's the first time it happens.
Can it be that i got this message because the server which this package was running on disconnected? Cause that could've happened
My main concern is whether the package data transfered ok to SQL
DTEXEC is the command line tool used to run SSIS packages. It is used behind the scenes when you run a package on the SSIS service or when you schedule it within a SQL Server Agent job.
A package with production purposes is a package that has been developed, tested and released to production. In other words, when it starts doing with it is supposed to do. The BIDS designer in Visual Studio is intended to create and debug your packages, not run them at a daily basis. It can lead to performance issues (as it has to color all the boxes, update the counters in the dataflow et cetera). And you can run into debugging issues, as you have noticed.
If you want to see if the data is transferred OK to SQL Server, why don't you run a SELECT statement on the server to find out?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2012 at 1:05 am
Are you saying it's not recommended to run ssis by clicking on "Execute package"?
I sure did run some selects, the main table seems fine and so is row count, but there are a lot of tables in between, i can't check them all.
I just want to make sure this scary "terminate" didn't affect my data in any way, do you know the answer to that? Given the package already completed before i got the message
April 19, 2012 at 1:17 am
Mikalina (4/19/2012)
Are you saying it's not recommended to run ssis by clicking on "Execute package"?I sure did run some selects, the main table seems fine and so is row count, but there are a lot of tables in between, i can't check them all.
I just want to make sure this scary "terminate" didn't affect my data in any way, do you know the answer to that? Given the package already completed before i got the message
For debugging purposes, yes. For actually running it every day? No. Performance of the dataflow will be degraded in the BIDS designer.
If the designer says the execution is completed and every task/component ran successfully, you can normally assume the data was transfered OK.
As I said, the error is probably more a debugging issue of Visual Studio itself.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2012 at 1:29 am
Ok, thank you for your help! I learned something new today
But how can one see the package process when running it with DTEXEC? I mean which task ended, which is running now..
April 19, 2012 at 1:36 am
Mikalina (4/19/2012)
Ok, thank you for your help! I learned something new todayBut how can one see the package process when running it with DTEXEC? I mean which task ended, which is running now..
You don't 😀
SSIS is a tool to create ETL processes. Usually these run in batch process during the night, when it is OK to put a heavy load on the server and on the source system.
SSIS is designed to run without any user interaction.
So basically you have to design your package so that you can trust it to run without supervision. This means efficient design, but also logging and error handling.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply