SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Intermittent Failures with new 2012 SSIS Job
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

photond
Starting Member

20 Posts

Posted - 06/24/2013 :  09:05:03  Show Profile  Reply with Quote
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:00

Every 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

India
52325 Posts

Posted - 06/24/2013 :  13:41:02  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

photond
Starting Member

20 Posts

Posted - 06/24/2013 :  16:50:09  Show Profile  Reply with Quote
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. :(
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/25/2013 :  00:43:33  Show Profile  Reply with Quote
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 error


http://visakhm.blogspot.com/2012/09/easy-package-execution-monitoring-in.html

We'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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

photond
Starting Member

20 Posts

Posted - 06/27/2013 :  10:03:51  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/27/2013 :  10:27:22  Show Profile  Reply with Quote
is that a project parameter created for storing connection string of edwstaging source? is it encrypted?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

photond
Starting Member

20 Posts

Posted - 06/27/2013 :  11:30:40  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/27/2013 :  12:30:39  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

photond
Starting Member

20 Posts

Posted - 06/27/2013 :  17:13:37  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/28/2013 :  00:45:55  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

photond
Starting Member

20 Posts

Posted - 07/02/2013 :  09:53:28  Show Profile  Reply with Quote
No. I found this link that looks like a similar problem though. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/68d85a2b-09a4-4de8-9e90-6cc28c48a0e0/error-reading-variable

I'm still puzzled, we're still getting the failures occasionally.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/03/2013 :  01:04:50  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

photond
Starting Member

20 Posts

Posted - 07/03/2013 :  15:43:47  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/04/2013 :  01:12:10  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000