| Author |
Topic |
|
photond
Starting Member
20 Posts |
 Posted - 2013-06-24 : 09:05:03
|
| Our team has several jobs in production using the old package deployment model. They have been running successfully and we haven't had any issues. However, we recently developed a new job that uses the project deployment model in SSIS 2012. It's a quick job that typically takes less than a minute. We set it up to run at 6:00 and 6:30 and it never fails consecutively. Since the beginning of the month, it has failed at: -6/22 at 6:30 -6/18 at 6:30 -6/16 at 6:00-6/13 at 6:00 -6/11 at 6:00-6/10 at 6:00Every other day and time not listed, it ran successfully. The job calls a few SSIS packages and processes a cube. The logs for the failures give almost no information. All that it says when it does fail is, "ended unexpectedly." When the failures occur, they are at different stages in the job and can happen after 6 seconds, 8 seconds, or 10 seconds, it appears to be completely random.I know this is kind of a shot in the dark, but has anyone experienced similar problems with the new project deployment model? Any tips or ideas for ways to resolve the failures? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 13:41:02
|
| Not something I've seen. But since it happens alternatively my (wild) guess is there might be some other job running once in two days and doing something on the cube run almost the same timeline (or may be an earlier job getting frozen a bit in cube processing step). This might have interfered and caused cube steps to fail.Worth checking on other processes running at the sametime.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
photond
Starting Member
20 Posts |
Posted - 2013-06-24 : 16:50:09
|
| Thanks for your response, visakh16. There was a backup job we had scheduled around 6, but we changed the time for that job weeks ago. I thought that might be the issue too, but the failures have continued after changing that. There aren't any other jobs running at those times. I'm completely out of guesses. It almost has to be related to the new project deployment model as that is the only job failing. We also changed the job schedule to different times of the day and it still failed randomly. I'm afraid we might just uninstall and reinstall SQL Server. :( |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-25 : 00:43:33
|
| do you've any other details on the error. Did you try running the all executions report from ssms and see if you could get any more details on the errorhttp://visakhm.blogspot.com/2012/09/easy-package-execution-monitoring-in.htmlWe're also having a system with sql jobs running executing SSIS 2012 packages (in proj deployment mode) and havent had any issues so far!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
photond
Starting Member
20 Posts |
Posted - 2013-06-27 : 10:03:51
|
| Thanks again, visakh16. For the first time yesterday, we received a meaningful error message: "Error: Reading the variable “$Project::EdwStaging_ConnectionString” failed with error code 0xC0010009." That message hasn't shown up in subsequent failures but it may be the cause of our problem. The variable might be locked in memory while it's called by another package, leading to the intermittent errors. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-27 : 10:27:22
|
| is that a project parameter created for storing connection string of edwstaging source? is it encrypted?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
photond
Starting Member
20 Posts |
Posted - 2013-06-27 : 11:30:40
|
| Yes, it is a project parameter for our connection string to our SQL EDW. It's not encrypted, it lists out the connection string (Data Source=....;Initial Catalog=EdwStaging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;) in the value field. Sensitive is set to False and required is set to True. Is that the correct setup? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-27 : 12:30:39
|
| The settings look fine.Are you sure the name is defined exactly as EdwStaging_ConnectionString as its case sensitive. the error code suggest its Item not found error which means its unable to find the referred variable in the variables collection within package.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
photond
Starting Member
20 Posts |
Posted - 2013-06-27 : 17:13:37
|
| Yes, it's defined exactly as that. Could it be that the variable is called multiple times at once, causing the error? We have about 15 extracts that are called in a container that use that connection string. I don't understand why it's successful more than half the time though. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-28 : 00:45:55
|
| hmm..is any of those trying to change the value of this variable? unless that happens, i dont think that concurrent access wouldbe an issue------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
photond
Starting Member
20 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-03 : 01:04:50
|
| Did you try scoping trick which was specified in link? ALso see if you can rearrange tasksto avoid concurrent usage of variables by more than 1 task.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
photond
Starting Member
20 Posts |
Posted - 2013-07-03 : 15:43:47
|
| Thanks for all your responses, visakh16. I think we finally found the issue, Microsoft released a hotfix that we're going to try.http://support.microsoft.com/kb/2837964 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-04 : 01:12:10
|
quote:
Originally posted by photond
Thanks for all your responses, visakh16. I think we finally found the issue, Microsoft released a hotfix that we're going to try.http://support.microsoft.com/kb/2837964
Thansk for sharing this------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
Intermittent Failures with new 2012 SSIS Job