Hello,
is there any way to execute a SSIS package at the SQL Server programatically from a remote computer? (I use .net 2)
I don't need variables or return values, I only want to start the package. I know that it is easier to do that job with a webservice etc on the SQL Server but I cannot use such.
A method to call a package from a stored procedure would also help me a lot
Kind regards, _Rodney_
Do I need admin access on my computer to Run or manage an SSIS Package?
if i dont have admin access on my computer, what functionality of SSIS i cannot use?
Hi. Is it possible to call an executable that resides on another server from an SSIS package? If that's not possible, is it possible to call an EXE that resides on the SQL Server box itself? Thank you
View 3 Replies View RelatedI have an SSIS package stored on the file system and I want to execute it from a Visual Studio 2005 VB project. I have searched the internet for examples and have been unable to find any examples. I gather that I will be using SQLSMO however I'm at a loss on how to do this task. Any help is appreciated! Thanks.
Karen
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.
Hi All,
I need your help to call an SSIS package using ASP.NET. I have deployed the package using SQL server deployment with an windows authentication. I need to call that package from a remote machine. Also i need to assign a value for a variable in that package while calling it from ASP.NET.
Plz let me know the steps to call that package from ASP.NET
Thanks
I am having few queries related to follow.
1. Is it possible to call SSIS package from procedure .
2. Also want to supply parameters to procedure.
3. Can multiple users execute that procedure simultaneously.
4. If yes then will it cause any issue if it is run simultaneously by 10 users.
Hi, Everyone:
I am getting the following error message when I try to execute a SSIS package from an asp.net page written in C# 2.0. What I am trying to do is basically just click on a button in the web page and it will execute the package. The code to execute the package is pretty simple. I pass the path of the DTS package stored in the local folder. This works fine in the machine where SQL2005 is installed locally. But it fail when I have a seperate Web Server and SQL Server. Any ideas? Do I have to install SSIS or SQL2005 on a web server as well?
Thanks
static public string Execute_SSIS_DTS(string DTS_Path)
{
Microsoft.SqlServer.Dts.Runtime.Application app;
app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package package = app.LoadPackage(DTS_Path, null);
DTSExecResult result = package.Execute();
return result.ToString();
}
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.
Line 226: static public string Execute_SSIS_DTS(string DTS_Path)
Line 227: {
Line 228: Application app = new Application();
Line 229: Package package = app.LoadPackage(DTS_Path, null);
Line 230: DTSExecResult result = package.Execute();
[COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +43
[DtsPipelineException: Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +169
Utilities.Execute_SSIS_DTS(String DTS_Path) in c:InetpubwwwrootMasterTablesApp_CodeUtilities.cs:228
MasterTables_Admin_MasterTables_LOINC_External.btn_SyncLISTest_Click(Object sender, EventArgs e) in c:InetpubwwwrootMasterTablesMasterTables_CustomMasterTablesCustom_LOINC_External.aspx.cs:98
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +114
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +141
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3215
I have ssis package which is credated by VS-2010.
I want execute this SSIS package from the stored procedure (SQL server 2005).
How do we connect and get data from SAP database using SSIS 2012/2014.Currently we are extracting data using WebService, and BAPI function call for RFC, but it seems not so much reliable, due to timeout and others. ion providing the link for download of .NET Framework Data Provider for mySAP Business Suite and some sample to invoke and get the data using SSIS.
View 5 Replies View Related I am new to the SSIS.
For DTS package of sql server 2000, I can call a DTS package from stored procedure. The command is:
dtsrun /E /SMyServer /NMyDTS /Wtrue /A Parameter1:3= 'Test'
Does anyone know, how do I do the similar thing from SSIS environment.
1) How to call a SSIS package from Stored Procedure?
2) How do I pass parameter to the SSIS package?
Thanks everyone.
Hello,
I have a bundling package that runs about 20 other packages. It has been working fine for a while but a couple of days ago it fail with the following message,
Error 0x800706BE while loading package file "D:PackagesToradSales.dtsx". The remote procedure call failed.
I´m running the SSIS packages in an 64-bit environment.
Thankful for help with this!
//Patrick
hi all,
thanks for the valuable information all the time!!! saved me a lot of time...
our team developes a system for text data improvement using ssis .
we have a few heavy packages that we want to execute on two separate "SSIS servers" that will be dedicated to runnung these packages only, and repeatedly. the main sql server will be placed on a different server machine .
my question is:
what is the best way to do this?
if we schedule these packages as a job of the Sql Ajent- does that mean that the packages will be executed on the sql server machine (which is not what we want) ? or could we define a remote machine to run the package on, and specify our "SSIS servers"?
or- should we use a simple scedualer on the "SSIS servers" using a dtexec command? but then i loose the benefits in using the sql agent- such as logging, notifications, etc.
do we need to install sqlserver on these "ssis servers"?
thanks for your replies!
OS: Windows 2012, standard, 64-bit
SQL: 2012 + SP1 + CU2 = 11.0.3339
We recently decided to "break apart" our BI environment. We used to have everything on one box, DB Engine, SSIS, SSAS & SSRS. Everything has been running fine, but we now have other projects using these services, so we decided to break them apart into their own boxes.
We now have DB Engine on one Server, SSIS & SSRS on another server and SSAS on yet another server, so we now have three boxes that replaced one box. All are Windows 2012, standard, 64-bit with SQL Server 2012 + SP1 + CU2.
Since some of our SSIS packages have to access external resources, we used a domain account for it's service account. The DB Engine and SSAS box are using the default service accounts when installed. I can execute the packages fine on the SSIS server, I can even execute them via SQL Agent jobs on the SSIS box (we did install a default instance of SQL on the SSIS box), however when I try to execute a package from my laptop, it fails with the ugly "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'".
I immediately double checked my SPNs and they all looked correct for the SSIS server and the service account we are using (and we had no duplicates). I also double checked the User Rights Assignment in the Local policy editor and all the correct Rights have been assigned (Log on as a service, Bypass traverse checking, Impersonate a client after authentication).
Hi,
Is that possible to execute SSIS packages from a remote SQL Server 2005 in another domain at local computer (Windows XP)?
Can I use SQL authentication to connect to the remote SSIS? My problem is, the user who execute the SSIS is not allow to access to SQL server directly. Is that possible to code everything in a batch file instead?
I tried to use web services to execute the remote SSIS but it always return "authentication error"?
Can someone help?
Many thanks!
Regards,
Mashmallow
Hi everyone!
I have a question regarding ASP.NET 2.0 reading available packages (calling "GetDtsServerPackageInfos" on the "Application"-Object which returns "Microsoft.SqlServer.Dts.Runtime.PackageInfos") from a remote SSIS server (not running them). It seems to me that I am experiencing either a security/delegation issue or that I am trying to do something that is not possible...
The setup is the following.
I have two machines, A and B. A runs IIS 6/ASP.NET 2.0 and B runs SQL Server and SSIS. They belong to the same domain. IIS runs the application pool with the webapplication in question under the "Network Service" account and A is trusted for delegation to B. At the moment its trusted when using Kerberos and for MSSQLSrvc and rpcss; but I tried "all" and many more as well.
I configured IE to use Windows Authentication and set the URL to belog to the Intranet (to enable kerberos authentication).
My ASP.NET webapplication now tries to read (in the sense explained above) the available packages on the SSIS machine (B). The packages are stored on the filesystem, not in mdsb. But I feel that this does not matter, since ASP.NET is accessing them through "the SSIS service" which can access packages in msdb or/and on the filesystem?
What happens now is that
a) everything works, when I fire up IE on A and access the webapplication. The user I have on A is a local admin.
b) I get "E_ACCESSDENIED" when I start IE as regular User on A
c) I get "E_ACCESSDENIED" when I access the webapplication from any other machine than A (IE6 or IE7)
My main question is: is it possible to do what I want to do? The other question is, why does everything work when I access the webapplication from A, but not from any other machine? The second thing makes me think it could be a delegation issue (double hop and SSIS don't seem to go togethe too well). But still, I could not find any resource telling me that this is impossible And frankly, I do not fully understand delegation and/or Kerberos.
I would greatly appreciate if anybody had a pointer or something for me.
Regards
scherand
Btw: Thanks to Caddre for his (her?) first help and pointing me to this forum.
Hi,
I have created a SSIS package on my local machine.
I need to deploy this package on a Test Server and it should execute as a scheduled job.
I am not sure of the steps I need to follow and things I need to be careful about.
I have the following doubts:
1) While developing the package, I have referred to the local SQL Server with Windows credentials.
2) The package, once deployed on the Test Server needs to point to the test server database with SQL Server credentials.
3) I know how to create a deployment utility. However, I am not sure if the deployment manifest will run without errors on the remote machine.
4) Even if the deployment manifest executes successfully and deploys the packages, how do I generate a script to create a Job and schedule the package?
5) I am using a File DSN to connect to IBM DB2 database in one of my Data Flow Tasks in this package. How do I configure it automatically on the remote machine? Won't there be any issues with it?
Please help me...
Thanks and Regards,
B@ns
Hi,
i created a SSIS package to import data to a remote sql server, using the following connection string:
"Data Source=My-SQL;User ID=PortalUser;Password=Password;Initial Catalog=TestOMIWarehouse;Provider=SQLNCLI.1;Auto Translate=True;";
and got an error "Login failed".
When i give PortalUser a sysadmin server role, it works. But i don't want to give PortalUser a sysadmin role. Any suggestions? Is sysadmin role required to access a remote server using Sql server authentication?
thanks! Any help would be appreciated.
alea
I am developing a package to restore a database from backup file on a remote server. I am having problems accessing the remote backup file when it is addressed via the admin share, in this case N$. It runs okay if a specific share is created but for some unknown reason fails via the adminshare.
I am executing the package job with a proxy account that is a member of the local administrators group on the remote server.
It appears that access via a remote admin share isn't possible from within a SSIS package. Is this the case?
I have written a VB component to execute a SSIS package stored in SQL server.
I am using the Microsoft.sqlserver.dts.dtsclient, dtsconnection/dtscommand
and executereader and everything works perfect on the local machine. This is descibed in a msdn site .
But my customer have a remote SQL server and want no extra BI clients/SQL server clients etc on the client machine, only an ordinary windows client and network connection to the SQL server.
My question is: Can you still use dtsclient or do you have to do in some other way?
rose-marie
Hi,
I have installed SQL SERVER 2005 on my network machine.
How do I connect the DB from my machine.
From the Microsoft SQL server DSN CONFIGURATION PAGE on my machine,
I put in the name of my network PC and the SQLEXPRESS .
But connection FAILS.
The Server Name on my Network PC : RCH1SQLEXPRESS
The Full computer name is RCH1
So in my DSN CONFIGURATION PAGE on my machine,
I enetered as RCH1SQLEXPRESS
What am I doing wrong?
Hi
How can i make backup on the other computer.
1. SQL computer is express edition, and computers are in workgroup?
2. SQL computer is express edition and is not on domain, and backup computer is on domain.
thanks
alex
Hi All,
I created SP and enabled db_datareader and db_datawriter for roles for BUILTINUsers on database level.
I can call that SP from my application if I am accessing DB from my app running on the same machine. If I run app on other machine SP throw exception related to lack of permission.
I fixed that by enabling db_owner roles for BUILTINUsers. But it is not good fix from security point of view.
My question is: is there any other way to allow regular user to run that SP by not giving him db_owner privileges?
Also I have to mention that my SP procedure has some dynamic SQL code.
I would greatly appreciate any help,
Thanks,
Roman
I'm attempting to connect to an SQL Server Express database from a remote PC, but it isn't connecting. I have a user account created in the database, and I'm using the SQL authentication with that user account. I have success with this if I'm on the server that the database is on, but not remotely. Both are on the same network, even plugged into the same hub. There doesn't appear to be any network problems.
Any ideas? Thanks!
Is there an easy way to backup a database on a remote SQL Server to a backup file on my local computer?
View 3 Replies View RelatedPlease bare with me as I do not have much knowledge in this area with MSSQL.
I have installed MSSQL 2000 Developer and I have setup a database which I am able to connect to and work fine with from the actual server MSSQL is installed on.
The problem comes in when i try to connect to that same database from a different remote server. It will not connect to the DB from this remote server but I know all the connection information is correct so its something on the MSSQL DB server causing this and not on this remote server.
Someone I know has had this problem and has fixed it but they wont tell me how. All I know is they said something about manually opening the ports on the MSSQL server and he also said when you set it to dynamicly determine the port that MSSQL is listening on it errored out. however if you set it staticly to 1433 it worked fine.
Ive also installed SP4 in attempt to fix this but no luck with that either. And I can also get the same exact remote server to connect to completely different DB server without issues.
If anyone can please help walk me through this I would be greatly appreciate and I am also willing to pay some money if it will make my life easier.
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...
we have sql 2005 standard edition. i was trying ( unsuccessfully ) to configure RAS on server 2003 to allow the server to authentificate vpn requests. i don't know how but the next day people started complaining about their mapped network drives being disconnected, and now no one can connect to the database via the network, eventhough they still have access to the server, and the network drives were remapped.
View 2 Replies View RelatedGreetings friends,
Not sure if this is the right place for my question but here goes anyway.
I have an instance of SQL Server 2005 installed on my DEV PC. A colleague of mine wants to access my server from his machine which connected to the same network.
I logged on to SSMS and added him as a new login.
Will he be able to connect to it now or do I have to do more than just that?
Your help would be appreciated.
Hello,
i'v installed a pogram that uses SQLEXpress and reporting sevices on a clients computer. I can acces the reports wih http://localhost/reports$SQLEXpress from that computer, but i can't reach them from other computers in the netwok by typing http://servername/reports$SQLEXpress
i am asked to imput a pasword for user "servername/guest" (which i do not know and dont think there is one) and then get the following:
You are not authorized to view this page
You do not have permission to view this directory or page using the credentials you supplied.
Please try the following:
Click the Refresh button to try again with different credentials.
If you believe you should be able to view this directory or page, please contact the Web site administrator by using the e-mail address or phone number listed on the preactor home page.
HTTP 401.3 - Access denied by ACL on resource
Internet Information Services
What should i do to solve this problem?
Regards
Darius
I have 2 xp machines one has the SQL EXPRESS Server with all the goodies on it and the other I just install the client stuff. I am using VB 6 to program with using the ADO. It works great on the server machine. The software pulls up great. I can't seem to get it to work on the client machine. I get "Login failed for user 'TIGGERLGuest'. I found the places to set the TCPIP setings on the server SQL Express to allow this even turned on the browser but nothing same error. I am using the same login on both machines and am pointed to the same workgroup. I used window authentication when I set them up. Not sure why it insists on using Guest.
This is a copy of the connection string I am using
"Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName="c:mpsdbmpswarehouse.mdf;Database=MPSDB;Data Source=tiggerlsqlexpress;Trusted_Connection=yes;"
Not sure if this is okay for connecting remote. Any help would be greatly appreciated
Rick
I am unable to connect to a remote server on Computer A from Computer B, but when I reverse it I am able to connect to a remote server on computer B from computer A. I have SQL Server 2005 Developer Edition on both computers. I am using the using the SQL Management Studio to connect. Both Computers have these settings
Using Surface Area Configuration both have local and remote connections checked. Both computers using tcp/ip only checked.
Using Configuration Manager both the sql server and the sql browser are running. Both computers have tcp/ip protocols enabled.
Under the server properties on both computers, under the Security Tab, I have SQL Server and Windows Authentication Mode checked. Under the Connections tab I have Allow remote connections on this server checked.
I believe I have the Security for both the server and databases done properly giving them both proper Logins and User Roles with the proper permission.
Also went to my window firewall settings and under the Exceptions tab, I added both the sql server.exe and sql browser.exe programs on both computers on my remote network.
But when I connect on Computer B, I get an error message stating:
TITLE: Connect to Server
------------------------------
Cannot connect to 'ServerName'
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 233)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476
------------------------------
BUTTONS:
OK
----------------------------
I tried pinging server a and then server b and they both pinged.
Could someone please tell me what could be wrong on my remote computers.
hi every one. i am a new user of asp.net 2.0 using C# code and i am facing a problem in accessing a SQL Server2005 database file in the remote computer. i have connected two pc with peer to peer connection and trying to add a databse using the "Add connection" option from the visual studio 2005. in the add connection dialog box it is showing me the remote server and it was supposed to show all the database in that SQL Server when i select one. but when i am choosing the server name it was not showing me anything. by the way i have configuered the surface area for "both TCP/IP and named pipes" and both the pc's server browser is turned on. is it the right way to access a database file from a remote pc or not?? please send me a good solution to do this things and try to explain the codes with example. waiting for response....plz send me the solution.. as soon as possible
View 2 Replies View Related