Another Mysterious issue in SQL 2005 & SSIS

Explain this:

Package runs successfully from BIDS
It runs successfully in SSIS store
It runs successfully when I manually execute the Job

JOB FAILS EVERY FREAKING NIGHT

It'd be easier to explain if you provided more information. Such as an error message.

I would imagine this is something to do with the user that SQL Agent is running under.

I also can't recommend this article highly enough:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

And the SQL Agent is running under the MyNetworkDomain/Administrator account.|||

TheViewMaster wrote:

Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

Exactly. Which is why you should change how you call the package as explained in the article that I linked to above.

-Jamie

|||

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?|||

TheViewMaster wrote:

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?

When you setup the job step, select "Operating System (Cmdexec)" from the 'Type' dropdown box.

-Jamie

|||

Change the job step type from "SQL Server Integration Services Package" to "Operating System (CmdExec)". Just enter the command to run dtexec ("C:\PROGRAM FILES\Microsoft SQL Server\90\DTS\Binn\dtexec") and then add the switches. You can copy all the switches from the command line tab in your SSIS step before you change it to a cmdexec step.

Are any of your connections in your package using sql authentication by chance?

|||Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory
|||

TheViewMaster wrote:

Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory

Have you installed SSIS on the server that SQL Server Agent is running on?

-Jamie

|||Have you checked the NT Event log for any signs of an error? I often forget to check this...

If you set it to execute during the day does it run? If it runs then compare the night failure times with your tape backup schedule. I recently had a maintenance plan failure caused by the nightly tape backups.

If it is still failing then it could be a security problem of some type. I have gotten this exact generic error message from security mis-configurations.

Thanks,
Greg Van Mullem
|||

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed|||

TheViewMaster wrote:

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed

Whereabouts is it installed? Because the C:\Program Files\Microsoft SQL Server\90\DTS folder is where it gets installed to by default.

-Jamie

|||I just dont get it (I used to think myself as a moderately intelligent guy but MS has really made me feel like a complete dumbass whenever I try to start using their new products - talking about that the Vista RC2 installation just hanged on me the other night with no apparent reason) 2 jobs and both have issues running automatically:

1st - runs on BIDS, runs on SSIS, runs as Manual job, Automatic Jobs fail
2nd - runs on BIDS, runs on SSIS - jobs (manual and automatic) always fail

And in do not have that DTEXEC installed on that machine to troubleshoot it according to the article posted

Another day - another load of problems
Thanks BG for making our job so nightmarish that we can excuse the $ spent on software - otherwise normal ppl could do it, rite?|||The management of SSIS is quite non-intuitive in my opinion, there are loads of posts about job failures. They usually have to do with encrypting sensitive information using a user key or the account used to run the job not having sufficient access. You should, however, have dtexec on the server somewhere. Did you do a file search for it?|||I didt search for dtexec - nada
So im copying the DTS/Binn directory from my development pc - let's see if that will help to come closer to solving this mystery

Another Mysterious issue in SQL 2005 & SSIS

Explain this:

Package runs successfully from BIDS
It runs successfully in SSIS store
It runs successfully when I manually execute the Job

JOB FAILS EVERY FREAKING NIGHT

It'd be easier to explain if you provided more information. Such as an error message.

I would imagine this is something to do with the user that SQL Agent is running under.

I also can't recommend this article highly enough:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

And the SQL Agent is running under the MyNetworkDomain/Administrator account.|||

TheViewMaster wrote:

Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

Exactly. Which is why you should change how you call the package as explained in the article that I linked to above.

-Jamie

|||

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?|||

TheViewMaster wrote:

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?

When you setup the job step, select "Operating System (Cmdexec)" from the 'Type' dropdown box.

-Jamie

|||

Change the job step type from "SQL Server Integration Services Package" to "Operating System (CmdExec)". Just enter the command to run dtexec ("C:\PROGRAM FILES\Microsoft SQL Server\90\DTS\Binn\dtexec") and then add the switches. You can copy all the switches from the command line tab in your SSIS step before you change it to a cmdexec step.

Are any of your connections in your package using sql authentication by chance?

|||Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory
|||

TheViewMaster wrote:

Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory

