Running SSIS Package From Asp.net Application Failed
Aug 11, 2006
I 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!
View 1 Replies
ADVERTISEMENT
May 16, 2007
Hello,
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
View 2 Replies
View Related
Aug 11, 2006
Hello 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
View 7 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
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
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 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
Jul 6, 2006
i had scheduled to run a package it failed but a part of the package still shows up on the integration system running packages. i tried to stop it or delete it nothing works. when i try toi run the package even in visual studio that part of the package fails.
how do i remove the running package???
Thanks
jas
View 3 Replies
View Related
Dec 28, 2006
Hi guys..
I had created a Integration services project within my local system.All my packages are running fine.I added it to source control.Now i added this project from source control to another machine.It is failing to run...The path it is trying to execute is the location of the where i actually created my project.
How can i make it work.let me know
View 8 Replies
View Related
Jan 22, 2007
running ssis package with ssis run time compoenents and sql server 2000...
Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?
Can we just install runtime for ssis and run the packages ?
Please explian with links if possible
thanks a lot
View 18 Replies
View Related
Feb 28, 2008
Hi,
Is it possible to use an SSIS package in a .NET application without install sql server?
Thank you in advance
View 5 Replies
View Related
Jan 26, 2007
Hello, is it posssible to execute SSIS packages from ASP.NET ? Which code should I Use?
View 4 Replies
View Related
Mar 31, 2008
Hi 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 ?
View 9 Replies
View Related
Feb 26, 2008
Hi..
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??
View 1 Replies
View Related
Oct 3, 2007
Hello
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
View 4 Replies
View Related
Jul 3, 2007
I 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?
View 10 Replies
View Related
Nov 13, 2007
Hi 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!
View 8 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
Nov 8, 2006
I have an issue when a job is scheduled to run a SSIS package. The package (exporte a table to a text file) runs fine from microsoft visual studio but when i create a job and run it, i get the following error:
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'VOLCANOAdministrator', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
View 5 Replies
View Related
Dec 8, 2005
My boss want to close access to xp_cmdshell for any logins including sa.
View 1 Replies
View Related
Feb 27, 2008
Can a SSIS Package be run as a job or a maintance tool. I need it to run nightly. Also in that same job can sql queries be added as well.
Thank you
Dee
View 27 Replies
View Related
Jul 20, 2007
Hello 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
View 4 Replies
View Related
Apr 9, 2008
Hi i get a error when i run my SSIS package. Here is the message
Error: 0xC02020A1 at import file, Flat File Source [1]: Data conversion failed. The data conversion for column "su_supplier_code" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at import file, Flat File Source [1]: The "output column "su_supplier_code" (61)" failed because truncation occurred, and the truncation row disposition on "output column "su_supplier_code" (61)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
The funny thing about this is when i run the job a 2nd time it works fine.
Has any one any idea about this error or why the job would work fine the 2nd ?
View 1 Replies
View Related
Jun 18, 2008
Hello,
I'm new here and hope you will be able to help me.
I have created several SSIS packages with Visual Studio 2005.
They all work fine in debug mode.
I have been able to make them work with a ODBC connection by using a ADO.NET connection.
Then I exported them to the file system in my SQL Server 2005 database and created a task in SQLAgent to run them.
All the packages using the ODBC connection fail with the following error :
Login failed for user XXX
Error : 18456; Severity : 14 , State : 8
This error is a password mismatch.
I tried several database users and checked the passwords multiple times.
It looks like SQL Agent is not able to retrieve the password although it is stocked in both the ODBC connection and the SSIS connection.
Can you help me?
Thanks,
Ares
View 19 Replies
View Related
Mar 7, 2007
hi frnds,
i have SSIS package, i would like to schedule the SSIS package inorder to run automatically once in a day.
can anyone suggest me the solun. pls its urgent.
thnkx in adavance..
View 3 Replies
View Related
Apr 17, 2008
Hey guys I have a simple SSIS package up and running locally i.e i have Visual Studio 2005 and SQL Server 2005 installed.
Now If i wish to deply this SSIS package to a new server what do i require to be installed on the new server.
I.e SQL server 2005 will be there but will i also need Visual Studio 2005 to be installed ?
Also to actually run the package do i only need the .sln file
View 8 Replies
View Related
Jun 12, 2007
I am running into an error when running a package using a scheduled job under SQL Server Agent account. I am getting following error:
Date 6/12/2007 4:19:15 PM
Log Job History (VistaODSFeed)
Step ID 0
Server 006-DEVSQL2005
Job Name VistaODSFeed
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. Unable to determine if the owner (ACIsnasir) of job VistaODSFeed has server access (reason: Could not obtain information about Windows NT group/user 'ACIsnasir', error code 0x6ba. [SQLSTATE 42000] (Error 15404)).
ACIsnasir is not the account under which SQL Server Agent service runs. However ACIsnasir has sa priveleges. not sure why am I getting ACIsnasir in the error and not the account under which sql server agent runs.
ideas?
View 1 Replies
View Related
Sep 16, 2007
I run an ssis task with right click in the BIDS.
the task transform data of the tables between sql2005 DB to another sql2005 DB.
this is the error I get:
Code Snippet
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:
"Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:
"TCP Provider: The semaphore timeout period has expired.
".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Task failed: CopyTables2
i have a similar task, only diffrent tables that being transfered, same settings. this task run succesfully.
for the task that fails it takes a long time to run (something like half an hour, maybe more) and then it falils. the task that succeeds finish fast (something like 5 minutes). the data in the tables that being transeferred in the good task is much smaller than the data in the tables in the failed task.
Anyone knows the reason to the error.
Thanks.
View 14 Replies
View Related
Sep 5, 2007
I have a SSIS package that reads from a remote directory on another server that is on the same domain. When I run the package under my domain user, it runs fine, however when I try to start the SQL job which has the package as the first step, the job fails. I created a proxy account with domain user credentials, set it to be active under "SSIS Package Execution" and set the SSIS package to run as the proxy account, but I get the following error message:
"Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 2). The step failed."
I'm not sure what to do at this point, so any help would be appreciated.
View 8 Replies
View Related
Sep 10, 2007
Hello,
I created a ssis package to transfer data between tables of sql2005 DB to another sql2005 database.
when i run it from BIDS with right click -- > execute package i get this error:
Code Snippet
Error: 0xC002F325 at CopyTables1, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
anyone can help here.
Thanks.
View 4 Replies
View Related
Aug 4, 2006
I have a package that I want to run as a scheduled job in sql server 2005. The job runs fine in the studio. I am convinced that it is not a password or security issue as I set the protectionlevel to do not save sensitive data and can still run the job in the studio. The User running the job has SA privileges as does the SQL Server Agent. The job reads a flat file, runs it through a script and than inserts the rows into a table. The job runs successfully only if the flat file is empty. The job history says: The return value was unknown. The process exit code was -1073741795. The step failed.
There are no OnError lines in the logging.
Anyone have any Ideas or tricks to try.
View 9 Replies
View Related
Jul 9, 2007
When I run a newly created SSIS package remotely from my Vista Business PC, it runs an older version of the package, pointing to the wrong location that I initially configured in the flat file connection manager and I get errors, however when I run that same package from the server, it runs just fine.
Does anyone know what I need to do to clean that up?
thanks,
Anatoli
View 7 Replies
View Related
Mar 15, 2008
I have created an ssis package for importing some table data of one db to other db . Both taget and destination tables are exactly same . I was able to run the package sucussfully from BI Studio , that time the datas were exported currectly .
I created a an sqlserver agetn job with the package as step . I want it to run this once in a week .
When i tried to run this package , I am getting the following error message
Started: 1:20:07 PM
Error: 2008-03-15 13:31:33.91
Code: 0xC0202009
Source: Data Flow Task Destination 6 - ReservationManualStatus [456]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
End Error
Error: 2008-03-15 13:31:34.19
Code: 0xC020901C
Source: Data Flow Task Destination 6 - ReservationManualStatus [456]
Description: There was an error with input column "LastModified" (480) on input "Destination Input" (469). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
End Error
One thing is , this error message is not consistent all the time ,
I am getting the similar message only but from different tables making problem each time i run .
One thing common is , it alwayse pointing to date type fields.
Plese help me to get out of this problem
Thanks in advance
View 4 Replies
View Related
