Trigger DTS Package Thr' Asp.net Application
Jun 17, 2005I am newbie to DTS stuff. Is there anyway I can trigger DTS package thr' asp.net applicationPlease help
View 1 Replies
ADVERTISEMENT
Run Dts Package From Visual Studio(user Will Trigger The Running Of Dts Package)
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?
SQL Trigger To Notify VB.NET Application ?
Aug 10, 2007Hi all,
I have a VB.NET 2005 application that needs to monitor constantly each change of a field on a SQL server 2005.
Is there a way of setting a trigger up in the SQL Server that notifies the VB.NET application to avoid having the VB.NET apllication querying the SQL table all day for changes?
The table in SQL stores a temperature value. I need the VB.NET application to receive this value everytime there's a change. I could do a timer on the VB.NET application to check for changes in the table every 1 second but I don't think this is efficient. The temperature value may change once every two or three seconds.
The VB.NET application will process the temperature value.
Is there a way of notifying the VB.NET application (which runs from a different server) from within SQL Server? If so, how? If not, what would you go about implementing this?
Thanks again for your comments,
ST
Application Variable In A CLR Trigger
Apr 30, 2008Is it possible to get a variable( for example user id of the currently logged in user) in a CLR SQL Server trigger.
View 1 Replies View RelatedTrigger Works But Hangs Application
Apr 30, 2001I have a trigger on a table which when I do a direct update to throught QA seems to work fine. When a user developed application updates the record it locks their screen and record. I can't see where it could be causing it, but I am pasting it below. Any thoughts/comments greatly appreciated
CREATE TRIGGER tr_Patient_Updt2
ON patient_
FOR UPDATE
AS
if update(df_last_cholestero)
begin
declare @tstyears int
declare @tdate datetime
declare @sdate datetime
declare @bdate datetime
declare @ptage int
declare @actid varchar(12)
select @bdate = convert(datetime,df_last_cholestero,101), @actid = account_id
from inserted
select @ptage = dbo.GetAge(convert(datetime,patient.Date_Of_Birth, 101), GETDATE())
from patient where patient.account_id = @actid
select @sdate = convert(datetime,patient.Date_Of_Birth,101)
from patient where patient.account_id = @actid
select @tstyears = datediff(month,@bdate,getdate())
if @ptage between 18 and 65
begin transaction
begin
if @tstyears < 12
begin
set @ptage = @ptage + 5
set @sdate = DATEADD(year, @ptage, @sdate)
update patient_
set df_next_cholestero = convert(char(08),@sdate,112)
where patient_.account_id = @actid
end
else if @tstyears >= 12 and @tstyears < 24
begin
set @ptage = @ptage + 4
set @sdate = DATEADD(year, @ptage, @sdate)
update patient_
set df_next_cholestero = convert(char(08),@sdate,112)
where patient_.account_id = @actid
end
else if @tstyears >= 24 and @tstyears < 36
begin
set @ptage = @ptage + 3
set @sdate = DATEADD(year, @ptage, @sdate)
update patient_
set df_next_cholestero = convert(char(08),@sdate,112)
where patient_.account_id = @actid
end
else if @tstyears >= 36 and @tstyears < 48
begin
set @ptage = @ptage + 2
set @sdate = DATEADD(year, @ptage, @sdate)
update patient_
set df_next_cholestero = convert(char(08),@sdate,112)
where patient_.account_id = @actid
end
if @tstyears > 48
begin
set @ptage = @ptage + 1
set @sdate = DATEADD(year, @ptage, @sdate)
update patient_
set df_next_cholestero = convert(char(08),@sdate,112)
where patient_.account_id = @actid
end
end
end
commit transaction
What Code Is Required To Use The Trigger With The Application Of .NET && SQL 2005
Apr 22, 2007Hi Everyone, I m using ASP.NET 2005 with C# and SQL SERVER 2005. I m using stored procedure and sql datasource control to retrieve the data. I want to use the trigger alongwith storedprocedure. I have created the following trigger on emp_table. CREATE TRIGGER Employeee on emp_tableAFTER DELETE ASDECLARE @empid int SELECT @empid=empid from emp_table where locationid=(SELECT locationid from deleted)IF @@ROWCOUNT=0 DELETE FROM location_table where locationid=(SELECT locationid from deleted)What i wanted to know is how can i use it with asp.net and sql server 2005 when any update,delete or insert query is executed thru the stoed procedure...What code is required to use the trigger with the application of .NET thanxs.....
View 2 Replies View RelatedTransact SQL :: Trigger Execution With OLEDB Connection From In-house Application
Oct 28, 2015issues with triggers in Sql Server 2014.
A few weeks ago I've done a SQL Server migration from SQL Server 2000 to SQL Server 2014.It was a bit tricky but anything worked fine.
I have some legacy VB6 (Visual Basic 6) applications written in house which worked with Databases on the old SQL server 2000.Surprisingly, these applications worked well after the upgrade to SQL Server 2014 without having to change a piece of code.
Now, some users tell me that they receive some unusual message when saving data from these legacy applications.After investing for a few hours, I discovered that triggers are not executed when those users try to save data from grids or forms in their applications.Trying to reproduce the INSERT statement in SQL Server Management Studio, the triggers run well.From the application, they don't.
These applications connect to Database Server thru OLEDB connection with the following ADO connection string :
Provider=SQLOLEDB.1;Password={password};User ID={user};Initial Catalog={db};Data Source={datasource}.the {user} is a true SQL account who have read/write/delete access in the databases.
On the web there is a lot of questions involving the same issue, but only from SSIS.I found some articles about an OLEDB connection parameter named FastLoadOptions with a value of FIRE_TRIGGERS, but nowhere how to put it in the ADO OLEDB connection string.
how to reactivate the "normal" use of triggers from an ADO OLEDB connection ?Either with some obscur parameter in the connection string or options somewhere in the SQL Server 2014.
DTS Package :MMC.exe - Application Error
Feb 13, 2007Hi when i open my DTS package on DTS design , and when i go through different connection and test connection it successfully , when i cancell the step properties i got the error below and thw whole enteprise manager closes, i do not have ant problem with other dts packages except two of them which keep giving me the error below any suggestions
MMC.exe - Application Error
'The instruction @ "0x10001919" referenced memory @ "0x00000004." The memory could not be "read
How To Save DTS Package As An Application
Sep 29, 2005Hi,I want create stand alone application from DTS package. I remember Idid one time ago, I couldnt recall how I did it.Thanks.
View 4 Replies View RelatedSSIS Package In A .NET Application
Feb 28, 2008Hi,
Is it possible to use an SSIS package in a .NET application without install sql server?
Thank you in advance
Execute SSIS Package From ASP.net 2 Application?
Jan 26, 2007Hello, is it posssible to execute SSIS packages from ASP.NET ? Which code should I Use?
View 4 Replies View RelatedCannot Execute SSIS Package From Asp.net 2.0 Application
Mar 31, 2008Hi everybody,
I have the following method and it works well on my development workstation ;
Code Snippet
private DataTable ilaclariGetir(string dataReaderDestination) {
string dtexecArgs;
DtsConnection dtsConnection;
DtsCommand dtsCommand;
IDataReader dataReader;
DtsDataParameter dtsParameter;
DataTable dataTable = new DataTable();
dtexecArgs = @"/FILE ""C:TempEczaTakipProjessisPaketleriPackage.dtsx""";
dtsConnection = new DtsConnection();
{
dtsConnection.ConnectionString = dtexecArgs;
dtsConnection.Open();
}
dtsCommand = new DtsCommand(dtsConnection);
dtsCommand.CommandText = dataReaderDestination;
dataReader = dtsCommand.ExecuteReader(CommandBehavior.Default);
// IDataReader 'ın buradaki gerçekleştirimi diğer dataReader 'lardan farklı. Kayıtlar okunurken son kayıt okunmasına rağmen
// read() metodu bir kez daha true dönüyor ve kayıt sayısı + 1 tane okuma yapılıyor. Yapılan son okuma da istisna fırlatıyor.
try
{
dataTable.Load(dataReader);
}
catch (ApplicationException ae)
{
}
dtsCommand.Dispose();
dataReader.Close();
try
{
dtsConnection.Close();
}
catch (Exception ex)
{
}
return dataTable;
}
However it always fails after I publish the asp.net 2.0 application to our Win 2003 Server. I also copied the configuration and dtsx file to proper location on the server.
I can use dtexec to execute the package without any problem on the same location.
But when called from the application, the package fails to validate.
Is it about a user- permission problem ?
Execute SSIS Package From .net Application
Feb 26, 2008Hi..
I have Created an SSIS Package to Backup a database..
I want to execute that SSIS package from a .net application
How can i do this??
Calling A SSIS-Package From A COM+-Application
Oct 3, 2007Hello
I have a SSIS-Package stored in the SQL Server. This package works fine when i execute it from the SQL Server. But if I try to execute the package from a COM+-Application, nothing happens.
Here is my code snipped:
Public Function ExecutePackage €¦
Dim myPackage As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
'----- Execute the package from SQL Server
myPackage = app.LoadFromSqlServer("\PackageName", "ServerInstance", Nothing, Nothing, Nothing)
pkgResults = myPackage.Execute()
€¦
End Function
In a Windows Forms Application I can execute the package with this code snipped succesfully. Therefor I think that the problem is my COM+-Application.
Can anybody help me?
Thank's
Jürgen Paulus
How To Execute A SSIS Dtsx Package From An Asp.net 2.0 Application?
Jul 3, 2007I have a SSIS package that I want users to be able to execute by clicking a button on an a web page. The package does not require any parameters to be passed to it. Previously I've executed DTS packages without any problems but after a fair bit of investigation and trawling the net I've not found a way to do this successfully with SSIS. Some code I've tried - Dim app As New Application()
'
' Load package from file system
'
Dim package As Package = app.LoadPackage("c:ssisPackage.dtsx", Nothing)
'package.ImportConfigurationFile("c:ExamplePackage.dtsConfig")
'Dim vars As Variables = package.Variables
'vars("MyVariable").Value = "value from c#"
Dim result As DTSExecResult = package.Execute()
lblResult.Text = "Package Execution results: {0} " & result.ToString()
All I get is a message 'Failure'.
Does anyone have an example of how to do this?
Pass Parameters Into SSIS Package From A Web Application
Nov 13, 2007Hi all,
I wish to pass parameter from a dropdownlist in my web application to the SSIS package so as to export the user specified table.
Is using package configuration the right way to do this?
Any advice is appreciated!
Running SSIS Package From Asp.net Application Failed
Aug 11, 2006I have an asp.net web application and a web service (both of them are created in VS 2005 - asp.net 2.0). They are located on the same web server. In both web.config files, I have set <authentication mode="Windows"/> and <identity impersonate="true"/>. Also, configured the IIS settings to use Integrated Windows Authentication and unchecked the Anonymous access (for both). The web service is called from the web app, so I have to pass credentials for authentication to the web service. The web service loads and executes a SSIS package. The package and all the other sql objects are located in the sql server 2005 (windows server 2003 - the same server as the web server).
When run the web service from develop environment (vs. 2005), I get whatever I expected. When call it from web application, however, the package failed (no error message).
In the SSIS package, there are three connection managers €“
· A: Microsoft OLE DB Provider for Analysis Services 9.0 Ã connectionType=OleDbConnection
· B: .Net Providers SqlClient Data Provider à connection type=SqlConnection
· C: Native OLE DB Microsoft OLE DB Provider for SQL Server à connectionType=OLEDB
After ran the web application and check the sql database, I can tell that the package was reached and when through the first two steps (clear some records in table_1 and extract some records from table_2 ) which relate to the connection manager B €“ ADO.Net connection. The remaining steps failed which are related to the connection managers A & C.
From SSIS package log file, found that the user credentials (domain and username) were correctly passed from web service to sql server 2005 at the first two events, but the credentials (or operator) changed from domainABCuser123 to NT AUTHORITYNETWORK SERVICE after packageStart. Then, it complains €¦ either the user, domainABCserverName$, does not have access to the database, or the database does not exist.
I think the credentials are passed ok but some setting related to the Analysis services are not correct - complaining start from there. Any clues?
Please help and thank you all!
Trouble Running Application In SSIS Package
May 16, 2007Hello,
Hopefully someone can help. I am not new to SQL Server, but am new to SSIS packages. As one step of my package I need to open and run a windows application. No problem there right? But, my problem is that I cannot get the "focus" returned back to the SSIS package. It doesnt move on to the next step until I close the windows application. It is like the package is the parent object waiting for a "finished" message from the child object, and it doesn't receive that message until the child object is closed. I've tried using a batch file to run the app and calling the batch file from an Execute Process Task, but that doesn't move onto the next step until the dos command prompt window closes and I can find a way to automatically close it from XP. I also can't find a way to kick off this windows app from a script task using command line. Not sure if I would run into the same problem going that route or not. If anyone can help, i would appreciate it.
Thanks
JK
DACfx Application - Could Not Load Package From Dacpac
Jul 21, 2015UPDATE: Using Visual Studio 2012 and SSDT 11.1.50512.0
I am using this code to load a dacpac and get the version number from it:
using (DacPackage dacpac = DacPackage.Load(ADacpacFile))
{
retVal = dacpac.Version.ToString();
}
After I install the application on a test machine, this code executes properly and returns the version number on the first run of the application. I can execute the code multiple times while running and retrieve the version number.Â
Once I exit the application and then reload it, the above code fails with the non-descript error message "Could not load package from 'filename.dacpac'. Stack trace shows: at Microsoft.SqlServer.Dac.DacPackage.Load(String filename, DacSchemaModelStorageType modelStorageType, FileAccess packageAccess).
I have tried rebooting. I have tried manually copying over the dacpac files from the install source. I get the same error every time. The only thing that works is uninstalling the application and reinstalling it. Then it works for the first execution and fails again afterwards.Â
On my development box, it works every time without error. Only when I deploy the application via install does this happen. I have tried two different boxes (both Win7 64bit) with the same result. The dll's that are being installed with the application are:
Microsoft.Data.Tools.Schema.Sql.dll
Microsoft.Data.Tools.Utilities.dll
Microsoft.SqlServer.Dac.dll
Microsoft.SqlServer.TransactSql.dll
Microsoft.SqlServer.TransactSql.ScriptDom.dll
How To Get Application Path In Integration Services Package?
Apr 13, 2007We have a main package which is calling multiple other packages. The the child packages uses parent package variables and which we are configuring using configuration file while installation of the package. Here we need to set each path while installation because there are many variables using the same path with the different file names(Different files stored in same Folder).
We want to get the main path and then if we use relative path in other variables then we need not to assign all the variable paths while installation.
How to we can get the Application path in Integration Services Package(Script Task)?
Thanks in Advance!
SSIS Package Execution Failed Within ASP.NET Web Application
Aug 11, 2006Hello All,
I have a SSIS package which run well when stand-alone. However
it was failed when executed from .NET web application. It just simply return Failure. Code snip below:
Dim packagekgResults As DTSExecResult
.........
packagekgResults = package.Execute()
Environment: Windows 2003 Shared Server, IIS 6.0
Any idea?
Thanks in advance!
Tina
How To Make Application.LoadPackage() And Package.Execute() To Run Asynchroniously?
Aug 10, 2006Hi,
I am trying to execute a SSIS package programmatically. When a user drops a file in a shared folder, we execute the package based on that file. I am using SqlServer.DTs.Runtime.Application.LoadPackage() and SqlServer.DTs.Runtime.Package.Execute() functions each time to do this.
The problem is, when, say two people drop a file, the second one will not execute untill the first one is completed. I also tried only calling LoadPackage() a single time, and then storing the instance and calling Execute() in a different thread on each file drop; although the blocking still occurs. I assume the Package object is the one doing the blocking then behind the scenes.
Is there any built in functionality to make these functions (Application.LoadPackage() and Package.Execute()) run async? If not, has anyone had much success sticking these calls into a thread? I tried sticking these calls into a thread using System.Threading.ThreadPool.QueueUserWorkItem(), and this appears to work, however it randomly crashes the program when I drop multiple files one after the other. The exception also isn't too helpfull (pasted below):
"The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails."
So, is what I am trying to do possible? I know it must be, because when I was using .NET 1.X, I was just calling dtexec via the command line, and dtexec was able to execute many packages simultaneously....
Thanks for any help,
Drew
SSIS Package Permissions (Windows Application Log) Question
Jul 20, 2007Hello all,
I have an issue where the DBAs have informed my group that they need to get to a model where an SSIS package which presently needs Admin perms on a box in order to write to the Windows Application Log, no longer needs those perms to write to the log.
I am new to SSIS packages, though familiar with DTS packages (and ETL), so I'm wondering if their concerns (1) can be verified; and (2) if so, is there a better way to allow the package to write to the log without Admin perms (on the box). At the moment, the preference would be to do so without using .Net, in an effort to keep the implementation simple.
Scenario: A single package is scheduled to run at a predetermined time. Once complete it writes to the Windows Application Log.
Thanks in advance,
Henry
Run The Package Using Trigger
Jul 9, 2007Hi ALL,
How 2 run a DTS pkg automatically based on a table value
For ex:
Table T(has only 1 field) gets value '101'(count==1 or n) based on a trigger...Further, the package P shud run automatically, advantage here is the table gets truncated automatically through some .NET application. So, again whenever the table T gets the value(s), the pkg P shud run without any manual work
Please let me know the solution, Thanx in advance
Integration Services :: Getting Error While Execute SSIS Package From Console Application
Apr 23, 2015SSIS package working fine directly.I got following error while execute SSIS package from C# console application.
The connection "{79D920D4-9229-46CA-9018-235B711F04D9}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Cannot find the connection manager with ID "{79D920D4-9229-46CA-9018-235B711F04D9}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "OLE DB Destination.Connections[OleDbConnection]" in the connection manager collection of "OLE DB Destination".
Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.
OLE DB Destination failed validation and returned error code 0xC004800B. One or more component failed validation. There were errors during task validation.
Code :Â
  public static string RunDTSPackage()
    {
      Package pkg;
      Application app;
      DTSExecResult pkgResults;
      Variables vars;
      app = new Application();
      pkg = app.LoadPackage(@"D:WORKPackage.dtsx", null);
   Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = pkg.Execute();
}
I have recreate the application with again new connection in SSIS. Still not working.
DB : SQL Server 2008 R2
Can A Trigger Start A DTS Package Process?
May 26, 2001I expect to create a trigger to post updated data from GoldMine hosted in MS-SQL to my migration MS-SQL database in the appropriate tables mirroring the destination PICK data tables.
Then, start an ActiveX DTS package to migrate the data via a PICK DSN to data tables in a PICK database.
Currently the dba has been able to use VB6.0 with ADO to push data into PICK. He also was able to do similar using MS-Access.
However, PICK (RainingData) is of the opinion that he must script a PICK server side Basic (RealBasic) insert script to receive the data from a VB6.0 application triggered by MS-SQL.
I think that I could skip the Basic script and go direct with ADO in DTS as he has before with VB6.0 with a user form.
Can I have the trigger start the DTS or should I just schedule it to run as often as necessary to update the PICK database?
FYI, this is a one-way data flow into PICK.
TIA
Anyone within the L.A. CA area that has experience with PICK and MS-SQL can get some well paid consulting hours. I'm just the GoldMine GMT whose been enlisted to get the job done, but would appreciate an expert with PICK to join the project.
jEfFp...
Trigger A SSIS Package On Its Completion...
Nov 19, 2007Is there a way to trigger a SSIS package automatically when it ends successfully. Actually i am moving data from one database to other.......i am passing start date and end date as my paramter...So is there some way once my SSIS package ends succesfully then it can start again on its own reading a new set of date range from a file or something....
I appreciate any reposne in advance
Thanks...
Calling An SSIS Package From A Trigger Causes It To Run For Ever
Oct 26, 2007hi,
when calling an SSIS package from a trigger causes it to run for ever. Know why??
In Detail;
Consider that there are two tables with the same schema in a db. Lets name that Test1 and Test2.
I develope a package whihc will transform data from Test1 to Test2. I initiate this package from a Trigger for Insert on Test1. For eg.
CREATE TRIGGER Trigger_Test1
ON Test1
AFTER INSERT
AS
BEGIN
EXEC xp_cmdshell 'dtexec /FILE "C:TestTestPackage1.dtsx"'
END
This runs for ever when a record is inserted into the Test1 table.
But, when the trigger is on someother table , everything works fine.For eg, if the trigger is on the table TT1 & this trigger initiates the same package while inserting a record into TT1, everything is fine.
Can anyone help me on this.
Thanks
Man
How To Create A Trigger Such That It Can Delete The Rows Whenever Any Other Application Such As Biztalk Had Read The Rows ?
Mar 12, 2007I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?
View 3 Replies View RelatedImporting Data In Datatable Using SSIS Package Trigger On Insert Is Not Firing On That Table
Oct 6, 2007Hi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP
Integration Services :: Trigger SSIS Package Whenever New CSV File Gets Added To Shared Location
Jul 10, 2015I have an ssis package that moves data from a new csv file in a share location to sql server database table. However I need to get this agent job triggered whenever a new csv file gets added to the shared location.
What is a best strategy to do this keeping in mind that while package is running and two new csv files come in and package shd copy data from both the files.
Package Execution Fails In Windows Service But Runs Fine As A Windows Application.
Jun 23, 2006I am attempting to write a Windows service that watches a database for uploaded files to import. When a new file is found, the corresponding SSIS package is run from the file system with variables passed through. I started development as a Windows app and copied the functionality to a service.
The app runs fine. The service does not. I get a "Failure" each time a package is executed. Everything is identical behind the scenes with the obvious exceptions that OnStart and OnStop handlers are buttons in the app. I added a script task at the beginning of one of the SSIS packages to notify me that it is even running at all. It doesn't even hit that initial task.
Again, the app will run all packages just fine. The data is imported and the results return as "Success."
The following is the code executing the package. Any help is appreciated. I've been banging my head on this one for a few days now. (Is there a tag to format a code sample?)
Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
pkgLocation = sPackageFolder & PackageName & ".dtsx"
pkg = app.LoadPackage(pkgLocation, Nothing)
Dim vars As Variables = pkg.Variables
vars("ImportId").Value = ImportId
vars("ProductionServer").Value = ProductionServer
vars("ProductionDatabase").Value = ProductionDatabase
vars("SourceFileName").Value = FileName
vars("SourceFilePath").Value = FilePath
pkgResults = pkg.Execute()
Distrib.exe Application Error , Application Failed To Initialize Properly(0xx0000142)
Apr 13, 2008have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.)
Replication past two months working fine, Now
1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber).
Iam attaching thw file.
Thanks
Sandeep