Have you installed SSIS on the server that SQL Server Agent is running on?

-Jamie

|||Have you checked the NT Event log for any signs of an error? I often forget to check this...

If you set it to execute during the day does it run? If it runs then compare the night failure times with your tape backup schedule. I recently had a maintenance plan failure caused by the nightly tape backups.

If it is still failing then it could be a security problem of some type. I have gotten this exact generic error message from security mis-configurations.

Thanks,
Greg Van Mullem|||

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed|||

TheViewMaster wrote:

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed

Whereabouts is it installed? Because the C:\Program Files\Microsoft SQL Server\90\DTS folder is where it gets installed to by default.

-Jamie

|||I just dont get it (I used to think myself as a moderately intelligent guy but MS has really made me feel like a complete dumbass whenever I try to start using their new products - talking about that the Vista RC2 installation just hanged on me the other night with no apparent reason) 2 jobs and both have issues running automatically:

1st - runs on BIDS, runs on SSIS, runs as Manual job, Automatic Jobs fail
2nd - runs on BIDS, runs on SSIS - jobs (manual and automatic) always fail

And in do not have that DTEXEC installed on that machine to troubleshoot it according to the article posted

Another day - another load of problems
Thanks BG for making our job so nightmarish that we can excuse the $ spent on software - otherwise normal ppl could do it, rite?|||The management of SSIS is quite non-intuitive in my opinion, there are loads of posts about job failures. They usually have to do with encrypting sensitive information using a user key or the account used to run the job not having sufficient access. You should, however, have dtexec on the server somewhere. Did you do a file search for it?|||I didt search for dtexec - nada
So im copying the DTS/Binn directory from my development pc - let's see if that will help to come closer to solving this mystery

Analysis services query timeout from SSIS

Hi,

I've just developed a simple integration services package which picks up a list of MDX queries from a table then runs them against our Cube using a .NET OleDb connection and saves the results to a csv file.

Unfortunately the queries frequently timeout, despite the fact that the Timeout property on the Connection Manager is set to 100000 and the Connect Timeout property is set to 600.

I have some logging on the packages and this revels that the queries will often timeout after less than 60 seconds with the error below:
<<Query>> failed with the following error: "XML for Analysis parser: The XML for Analysis request timed out before it was completed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Looking at the properties of the connection manager, I can see that the ConnectionString which has been generated is:
Data Source=Server;Initial Catalog=CubeName;Provider=MSOLAP.3;Integrated Security=SSPI;Connect Timeout=600;Auto Synch Period=1000;Timeout=100000;

Subsequent re-running of the package will often succeed, I'm guessing this is because the Cube has cached the query result and is able to return it fast enough.

Is there an additional timeout property that I'm missing somewhere?

Your help is much appreciated!

-Stuart

Perhaps try ExternalCommandTimeout? (http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx)|||Chris Webb built a "cache warming" prototype using SSIS and I think he found a way around this issue, you can find the post on his blog about this at http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1062.entry|||

Hi,

Sorry for the delay in replying and thanks for your suggestions. It seems that Chris Webb's solution was to ignore the timeout, which was fine given that he was building a cache warmer and didn't actually need to get the results. Given that I did need the results I decided to try and build some retry logic into my SSIS package.

I had a very clear and simple idea in my mind of how this would work. I would simply add an additional flow connector to the Execute SQL task with a Failure & Expression constraint where @.RetryCount < @.MaxRetries, then I would flow into a script task to increment @.RetryCount then back into the same Execute SQL task. Unfortunately though SSIS will not allow this as this would result in looping dependency.

The somewhat contrived solution I have now come up with is to extract my MDX Query list into a .NET Dataset pointed to by an SSIS Object variable, onto which I add an additional column to keep track of the number of retires. I then loop over the dataset and in the event of a query failing, I copy the row in the dataset and add it to the bottom, incrementing RetryCount.

It seems like a crazy solution to the problem of a query timing out. Is anyone at Microsoft aware that the Execute SQL task will timeout after 30 seconds when using an ADO .NET OleDb Connection with the Analysis Services provider, regardless of what you set the timeouts to be?

-Stuart