Hi,
I would like to now how can I check if a specific package is running now, at the moment.
the reason I need it is because I enable in a web page to run ssis package using web servise. I would like to be sure that no more than 1 person is starting the package until it completes.
Does any one has any idea?
Iram
Hi,
I have two FormView, and I need to know if the DataSet contains specific fields to display it in the first FormView or in the Second FormView.
Is it possible?
In my application (infopath browser forms), First I get current user name thru System.Environment.UserName, after that, i want to check whether this user has access (read & write) permission to certain database, How do I do that?
One way I tried before is to get all the users and stored the names in an array for comparing in my program, but can not figure out the SQL command (to get the database users), if there it is, please kindly tell me.
Or if I can just pass the current user to check?
Thanks in advance.
I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.
Like this:
ID IS NULL
1 1
2 1
3 0
1 0
The expected ID i get as a result of my query should be 1 in this case.
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.
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
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?
If possible, in both platforms, 2000 and 2005.
---
http://www.ssisdude.blogspot.com/
Hello Gurus
I am using sql 2005 and one job status is executing in job monitor in 2005,How can i check since how long this job is running?
Please advice
Nitin
Hi,
I am skilled Oracle DBA and recently started to support MS SQL Server 2000
.
For all my Oracle DBs I run "Watch Dog" script that test if database ( server for MS SQL Server) is up and running and send me message on pager or by E-mail if it is down.
MY QUESTION IS:
How to do similar actions for MS SQL SERVER 2000?
Thanks in advance,
Michael Fradkov
Hi All,
I have a long running procedure (batch job) which will take quite some time to complete. I need to run a fixed number (configurable value) of copies simultaneously, i.e., either two copies or three copies at a time. (I don't want to use Service Broker for this purporse now)
For this I need to know whether this procedure is currenly being executed, and how many copies. Now I'm maintaing a flag in a table where this will get updated once the procedure starts and ends. This is not 100% reliable and need a manual check occasionally.
I would like to know, whether there is an easier method to find whether this procedure is being executed currently ?
Hope you are clear with my requirement.
SQL Server Version : 2005
Thanks in advance.
Regards
Babu
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.
I have a stored procedure that references another stored procedure on a linked server. I would like to check if the linked server is running prior to accessing the stored procedure. Everything I have tried returns either message 11 or 17 and terminates the stored procedure. I would like to do some cleanup before the termination. Is there any way to check this?
ben
Hi,
I have my SQL server installed and running on a windows 2003 server. Sometimes, the server stops unexpectedly. I want to know if there is any mechanism to alert as and when the SQL server or SQL server agent goes down.
I guess everything is a windows service. Can we have a mechanism to test this service from a script and send an alert mail when the server goes down? How can this be done from a script?
Thanks in advance
-Sudhakar
Hi,
How to check if ftp/sftp server is running before actually starting the ftp/sftp taks? I tried the script task as shown in the attached link but it does not really check for ftp connection.
http://blogs.conchango.com/jamiethomson/archive/2005/10/10/SSIS-Nugget_3A00_-Verify-a-data-source-before-using-it.aspx
Thanks
I am about designing an access project file that connect to a SQL Server database : it could be either on the local computer or a remote workstation, this depending on where the front end is running.
Bypassing the default log on dialog box i have set my own form that users should use to connect on adp. Then using baseconnection string i connect the adp to sql server database.
My concern is how can i check from vba if the instance of SQL Server where my back end database is located is running in order to make
users knowing that due sql server service status off their connection
tentative will fail.
inform u i can check same from console management however i would to automate same controle from my adp and avoid that users have to operate such task...
Hello friends I want to ask that by which command I can know about how many process are currently running on the server ?
Waiting for reply.......
I would like to know if it is possible to use dtexec to run a specific task in a package? If so how?
View 4 Replies View RelatedI need to start a job task from the command line. Does anyone know of the correct command(s) to do this?
I also need to check to see if a specific job task is currently executing from the command line. Does anyone know of the correct command(s) to do this?
Thanks for any assistance.
Jim
When i have multiple sql server instances running on one server, how to check the names of that instances with out connecting to sql server management studio.
View 12 Replies View RelatedHello all
I am trying to import an XLS to a table in my database. Sounds simple, but the import should be an UPDATE, not an INSERT. In other words, I want to transfer data from the XLS sheet, to specific field in my database.
Let me explain more clearly.
I have an XLS sheet with a manager ID and a employee ID. The table in the database is already populated with all employee ID's but the field manager ID is still empty. Every employee has a different manager.
The import should place the manager ID's in the fields where the employee ID is the same as in the XLS sheet.
If somebody could please help me out, thank you!
Best regards
Maarten
I need to be able to call a SSIS package with a specific parameter for one of the variables. Basically the package needs to be triggered from an application (.NET, C#) with a specific value.
The package is stored in the SQL Server (in Stored packages).
I have seen the article on dtexec and the SET flag, however we do not want to turn on xp_cmdshell on the SQL server due to the security implications, and that seems to be the only way to call dtexec
I have seen another article that says you can have a job invoke a package then have a specific user granted permission to the job, but that doesn't address how to set the parameter/variable for the package.
There are actually some other steps that technically need to run using the same parameter, so ideally if I could have a stored procedure call the SSIS package, then the subsequent toher stored procedures using the sent in parameter that would be great. I just can't figure out how to do this securely (as xp_cmdshell is not considered secure).
Thanks for any comments.
It's very easy to make a generic xml configuration file for the connection to the database for all packages my project contains. The connectionstring is the same for all these packages
Now I want to do the same for all flat file sources I have in my project where the connectionstring is not the same for all packages.
For example I use the following flat files in my project:
c:AAA.txt
c:BBB.txt
c:CCC.txt
If I move the packages to the production database the flat file sources are located in another directory.
So in fact for the flat file sources there is a generic part for all files (in this case 'c: ') and a specific part ('AAA.txt', 'BBB.txt' and 'CCC.txt').
Can I indicate this in the package configurations sections? And how?
Thanks,
John
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?
Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...
View 2 Replies View RelatedI have a ssis package which runs daily. This ssis package has couple of execute sql tasks which load data for yesterday's transaction. Ex.
INSERT INTO Shipped (Div_Code, shipment_value, ship_l_id, shipped_qty, shipped_date, whse_code,
ord_id, ship_id, ship_l_ord_l_id, Created_date) select ord.DIV_CODE as div_code, ship.SHIPMENT_VALUE as shipment_value, ship_l.SHIP_L_ID as ship_l_id, ship_l.SHIPPED_QTY as shipped_qty, ship.SHIPPED_DATE as shipped_date, ship.WHSE_CODE as whse_code, ord.ORD_ID as ord_id, ship.SHIP_ID as ship_id, ship_l.ord_l_id as ship_l_ord_l_id, Getdate() as Created_date from SHIP ship, ORD ord, SHIP_L ship_l where ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))) and ship.WHSE_CODE='WPP' and ord.ORD_ID=ship.ORD_ID and ship.SHIP_ID=ship_l.SHIP_ID
All execute sql task has query like above query. and in some query we have date filter which loads data for yesterday. Ex. one query has ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). some other query has ord.trans_date=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). this package runs daily through sql server job, so It loads data for yesterday. Now If i want to run for any particular date, How could we achieve from ssis?
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
I have scheduled an SQL Job to run every 15 mins. This runs an SSIS package. But sometimes my package might execute for more than 15 mins. In this case, a second instance of the package would start. I don't want this to happen. So is there any way I can check whether the package is already running before I continue the execution of the same. Right now I am using a mutex to handle this problem. Are there any problems using the mutex and is there a better way to handle this?
Thanks in advance.
Sumesh
Hi~~.
In client side, the sqlncli is needed.
Below is the script of installing sqlncli.msi package.
msiexec /i sqlncli.msi /quiet /norestart /le C:sqlncli.log
Question is, how can I check the installation?
If it is already installed skip the installing process.
If not install the package.
hi,
i'm total newbee on SSIS packages and therefore need guidance.
I want to make a ssis package that (in order):
- check in table (tbl_orders) if there is any new order made
- if new order is made, update column (time_last_change)
- if this order has geography ID (ID_geography) inserted, insert name of geography.
Thank you in advance,
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 RelatedHi
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
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);
}