I am having problems of my DTS package hanging when run through the sql server agent through a scheduled job. If I execute the job manually it runs fine? If I run the package manually it runs fine? Of course there is no way to actually tell what is being hung? Any ideas?
Thanks David
I am encountering a weird issue with SSIS.
I have a very simple SSIS package that executes a .bat file. Here's
the
actual file it executes:
@Echo Off
c:
F:
cd ReportingServicesScripts
rs -i NoteBlankSnapshots.rss -S http://10.90.160.13/ReportServerTest
EXIT
The .bat file executes a Reporting Services .rss script using the
rs.exe
utility. When I run the command from the command prompt, it runs just
fine.
When I execute the .bat file manually, it runs and completes. When I
execute
the actual package, it also completes. But when I schedule the package
as a
job, it just hangs...it never finishes.
The owner of the job is an administrator in SQL Server. I have the
SQL
Server Agent configured to interact with the desktop - although
my .bat file
requires no input from the user.
I've created other jobs that just execute plain old SQL using the
same
owneer and these jobs complete just fine.
What am I missing?
Any help would be appreciated!
Thanks!!
I have a SQL 2000 SP4 server running on Windows 2003 SP2. About 6 months ago I started experiencing problems editing existing DTS packages. At that time it was just 1 package. (I tested that I could edit all others but this one package the first time I experienced the problem.)
But now it seems like anytime we experience import problems, I cannot edit the package so that I can verify that the import file is good. (So far the initial problem is always with the import file and once we figure out what that is and resolve it the package runs ok again.)
The problem is I'm planning on migrating to SQL 2005 and since I can't import DTS packages, I'm going to have to rewrite them as Integration Service Projects. If I can't edit the workflow tasks in DTS, it will be time consuming figuring out what the actual import file is. Also, most of these imports are coming from our AS400 which we are phasing out so I have to rewrite these to work with the new ERP package we are going to as we bring on each division.
I have not installed any post SQL 2000 SP4 hotfixes. So if anyone is aware of a hotfix that resolves this issue, please let me know.
Dear Folks,
I have a package that calls winzip to extract files(command line usage) in an Execute Process task. The package runs fine if I am logged in to the server. It hangs on the winzip task otherwise. The package is stored on the server (as opposed to the file system) & is run under a proxy account using the SQL Server Agent. I tried adding folder and WinZip32.exe permissions for the domain user who the proxy account was created under to no avail. Any Ideas?
Thanks for your help!
When I set the transaction option to required my package just hangs when I try to execute it. The status bar says "Validating" and then the name of the first destination data flow component (whatever that happens to be). I've let it sit for long periods and nothing happens. Any suggestions?
View 1 Replies View RelatedHello everyone,
I am having an odd behavior with an SSIS package that transfers data from one database to another. It seems to me that with some of the larger databases the services just stops working (0% utilization) when it is almost done. I don't get errors and the job stays in the "executing" state.
This doesn't happen always, but it will happen once the package is ran many times over the course of the week, I was wondering if you guys could point me in a direction on how I can find out the source of these hangs. Right now I am leaning towards it being a memory issue (or other misc. hardware problems) but I would like to rule out that it is software first.
Much help is appretiated.
Thank you
Jaime
I have an SSIS package that executes in about 1:20min from Visual Studio on my local machine. While executing, my machine is somewhat unresponsive.
When I deploy the package to the database server -- the very same database server that I am accessing from my local machine -- the package executes but eventually hangs. It appears to be running out of memory, and I usually have to kill the process to get the machine to respond. While it's hanging, the machine is unresponsive to all users. The hardware (including memory) is identical between my local development box and the server.
How should I troubleshoot this? I've tried deploying the package to MSDB, file system, running from dtexec, and running from dtexecui. This is very frustrating!
Thanks for any help.
--j
I have data flow tasks, one which validates the import file and one which processes the import file if the validation passed. The validation runs and pushes the three row types to three different recordset destinations. When I enter the processing data flow task, I have three parallel trees processing each recordset saved in the previous task. I'm using a script component to generate the rows which are then sorted and merged with the production database to find existing records. Based on this, I split to an OLE DB command (running an UPDATE command) or OLE DB destination (to simply insert the records.)
In this particular case, all records are being updated and nothing is being inserted new. Two of the three trees will complete the sort but hang on the merge, split, and OLE DB command components. The other will do the same but also hang on the split.
In another case, I truncated each destination table before running the package and the package runs fine.
Are toes being stepped on in the data flow task and causing a deadlock?
Update: I removed the sort transformation and sorted the rows before pushing them to the recordsets and I still get the same results.
I have a number of packages that I have moved from an old server. Each package was scheduled with a SQL Agent job. On the old server everything ran fine. All of the packages run fine from VS, from DTEXECUI and I have tried one from the command line with DTEXEC and it worked.
When I run from the SQL Agent job, I don't get a failure, the package just hangs. I let one of the agent jobs sit for an hour with no progress. The package typically takes about 15 minutes to complete.
Below is the output from my package log up to the point that it hangs:
#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnInformation,SIC0AD12,NT AUTHORITYNETWORK SERVICE,Item Pool,{0E40BC3D-A3E3-406A-B80B-D5989C49A589},{94F0C620-67CA-4DB7-8E68-F562ABC1450C},10/19/2007 4:23:24 PM,10/19/2007 4:23:24 PM,1074016266,0x,Validation phase is beginning.
OnProgress,SIC0AD12,NT AUTHORITYNETWORK SERVICE,Item Pool,{0E40BC3D-A3E3-406A-B80B-D5989C49A589},{94F0C620-67CA-4DB7-8E68-F562ABC1450C},10/19/2007 4:23:24 PM,10/19/2007 4:23:24 PM,0,0x,Validating
OnProgress,SIC0AD12,NT AUTHORITYNETWORK SERVICE,Item Pool,{0E40BC3D-A3E3-406A-B80B-D5989C49A589},{94F0C620-67CA-4DB7-8E68-F562ABC1450C},10/19/2007 4:23:24 PM,10/19/2007 4:23:24 PM,50,0x,Validating
I've been reading all of the posts concerning using a Proxy. I tried to set that up and got the same results.
Any help would be greatly appreciated.
We are on SQL Server 2000 SP2 version.
I have 3 DTS packages that are running successfully every day.
We need to change them as the source side tables are going to be changed pretty soon.
When I go into the designer view Enterprise Manager hangs when I do any of the following:
1. click on Properties for the transformation task
2. Click on Disconnected Edit
3. Click on Properties of connection 1(IBM DB2/400 Source), properties window pops up, now change the userid/pwd and click ok.
Any help will be greatly appreciated.
Hi,
We have a problem with visual studio. It hangs when i use the "execute package" option. New packages are running correctly but the packages which i've already built are not executing anymore...Any ideas to get things on track again?
Gr,
Hennie
When I push my SSIS packages up to my production server (which has a different data source than my developement environment) and I try to open the package on the production server, it takes forever for to validate all the steps of the SSIS package because it's trying to validate against a datasource that isnt there, so it just waits for each element it's validating to time out. This is exceptionally annoying.
Is there a way to turn off this validation 'feature'?
I've built an SSIS package in SSDT 2014. The package was running successfully
When I close and open the package it hangs on validation of single task out of all.
OLEDB DESTINATION Task
I have tried several times to close the SSDT and open but facing same issue.
These seems like bugs in SSIS/SSDT. What would cause the relocation of some script code to hang the validation process like this?
We're experiencing a problem where intermittently our SSIS packages will hang. There are no log errors or events in the event viewer. It will happen whether the package is executed from the SQL Job Agent or run from BIDs. When running from BIDs it appears to hang inside one of the data flows (several parallel pipes with sorts, merge joins etc...). It appears to hang in multiple pipes within the data flow component. The problem is reproducable, we just kill it and re-run, and it appears to hang in the same places.
Now here's the odd thing: as we simply open and close some of the components in the pipe line after the place it hangs, a subsequent run will go further in the pipeline before hanging. If we open and close all the components after the point it initially hung, the data flow will run fine, from there on out. When I say "open and close" I mean no changes are made, we simply double-click the component, like a merge join, then click 'close.'
To me this does not seem like a memory problem but likely something is wrong with the metadata, where opening a component and closing it somehow alters the metadata to "right it".
This seems to occur intermittently after we make modifications to the package. It's like if you make any mod, even unrelated to the data flow, you then have to go through and open and close every component in your package to ensure it will work. Again, no errors or warnings are fired.
Has anyone seen this type of problem?
Help!
I am using Script Transformation to output a new column as image[DT_IMAGE]
field to store serialized object. In the VB script, the sample code as
Row.serializedobject.AddBlobData ( binaryArrayReturnedFromC#dll )
The package always runs fine on my developing machine and will halt on other
machine at AddBlobData after certain number row records were processed. I am
stuck here. Anyone has any suggestion?
What I need is reading data from mutiple tables in one database and writing
into a single table in another datable. In order preserve all the columns
data, I use input column fields to construct a new object and then serialize
it, and store the serialize data into detination db table. (The object and
serialization function is coming from c# dll.)
Dim b As BusinessLicense = New BusinessLicense()
b.ApprovalDate = Row.approvaldate
b.BusinessId = Row.busid
b.BusinessName = Row.busname
b.NaicsCode = Row.naicscode
b.NaicsDescription = Row.naicsdescr
b.OwnerName = Row.ownername
b.Phone = Row.phone
b.Pkey = Row.pkey
b.RenewalDate = Row.renewaldate
b.StartDate = Row.startdate
b.Suite = Row.suite
Row.serializedobject.AddBlobData(Serializer.Serialize(b)) '''----This is blocking line
Row.infoType = BusinessLicense.TYPE
Both machine is xp with sp2. and standard SQL Server 2005 - 9.00.1399.06
Thanks!
I posted a message about this yesterday but I have more info..
I have a server (SQL 7.0, sp2) that anywhere I connect to it through EM the EM hangs. I can drill down into the server and even get into the Security drop down but if I hit Databases to drill down or Management etc. It hangs.
I did profiler on this and another server. When I click the + by the Database folder I hang and get
"TSQL:Batch Starting: exec sp_MSdbuseraccess N'db', N'%'"
but never get a "TSQL:BatchCompleted" for that statement. (Where other working servers I do get the set of both start and completed)
Drilling into the Security tab runs the query: exec sp_MSDBUserpriv... etc.) and this one works..
Anyone have any info on: exec sp_MSdbuseraccess N'db', N'%'
I can't find anything in BOL, Microsoft KB, Technet etc.. Please Help as there are certain things I can't do without EM... THANKS!!!
I have been trying all day to install SQL 7.0 on an NT 4.0 SP6a server. It gets to the point where it wants to load MDAC and just goes out to lunch. No errors are generated and the rest of the system does not appear to be affected. However, the only way to end the install is to end task it. Has anyone run into this?
View 1 Replies View RelatedSome of my jobs hang at FTP step for several hours until some one kills the FTP (it's a perl process) on the box. Is there a way to find how much time a particular step of the job is executing? and know it by somehow if it's hanging at that step?? If we know this,I want to kill the perl process(FTP) on the box in an automated way whenever the job hangs! Any ideas welcome!! Thanks. :-)
Di
Hi all - I'm having a problem with a dts job that I haven't run across before. The function of the job itself is pretty basic just moving data into a sql table using a few lookups and mappings. The job starts fine and gets to a status of 5000 records processed and just sits there. It's doesn't go into a 'not responding state' nor does it produce any error information in the error file I set up. Anyone have any thoughts on what might be happening? How to identify the specific record it may be hanging on? Help!! - this one is driving me nuts.
View 4 Replies View RelatedI'm running a pretty simply DTS just to pump some data into a table. Nothing fancy just a couple lookups and a couple columns being copied over. I get to 5000 records processed and the DTS just spins its wheels. It does not go into a 'not responding' state. There are no indexes on the table and the db size is plenty big so it is not reindexing or resizing the db at this point. Any ideas on how I can debug this one would be greatly appreciated.
Thanks
I am having a problem importing data into SQL 7 from any type of source. I go through the whole import process no problem. When I click the finish button to start the import, nothing at all happens. Enterprise Manager and the DTS just hang and I must use crtl+alt+delete to end the program. Can anyone give me any suggestions as to what might be happening. Big Thanks in advance, I've been working on this for days.
glevi
Anyone else had this problem ?
I've run dbcc check commands on a 5Gig database which has 2 Gig of unallocated space, with no problems with the tables or database.
However when i try to run dbcc shrinkfile ( on the DATA file ) from Query Analyser the command just runs indefinitely with no response.
Duncan
PS SQL 7 sp 2
Hi everyone, I had to do a restore of a database from tape. I have the tape set to eject when it's complete, the tape ejects and the dialog box looks like the restore finishes fine. But when I look at the database after the restore it is still in the loading phase. So the restore never really took or looks like it hangs. Do you guys know why or how to fix this problem? Thanks in advance.
Stink!
My client is using SQL Server 6.5 on NT 4.0. They have recently began to have their SQL server session freeze intermittently.
I have determined that only users that are accessing one particular database are freezing which leads me to a locking problem.
What is the best way to determine the locking problem (i.e. event logs, sp_lock, ....)?
Hello everyone,
I've done some searching, asking of friends, and searching every log file and event file I can think of. So now I'm coming here.
Recently I moved some of our databases from an NT4 box running SQL 7 to an Advanced 2000 box running SQL 2000. The web server is still on an NT4 box. It seems that about three times a day or so ASP type files will hang on the webserver. This server hosts different sites and all ASP type files will stop even if some of them hit the old SQL7 server.
Right now I moved the web server back to looking at the SQL 7 machine and things are going fine.
Can anyone offer me a direction to start looking? Why is it working fine to the old stuff but not the new stuff? Is there an issue with NT4 with its IIS trying to talk to an Advanced 2000 with its SQL2000?
Thank you in advance for any help.
I've written a recursive CTE to update a table's heirarchy data with a lineage that the CTE builds. This works and executes over 80,000 records on my local test environment in about 45 seconds. However when I try to execute it from a "production" environment, it hangs and never completes the update (well at least it's taking longer than 5 minutes and I'm too impatient to wait). The CTE itself still runs fine and returns data if I do a Select *, but not when I run the update. Here's my CTE, any ideas? Is there anything in database settings that would disallow this query update? or field requirements?
WITH MyLineage (ID, Lineage, ParentID)
AS (SELECT ID, CAST('.'+ convert(nvarchar(max), ID) +'.' AS nVarchar(max)) as Lin, ParentID FROM Equipment WHERE OrgID=2 AND ParentID IS NULL
UNION ALL
SELECT E.ID, CAST(L.Lineage+ convert(nvarchar(max),E.ID)+'.' AS nVarchar(max)) AS Lin, E.ParentID FROM Equipment E INNER JOIN MyLineage L ON L.ID=E.ParentID WHERE L.ID=E.ParentID)
UPDATE E SET E.Lineage=L.Lineage FROM Equipment E INNER JOIN MyLineage L ON E.ID=L.ID
If I try to do the update like this:
UPDATE Equipment SET Lineage=(SELECT Lineage FROM MyLineage WHERE MyLineage.ID=Equipment.ID)
it will hang in my local test environment as well. It seems like the other sql 2005 server instance is converting the join update into a nested select update, or something. I don't know what's going on, help?
Hi ppl
I have a DTS that is loading 6 text files into respective tables. Before loading this files the tables are emptied using SQL tasks with the following code:
DELETE
FROM Table1
WHERE (OMC=?)
OMC is a PK from table1 and ? is a parameter within the DTS.
Occasionally the system hangs executing these delete statements for one of the tables (not always the same!)
I’m forced to reboot and then DB goes trough a recovery process.
Any idea what might be wrong?
Thanks in advance
Hi,Currently it is running on SQL 7.00.623 on Winnt 4.0+SP5.Now i want to upgrade this with SP 4.As i follow the installation procedure according to MicrosoftSQL7SP4 readme help, It hangs during the installation of Mdac_typ.exe.Don't know where and whats went wrong with the procedure.I've tried 3-4 times to install it but the same problem raises. Pleasesome body assist me how to complete this installation.Thanks in advanceIvar
View 2 Replies View RelatedI 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?
I've installed SSE2005 (with SP1 and the hotfix rollup) on a Small Business Server 2003 SP1, and 90% of the time, all is well.
SSE is running two user databases; one for an ASP web-based application, and one for a desktop application. Occasionally, pretty much everything for both apps hangs for a number of seconds (variable, but probably about 10-30 seconds at a time). During the hangs, even using Management Studio Express becomes impossible, and Activity Monitor just times out, so I can't see what's happening. SSE's Errorlog and server Event Logs show nothing unusual.
The hangs tend to come in batches, on-and-off for an hour or so, after which the problem clears and it's service as usual.
Does anyone have any ideas what the problem might be, or at least how to start tracking it down?
Hi,
I have a package that runs just fine from BIDS. There are no message boxes in any scripts, or prompts of any kind.
However, when I attempt to the exact same package from a job, it just hangs forever.
Is there an obvious explanation for this?
Thanks
I have a problem where a SQL 2005 server in the morning will deny all connections do to a time out error. Sytem has 4 CPU's in it and sqlservr.exe is utilizing 85% of all CPU's. If I run filemon it does not show activity to any of the SQL databases. I get a login timeout message when I try to connect via the enterprise manager or from a command prompt, even if I bump the timeout delay to 5 minutes. If I restart the SQL Server services all is fine for a few days.
View 4 Replies View RelatedOn random occasions, our SQL Server instance will just hang and the users will not be able to use the application until we restart the SQL Server 2005 Service. Anyone have any ideas what we might do to figure out what is going on? The server hung up at 8:00 AM this morning. There was nothing after our midnight backup in the log until we rebooted the server at 8:05 AM.
Any suggestions would be greatly appreciated. Thanks! - Eric-