April 23, 2009 at 1:35 pm
SQL Server 2005 SP3. I have created a master package calling about 30 child packages to load a DWH. One child package per table. We are using windows authentication, an xml config file to set variables that are then passed to the child packages to be used for the connection managers.
All runs well from BIDS. We deploy to msdb and scheduled with SQL Agent on our dev server as an SQL Server Integration Services Package - all runs well. We deploy (to msdb) and scheduled on our prod server and it fails at the same spot about 80% of the time - about 20 packages complete, then it fails. We are logging pre,post,errors,warnings, and ontaskfailed to sysdtslog90 table ,but no errors or failures show up at all - just the pre-execute, post-execute and warnings. If we re-run the job immediately again it always runs to completion. If we restore the target db to the prior state and run the package it fails 80-90% of the time (the 2nd run works every time). Nothing about errors anywhere to be found.
We decided to change the SQL Agent job to an Operating System(CmdExec) and run with the dtexec utility. I copied the command line switches from the original SSIS job, and request errors and progress to the dtexec output and save to an output file - hoping that dtexec would give me better error messages. Here is the command line:
DTExec /SQL "\DWHLoad\DWHLoad_Master" /SERVER PROD /CONFIGFILE "C:\SSISPackageConfigurations\Master_dtsConfig.xml" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";xxxdba /REPORTING EP
Every time we run from SQL Agent as an OS command using dtexec utility it runs successfully 100% of the time. We restore the db's to exactly the same state and the SSIS package usually errors, but the dtexec never errors.
Has anyone had any experiences like this? I am fairly new to SSIS. I'd like to just go ahead and install the OS (dtexec) version of the job into production, but would like a tad bit more confidence. Is there a best practice of running SSIS packages from SQL Agent using dtexec? Could it be because we are using msdb for package store? Should I use file system? (I have not tried that yet - doesn't make much sense to me).
Any help and/or suggestions will be very much appreciated.
thx,
johng
April 23, 2009 at 2:23 pm
Is your xml config file referenced as an indirect configuration thru environment variable?
April 23, 2009 at 2:31 pm
no. it is directly passed on the command line.
The plot just changed - I was able to get the OS (dtexec) to fail - it ran through more child packages than the SQL Agent SSIS package - but the output was the same - no errors in dtexec output. It just stopped and the SQL Agent job reported error.
Not sure if I am glad or not . I am going to try 2 things now. Running the package as is from a batch file directly on the prod server, and creating a copy of the Master package and modifiying the precedence constraints in effect to allow less child packages running at the same time.
Any other ideas? It is so frustrating to not get consistency and more frustating not getting any kind of error messages!
TIA,
john g
April 23, 2009 at 4:15 pm
What account is running SQL Agent? Does that account have the necessary rights to login to the other systems with Windows Authentication?
Are you sure the job running as an OS command is actually completing successfully? Or, is it just not reporting that it failed?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 23, 2009 at 4:28 pm
The SQL Agent account is domain admin. I believe it is OK because every once in a while the job completes successfully. The output from dtexec on at the end of a completed job give the time etc, an incomplete(failing) job looks like this:
...
Progress: 2009-04-23 15:31:54.25
Source: Load DimAccount
Cleanup: 18% complete
End Progress
Progress: 2009-04-23 15:31:54.27
Source: Load DimAccount
Cleanup: 27% complete
End Progress
and it ends there. When running in cmd window I don't get any error messages either. At least I am getting it to fail every way I run it now - don't know why it fails much less tho when running dtexec.
Because of the wierdness of this, I am now running the package on our Dev Server, pointing the connections to the Prod Server - I am getting very suspicious of it. Bringing exact copies of the database over to the dev server, I cannot get it to behave the same way - it always succeeds.
Thank you - and keep those ideas coming...
john g
john g.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply