Package Detect That Instance Is Already Running?
Aug 2, 2006
Folks,
I have a package scheduled to run every hour.
Users have asked if, in addition to the scheduled run, they can have it
so that they could dump a file into the input directory and then
kick-off the package immediately.
Problem is that things fail if they try to start the package when the scheduled instance of the package is still running.
Is there any way that the package could check to see if an instance if
itself is currently executing and refuse to execute if there IS an
instance running?
PJ
View 5 Replies
ADVERTISEMENT
Dec 6, 2007
Hi there
We have a SSIS run which runs as follows
The master package has a configuration file, specifying the connect strings
The master package passes these connect-strings to the child packages in a variable
Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pc€™s.
We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.
The package is run from the file system, through a job-schedule.
We experience the following when running on a not default sql-server instance (called dkms5253uedw)
Case 1:
The master package starts by executing three sql-scripts (drop foreign key€™s, truncate tables, create foreign key€™s). This works fine.
The master package then executes the first child package. We then in the sysdtslog get:
Error - €œcannot connect to database xxx€?
Info - €œpackage is preparing to get connection string from parent €¦€?
The child package then executes OK, does all it€™s work, and finish. Because there has been an error, the master package then stops with an error.
Case 2:
When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.
Case 3:
When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.
Case 4:
When we then stop the sql-server on the default instance, the package faults again, this time with
Error - €œtimeout when connect to database xxx€?
Info - €œpackage is preparing to get connection string from parent €¦€?
And the continues as in the first case
From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).
We have tried delayed validation in the packages and in the connection managers, but with the same results (error).
So we are desperately hoping that someone can help us solve this problem.
Thanx,
/Nils M - Copenhagen
View 3 Replies
View Related
Mar 15, 2008
I am using SMO EnumAvailableSqlServer to make a list of available SQL servers in local network. I have tested it in two different local networks. In the first all works fine, but in the second I have noticed that when disconnected from the network the call fails to even detect the locally installed sql servers.
I know that I can scan registry to get installed local servers, but I want to know what could be making problem.
View 3 Replies
View Related
Aug 4, 2000
From client, how can I detect which services're running or not? Example for SQLAgent, MS DTC..
Thanks.
View 1 Replies
View Related
Oct 3, 2015
i just kicked off a job that starts a data driven subscription that generates ~200 excel spreadsheets.
I kind of expected them to appear in the execution log, and that the TimeEnd would reflect when they complete.
instead, it appears that the TimeEnd is when the SQLcommand finished executing, ie, a few milliseconds after the start.
it does not reflect when the report finishes completing; if i run sp_whoisactive, i see a stack of reports at a time clawing their way through the process. (ie i see calls to [CreateChunkStatement] and all the other steps a report kicks off.
Other than watching execution requests, how can i know if anything is running as far as subscriptions go?
View 0 Replies
View Related
Apr 27, 2007
SSIS Experts,
I am executing a child package programmatically and want few lines of code to detect cause of the failure on the fly. One way will be to run the child package through dtexec command uility. But on the fly i will be assigning few values in Parent package variable to the child variables and finally run the child package. Since we cannot apply expressions or use precendence constraints in place, It seems that we are left with only choice to programmatically detect errors in tasks. In DTS, it was achieved this way:
Code Snippet
For Each ostep In oPkg
If ostep.ExecutionResult = DTSStepExecResult_Failure Then
ProcessSubDTSPackage = DTSTaskExecResult_Failure
End If
Next
Thanks
Subhash Subramanyam
View 4 Replies
View Related
Nov 15, 2007
The box I am trying to connect to is running two instances of SQL Server.
There is a SQL Server 2005 instance which is the default. There is a SQL Server 2000 instance which is named 'SQLSERVER'.
I can connect to the SQL Server 2000 instance no problem:<add key="ConnectionString" value="server=MYPCSQLSERVER;database=mydatabase;user id=****;password=****" />
However, I am having trouble connecting to the Default SQL Server 2005 instance. I have tried:
<add key="ConnectionString" value="server=MYPC;database=mydatabase;user id=****;password=****" />
but it doesn't work. I have tried explicitly setting SQL Server 2005 to use port 1434 (as SQL Server 2000 is running on port 1433), and then used:
<add key="ConnectionString" value="server=MYPC,1434;database=mydatabase;user id=****;password=****" />
but this doesn't work either.
Am I mssing something here? Any help much appreciated
Thanks...
View 2 Replies
View Related
May 2, 2008
Hi All,
I am in the process of moving from a 32-bit SQL Server 2005 Enterprise (9.0.3054) to a 64-bit SQL Server 2005 Enterprise (9.0.3054 with 4 CPUs and 8GB of memory on Win 2003 SP2) and the process has been very frustrating to say the least. I am having a problem with packages that I created on my 64-bit SQL Server. I am importing a few tables from the 32-SQL Server into the 64-bit SQL Server using the Task --> Import to create the package.
Sometimes when I am creating a package I get the following error in a message box:
SQL Server Import and Export Wizard
The SSIS Runtime object could not be created. Verify that DTS.dll is available and registered. The wizard cannot continue and it will terminate.
Additional information: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.Windows.Forms)
Other times when I run a package that has run successfully before I get the following error:
Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x025d23f0.
Other times I get this error message:
.NET Runtime version 2.0.50727.1433 - Fatal Execution Engine Error (79FFEE24) (80131506)
And still other times
The package appears to hang when running. By this I mean that the Package Execution Progress shows progress up to a point then it just stops. (The package takes about 17 seconds to run normally) CPU usage is at 1% and the package cannot be stopped.
I have deleted and re-created the package several times and I have also re-installed the service pack on the SQL Server (9.0.3054) but that did not help.
Does anyone have any other suggestions to try?
Thanks.
View 4 Replies
View Related
Dec 16, 2007
how to check how many instance are running in the db server
View 1 Replies
View Related
Nov 5, 2007
Hi all,
I am creating a dts package to export files from one database to another database.
I tried to search for ways to execute the files and found out that i need to add
reference to Microsoft.Sql.managedDts. However, I cannot find this reference from
my reference. Do i have other alternatives to run this file?
View 8 Replies
View Related
May 25, 2007
I need to get up on my computer with MSSQL Server 2005 SP2 an exact copy of running MS SQL Server 2005 SP1 instance (with system objs, accounts, etc.).
In the same LAN.
Well, not exact one but the one as close as possible...
And what exactly may I mimick without interfering to existing SS2005 in production?
What is the best way to do it without invoking excessive (manual) work and problems?
I see dozens of ways and I am still in doubt...
No ideal or straight-forward solution
View 12 Replies
View Related
Apr 26, 2006
I am receiving an error on my master package that executes a number of other packages. The individual packages work fine when executed by themselves. However, I am getting the following error when I attempt to execute it from another package:
Error: Failed to acquire connection "conneciton". Connection may not be configured correctly or you may not have the right permissions on this connection.
Thanks in advance for your help.
View 1 Replies
View Related
Feb 2, 2007
I'm a bit confused about how ssis handles concurrent package runs
let's say I'm running this package and I 've got 3 variables set in it
VARA
VARB
VARC
and by default they are all set to 0
if I run
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARA].Value;1
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARB].Value;1
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARC].Value;1
I'm expecting to run 3 isloated version of the package with in
first version
VARA=1
VARB=0
VARC=0
second version
VARA=0
VARB=1
VARC=0
third version
VARA=0
VARB=0
VARC=1
but it doesn't seem like doing that the maxconcurrent variable is set to 40 to be on the safe side.
when I run I get
first version
VARA=1
VARB=0
VARC=0
second version
VARA=1
VARB=1
VARC=0
third version
VARA=0
VARB=1
VARC=1
any ideas?
Thanks
View 2 Replies
View Related
Jul 6, 2007
I am trying to run a SSIS package (called "Parent") that calls another package (called "Child"). The packages are stored in SQL Server (MSDB database) and are called from a console app by the code below:
Public Shared Function RunPackage(ByVal server As String, _
ByVal userName As String, _
ByVal password As String, _
ByVal packageName As String, _
ByVal packagePassword As String) As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
Dim result As String = ""
app.PackagePassword = packagePassword
pkg = app.LoadFromSqlServer(packageName, server, userName, password, Nothing)
pkgResults = pkg.Execute()
result = pkgResults.ToString() & vbNewLine
For Each err As DtsError In pkg.Errors
result += "Task: " & err.Source & vbNewLine
result += ParseErrorMessage(err.Description) & vbNewLine
Next
Return result
End Function
If I just run the Child package with this code, then it works! But is I run the Parent package from the same code, then it fails with this error: "Error 0x80004002 while preparing to load the package. No such interface supported".
The only difference between the 2 packages is that the Parent calls the Child, and the Child calls nothing.
The Connection Manager in Parent used to connect to Child uses SQL Server Authentication and it needs to be that.
My question: From VB.NET, how do I run a SSIS package that calls another package?
View 1 Replies
View Related
Aug 30, 2006
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.
Thank you
Tej
View 7 Replies
View Related
May 12, 2006
Hi,
I have 2 instance of SQL Server in my system. I want to view Stored Packages in both instance using SQL Server Integration Services.
How to accomplish this task.
Regards, Balaji Thiruvenkataraju
View 3 Replies
View Related
Sep 12, 2005
Can anyone point me to some sample code ord give me an example. I'm trying to run a DTS package from my asp.net/vb.net application. I don't need to pass it any variable, just need it to run. I can't seem to find anything on the net for this. Thanks in advance, Ryan
View 7 Replies
View Related
Nov 21, 2001
Hi
I have one DTS package looks for specific file in a directory to process the data into sql. I have schedule this package to run every 5 mins.
I would like to invoke the package when a file arrive at the folder instead up running it every 5 mins.
Thanks
View 1 Replies
View Related
Oct 19, 2007
Hi Folks
I am calling SSIS package from my .net code. I want to know whether my package is still running or not. And if it is taking longer than a limit then execution of package should stop. I am executing package in different thread like this
DelegateExecute del = new DelegateExecute(pkg.Execute);
del.BeginInvoke(null, null);
As I explored, I learnt that RunningPackage class could help me in not only determining whether package is running butalso in stopping too. Unfortunately RunningPackage class is saying that there is no package running! Is Runningpackage works only with the packages running from MSDB?
Following is the code I am using.
public string LoadPackage(string path, long ThrashHold)
{
string pkgLocation;
Package pkg;
Application app;
//DTSExecResult pkgResults;
string str;
pkgLocation = path;
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
//pkgResults = pkg.Execute();
long startDT = DateTime.Now.Ticks/10000;
DelegateExecute del = new DelegateExecute(pkg.Execute);
del.BeginInvoke(null, null);
DealPackage();
while (pkg.ExecutionStatus != DTSExecStatus.Completed)
{
//str = "pkg.ExecutionStatus = " + pkg.ExecutionStatus.ToString();
//str = str + "pkg.StartTime = " + pkg.StartTime.ToString();
//str = str + "pkg.StopTime = " + pkg.StopTime.ToString();
//str = str + "pkg.ExecutionDuration = " + (DateTime.Now.Ticks / 10000 - startDT).ToString();
//Console.WriteLine("********************************");
//Console.WriteLine(str);
//Console.WriteLine("********************************");
if ((DateTime.Now.Ticks / 10000 - startDT) > ThrashHold)
{
Console.WriteLine("********************************");
Console.WriteLine("package should be stopped");
//Console.WriteLine(str);
Console.WriteLine("********************************");
break;
}
}
str = "pkg.ExecutionStatus = " + pkg.ExecutionStatus.ToString();
str= str+"pkg.StartTime = " + pkg.StartTime.ToString();
str= str+"pkg.StopTime = " + pkg.StopTime.ToString();
str = str + "pkg.ExecutionDuration = " + pkg.ExecutionDuration.ToString();
DBOperations dbr=new DBOperations(ServerName,"PEPE_SSIS","sa","sa@123");
dbr.EnterPackageDetail(path,pkg.ExecutionStatus.ToString(),pkg.StartTime.ToString(),pkg.StopTime.ToString(),pkg.ExecutionDuration.ToString());
return str;
}
private void DealPackage()
{
RunningPackages pkgs = app.GetRunningPackages(se);
int pkgsRunning = pkgs.Count;
Console.WriteLine("Packages before stop: " + pkgsRunning);
foreach (RunningPackage p in pkgs)
{
Console.WriteLine("InstanceID: " + p.InstanceID);
Console.WriteLine("PackageDescription: " + p.PackageDescription);
Console.WriteLine("PackageID: " + p.PackageID);
Console.WriteLine("PackageName: " + p.PackageName);
Console.WriteLine("UserName: " + p.UserName);
}
pkgs = app.GetRunningPackages("yourserver");
foreach (RunningPackage package in pkgs)
{
package.Stop();
}
pkgsRunning = app.GetRunningPackages("yourserver").Count;
Console.WriteLine("Packages after stop " + pkgsRunning);
}
View 1 Replies
View Related
Jul 27, 2006
one more guidance needed with regard to the Quotation marks
path = @"D:SSISProjectIntegration Services Project1ArchiveTicket.dtsx";
jobCommand = new SqlCommand(@"xp_cmdshell 'dtexec /f "'path '" /Set package.Variables[User::ArchiveFileType].Properties[Value];""Excel""'", cconn);
How Do i set the path's value in c# the quotation marks are wrong again. cos i want to set value of path according to selection in the UI.
Select("deviceUniqueId = '" + deviceUniqueId + "'"); is an e.g of Doing in C# but when i implement it through text it does not recognize
Thanks
Jas
View 4 Replies
View Related
Jun 2, 2006
When I ran a package directly on the machine with sql 2005 installed it ran at a fine speed. But when I used Remote Desktop to access the same client and run the same package it was much slower.
1. I think that the package is running in the memorycpu of the local machine. Is this correct?
2. Is there any way for the remote client to run the package in the server's memorycpu?
TIA,
barkingdog
View 13 Replies
View Related
May 21, 2002
Hi all,
I am trying to run a Excel macro from a DTS package.I've created the macro and Batch file to run a macro, and i am executing a batch file from DTS package.I am able to run a macro when i am executing a DTS package manually.but its not working when i schedule the process. any solutions?
its not the security problem also.I've checked it.
View 1 Replies
View Related
Aug 25, 2000
I have a DTS package hat copies several tables to another Reporting database.
When executing it as a apachkage, it works fine. However, when I try to schedule it as a job, it fails. I tried this with another package and it didn't work also. Any ideas?
Thanks,
Steve
View 1 Replies
View Related
Jan 7, 2003
Is there a way to run a local package from a stored procedure?
Thanks,
Ken Nicholson
View 2 Replies
View Related
Mar 15, 2004
I have a DTS package that is owned by Joe.
A job that is owned by Domain Admin runs Joe's job every night.
Joe has left the company and his account will be deleted.
2 Questions:
1. Will the job still be able to run the DTS package ?
2. If I need to change the owner of Joe's DTS package, how do I do that ?..just a simple 'Save as' ?..and if so, I will not be able to save the DTS package with the same name...and thereby the job will not recognize the new DTS package name...will I have to re-shedule the new DTS package ?
thank you
View 6 Replies
View Related
Mar 17, 2004
Hi,
Does anyone know if it is possible to run a DTS Package from MS-Access database (.mdb)? If yes, could you please specify how to do that? Thanks!
Regards,
av
View 2 Replies
View Related
Apr 30, 2008
Hi
I have a SSIS package stored on the database server under Stored Packages within the Integration Services section.
Does anyone know how to run this package using SQL?
Thanks
View 5 Replies
View Related
May 6, 2008
I'm trying to stop a DTS package from continuing to run after the result of the following ActiveX script:
Function Main()
If day(date) > 2 and day(date) < 10 and weekday(date) = 2 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End if
End Function
Any help would be appreciated.
View 2 Replies
View Related
Jul 20, 2005
We have 2 Windows 2000 Servers, Server2 and Server3. Server3 alsohosts a SQL Server 2000 Instance. Server2 DOES NOT host any SQL Server2000 instances and is used as our Application Server. We are trying tostop a "service" that is setup on Server2 by executing a DTS packagethat resides in SQL Server on Server3. So far, we have had little luckdoing this. In other words, if you execute this package, it tries tostop "service" on Server3 as opposed to stopping it on Server2.Appreciate any help and feedback.ThanksJagannathan Santhanam
View 1 Replies
View Related
Aug 18, 2006
I'm trying to run a SSIS package (dtsx) from inside an sql job (SQL Server agent). This works fine if the user running (run as) the step is a local admin on the server. If it's not, I get the error message "The package could not be loaded. The step failed". This happens even if the user has all possible serverroles such as "sysadmin" etc in SQL.
So, my question is, is there any way to load an SSIS package without being local admin on the machine? In case it is, what is needed?
regards Andreas
View 3 Replies
View Related
Mar 31, 2006
Hi everybody,
I have to create a package that executes other packages I've already created... Every one of these packages run with the same Configuration File, but if I try to execute the main one, including the path of the file, I get errors from the other packages because they can't find it... How can I manage to pass this file and its content to the other packages??
Here a little explanation of te process:
Main Package needs configuration file X.dtsConfig, and calls: package1, which needs configuration file X.dtsConfig; package2, .......
I hope everything is clear...
View 9 Replies
View Related
Nov 2, 2006
Hi,
I have a solution, and I have a few projects in this solution. Each project has a few packages in them. The problem/question I have is this: when I am working on one of the packages, I have only this package open. When I try to run/test this package, every single package in every project gets opened, recompiled/rebuilt before my current package gets to run. It's very frastrating and time consuming. Is there a way to somehow disable this weird behavior ? Is there a way to just build the package I am currently working on ?
Please, help.
Thanks,
Victor.
View 3 Replies
View Related
Nov 8, 2005
I need to create a package that will monitor a table in a source system and when a flag is set, load data from other tables in this source system to my destination system. Today this is accomplished with a SQL Agent job that executes every 15 minutes. If there is no work to do the job simply exits. I would like to create a SQLIS package that checks this control table every 30 seconds. Can I create a package that runs continuously?
View 16 Replies
View Related
