I have an SSIS .dtsx package that will run fine during debugging, however, when I run it as a job under the SQL Agent the job won't start.
To be more specific, the Agent will load the package, ensure that it passes validation (which it does), then sit there without starting the rest of the package.
The job will run under the "executing" status until I stop it manually.
Does anyone have any idea why it might be doing this
SQL Agent Hangs
somchai
Use the runas command (http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/runas.mspx) to launch cmd.exe as the same user that SQL Agent is running as and then use dtexec.exe to run the package.
-Jamie
MReynolds
Can I ask how you traced it back to your MergeJoin What is the specific indicator(s) that lead you to this conclusion. MergeJoin is quite unlikely to "hang" unless it doesn't get data on both of its inputs, in which case it is not actually hung but waiting for the data it needs to process. It is much more likely that one of the sources to the MergeJoin is not providing data and so MergeJoin is waiting for the input it needs to begin joining. That it work in debug also tend towards this because MergeJoin isn't dependent at all on permissions, etc in order to work but sources usually are.
Thanks,
Matt
dsm1982
I was under the impression that SQL Agent runs the package as a network service (ala NT AUTHORITY\SYSTEM)
How would I run the package under a that (or a different) account
Amarelo_Mrt
You might also try changing the MergeJoin's MaxBuffersPerInput property to 0 (or a larger number) to stop MergeJoin from performing backpressure functionality. Backpressure was implemented so that MergeJoin doesn't consume large amounts of buffers and cause large memory consumption. However, in your specific scenario it may be causing the "hang" you are seeing because of the slowness of one source over another.
HTH,
Matt
jbmeeh
I have discovered that SSIS is LAME. Its ability to work with SQL Agent (or the Integration Services Server) is dissappointing to say the least.
For whatever reason...The data from my flat file source is arriving to my MERGE JOIN too fast and the OLE DB Source (or ADO.NET DataReader) lags behind.
Therefor, my MERGE JOIN somehow becomes out of sync and just waits for the data from the OLE DB source.
So now I have to separate my data flow task into two different tasks.
One task has the flat file source and db source.....and performs an UPDATE and INSERT
The other task has the SAME SOURCES and dumps them directly into the MERGE JOIN.
Ohhhh no...don't put the sources into a multicast.....the world will erupt in chaos and all your systems will melt in a slew of the harshest irony.
So the answer is to cache the sources multiple times.
<sarcasm>
WOW THAT IS SOOOOOO EFFICIENT! I am so glad our company switched to Microsoft's fancy SSIS....What could we have ever done without it
ETL...Data Flow Tasks...Parallel Processing....Wooo hoo!....I'm having a party over here!
Whew! Thank you Mr. Gates....you are truly an American hero.
</sarcasm>
Tyler Whitney - MSFT
Can you expand on what you mean by "hanging up"
Interesting. I've never seen the MULTICAST be a bottleneck before!!!
It doesn't sound ridiculous to me - it sounds completely plausible. Why would they eve arrive at the same time If one source is producing 1000 rows but the other is producing 1000000 rows then the smaller dataset will almost certainly "arrive" before the larger one.
MERGE JOIN, by the very nature of what it does, requires data to be available to it from both inputs.
Is it possible that the perceived "hanging" experienced when using MERGE JOIN is simply that MERGE JOIN is doing what its supposed to be doing
Sure. You can capture the data into a raw file using the raw file destination. Your data then resides in a very very fast proprietary format and you can use it in subsequent data-flows.
Some people prefer to do the same thing but with in-memory ADO recordsets. Here you would substitute the raw file destination for a recordset destination. I am not keen on using recordsets because:
1) You need to write code to get the data back out of them (see here: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)
2) SSIS does not know, at design-time, the metadata of the ADO recordset. (This is the reason for there not being a corresponding recordset source adapter)
My recommendation - go with raw files for caching data!
-Jamie
slamzeedoor
Are you doing any logging from the package
If so, what appears in the log file
-Jamie
miguelangel
So your issue is that you are having to extract from the same data source more than once. Am I correct
1) Can you explain why the multicast is not an option for you Is it because of perceived problems with the MERGE JOIN If so, could you expand on that a little.
2) If you do not want to extract from a source more than once then you do have the ability to effectively cache that data somewhere for later use. I am happy to expand on this if you wish.
Clearly you have developed an opinion on SSIS that is going to be hard to change but I am going to try anyway :)
-Jamie
mkamoski
I traced it about (or at least I thought I did) by having both data sources output to a flat file.
Both the DB Source and the Flat File Source are able to output to my Flat File Source
I am bringing the DB Source and the Flat File Source into a MERGE JOIN
It is ONLY when I try to send the output of the MERGE JOIN to a destination that it hangs.
james100
Well...
I've checked all my permissions..doesn't seem to help.
I have actually ruled out the DELETE OLE DB problem anyhow
I have it dumping to a Flat File Destination instead...and it's still hanging up.
I traced the problem back to my MERGE JOIN.
It is the simplest LEFT OUTER JOIN...which I am doing all throughout my project (Agent doesn't seem to have a problem with any of them). For whatever reason...the SQL Agent hangs on this one.
Debug works fine.
ahhhhhh....I love this stuff
lost in the bitzone
If you have problems with SQL Agent then the first thing to check is security. Try running the package under the same account that SQL Agent runs as.
-Jamie
kkam
Can you expand on what you mean by "hanging up"
I wish I could elaborate more...Upon debugging from my computer the package runs smoothly.
I am kind of blind on this one because I have to rely on SQL Agent logging...which is logging nothing because the package is not producing any errors.
I did load the package in VS 2005 on the server itself. When I debugged it there...the package would hang at the MERGE JOIN.
By "hang" I mean just pause...sit there with a yellow box.
Debugging it on the server.....visually looked like this:
Flat File Source.....Green > Multicast.....Green > MERGE JOIN
DBSource.......Green > MERGE JOIN
MERGE JOIN.......Yellow.....Yellow.......Yellow........Yellow.......Yellow
Here is the REALLY weird part.
If I put a DataViewer in the path between the Multicast and the Merge (in affect pausing the data flow from the flat file to the merge during the debugging proccess) the package will complete successfully.
The flat file and the table each have around 2,900 rows.
I think I might try going in our server room with a preist and some holy water.
Daniel Kolman
Hahahah...
Actually...if it wasn't for your help....I would have probably given up a long time ago.
I would like to use a multicast for the two sources...in the same way I am doing throughout the rest of my package...
For whatever reason...for this particular Flat File and DB Source combination....the MERGE is hanging up.
The only thing I can figure out.... is it is occuring at the multicast of the Flat File.....as I am able to resolve the issue by simply taking it out.
Somewhere in there...the Flat File Source and the DB Source are arriving at the MERGE transform at different times (I know this sounds ridiculous...but it is the only way to explain it)
What's weird...is if I make my flat file huge (i.e. repeat the data in it several times) the issue is resolved because the Agent takes longer to process the flat file....in affect...giving it time to sync the data at the MERGE
It is frustrating to use the same Flat File Source twice...admittedly it's not a big deal...its just lame that I have to deviate from my design logic for this particular issue.
Caching the source data sounds very interesting....I would really appreciate it...if you were willing to expand on this.
Francesco_84
I have logging enabled.
However, the package never "starts" so the log doesn't pick up any errors.
I have narrowed it down to an OLE DB transform I have (through the process of elimination Enable/Disable)
The transform is trying to complete a DELETE function on a table. Whenever this transform is available in the package...The package won't start. When it's it's gone..the package completes successfully
Could it be a permission issue I have other DELETE transforms and they appear to be working.
Do you have any idea what I might look for