Executing An SSIS Package From TSQL Without Using Xp_cmdshell?

Oct 22, 2007

How can I execute an SSIS package from TSQL without using xp_cmdshell?


I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell.


I want to do this:-
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /sq pkgOne"'


Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)?


Thanks

View 14 Replies


ADVERTISEMENT

Executing An SSIS Package From TSQL Without Using Xp_cmdshell?

Oct 22, 2007

How can I execute an SSIS package from TSQL without using xp_cmdshell?

I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell.

I want to do this:-
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /sq pkgOne"'

Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)?

Thanks

View 1 Replies View Related

Error Executing Xp_cmdshell From Within A SQL Task In A DTS Package...

Jul 20, 2005

HelloI am trying to execute ‘xp_cmdshell' from within a DTS package thatwas created by another person. When I try to execute that ‘SQL Task'selectively from within the package, I get the following errormessage:Error Title: Package ErrorError Details:Error Source: Microsoft OLE DB Provider for SQL ServerError Description: xpsql.cpp: Error 87 from GetProxyAccount on line604Why is this error message popping up? When I create a new package(myself) and create an exact same SQL task as above and run it, theSQL task runs fine.Appreciate any help / feedback.Thanks in AdvanceJagannathan Santhanam

View 1 Replies View Related

Executing DTS Package From TSQL

Oct 26, 2004

When run in query analyzer, the procedure below returns no errors however it doesn't execute the dts package I am passing to it.

The package works beautifully if you run it directly in Enterprise Manager.

Can anyone help me with a possible causes or perhaps help me improve my error trapping to help me resolve this?


create procedure sp_ExecuteDTSPackage
@login varchar(15),
@userpwd varchar(15),
@svr varchar(15),
@packagename varchar(255),
@pkgPwd varchar(15)

as

declare @hr int
declare @object int

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
Begin
Print 'Package Creation Failed'
RETURN
end


declare @flag int
select @flag = 0 -- 0 = sql svr authentication, 256 = windows authentication

EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login,
@PackageName=@packagename, @Flags=@flag, @ServerPassword = @userpwd, @PackagePassword = @pkgPwd

if @hr <>0
begin
Print 'Package load failed'
RETURN
end

-- execute the package

EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Package execution failed'
RETURN
END

--return the step errors as a recordset
--

EXEC @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
print 'UnInitialize failed'
RETURN
END

-- release the package object
EXEC @hr = sp_OADestroy @object
if @hr <> 0
Begin
RETURN
end

View 1 Replies View Related

Path Not Found When Executing Package Using Xp_cmdshell Dtexec

Feb 10, 2006

I have a package that executes fine in BIDS. However, when calling it using xp_cmdshell it cannot find the path to the source flat file. Anyone have any suggestions?

Here is the statement:

xp_cmdshell 'dtexec /SQ NatlAcctsImport /SER CR1-SQL-01 /CONN SourceConnectionFlatFile;Q:BINATNLACCTS'

Here is the result:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:50:56 AM
Progress: 2006-02-10 09:50:57.40
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.40
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.42
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Pre-Execute: 0% complete
End Progress
Warning: 2006-02-10 09:50:57.43
Code: 0x80070003
Source: Data Flow Task Source - NATNLACCTS [1]
Description: The system cannot find the path specified.
End Warning
Error: 2006-02-10 09:50:57.43
Code: 0xC020200E
Source: Data Flow Task Source - NATNLACCTS [1]
Description: Cannot open the datafile "Q:BINATNLACCTS".
End Error
Error: 2006-02-10 09:50:57.43
Code: 0xC004701A
Source: Data Flow Task DTS.Pipeline
Description: component "Source - NATNLACCTS" (1) failed the pre-execute phase and returned error code 0xC020200E.
End Error
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Pre-Execute: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:50:56 AM
Finished: 9:50:57 AM
Elapsed: 0.985 seconds
NULL

View 3 Replies View Related

Error Stating Package Failure While Executing SSIS Package In Standard Edition

Feb 2, 2007

Hi,

I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.

But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.

Can any one help me how to over come this problem.



View 1 Replies View Related

SQL 2012 :: How To Capture Data Flow Component Name Dynamically While Package SSIS Package Is Executing

Jun 3, 2014

I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?

View 5 Replies View Related

Execute SSIS Package From SP Without Xp_cmdshell?

Nov 12, 2006

A developer needs to execute an SSIS package from a stored procedure and I do not want to enable xp_cmdshell on SQL 2005.

One suggestion is to have the application invoke dtexec on the client PC, but this would cause the package to be executed on the client and also requires some SSIS components be installed on each client.

Another idea is to configure a Windows Service to execute dtexec, but I do not know the risks of this approach. Any thoughts?

Thanks, Dave

View 1 Replies View Related

Execute SSIS Package From Xp_cmdshell?

Mar 19, 2008

How to execute SSIS package from CMD or using xp_cmdshell?
SSIS package is saved in the folder of the integration services Db.

Thanks in advance.

View 2 Replies View Related

Integration Services :: Executing Child SSIS Package In Parent SSIS

Oct 9, 2015

I want to achieve the following in (SSIS/SSDT for SQL 2012) - 

I have a generic SSIS package which simply sends out email notifications using SMTP email task (this package is within its own project, and has project level input parameters).

I need to be able to call this package in the Event handler section of every package (numbering in about less than 60) that we have. These packages are within their own respective projects.

I thought I could use the "execute package task", but it turns out , using this, I cannot call a package that is part of some other project. I also cannot call a package that is stored in the CATALOG. Is there any way I can do this ?

When I call the child package , I should be able to send in parameters like - error information and package name of the Parent package.

View 8 Replies View Related

Error While Executing SSIS Package From Other SSIS Package

Jan 10, 2007

Hi,

In our project we have two SSIS package.

And there is a task (Execute SSIS package) in First package that calls the execution of second package.

I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."

As we are running first package by job, job runs successfully logging above error

The protection level of second package is set to "EncryptSensitiveWithUserKey"

Can anybody please suggest how to handle it?

View 4 Replies View Related

Tsql To Find Out If Ssis Package Is Running ?

Mar 12, 2008

what table/column can we query to determine if ssis package is running ?
How do we kill package that is running ?

View 4 Replies View Related

SSIS Package Not Executing As A Job.

Dec 21, 2007

Hi All,
I hav a strange problem. I created a SSIS package on my machine with protection level as "EncryptAllWithPassword".

When I deploy this package on a SQL Server on another machine (either in File System or SQL Server)and add it as a job in SQL Server Agent and try to run it through a scheduler or manually the job fails with the following message -

Executed as user:IBM-P4-1976SYSTEM. The package could not be loaded. The step failed.

However if I deploy it on my own machine the job runs successfully.
Also if I run the package using dtexec utility on the another machine it runs successfully.
It is not running as a job on another machine. All the logged in users have administrative rights. My databases are altogether on a different server.

Can any one help me out with this issue...Pleassssseeee...

View 1 Replies View Related

Executing A SSIS Package

Feb 4, 2008

Can someone tell me the best way to "automatically" execute a SSIS package. I have approximately 100 computers that I need to execute packages on. Here is the scenario:

I have some text files that are coming from our AS400 to a local computer (the package will NOT be used to bring files from our AS400. I will be using Client Access for that). The text files will be stored in a specified directory on the local computer. I will be importing these text files into a SQL Database on all 100 machines. Once I create the Import Package, what is the best way to execute this package on the devices?

I thought maybe after creating the package, I can put an EXE on the desktop that will execute the SSIS package. This way all I would have to do is click a icon on the desktop. I don't know if this is feasible or not. After building a package, do you make it a EXE or what? Pease advise. Thank in advance.

View 2 Replies View Related

Executing SSIS Package From .NET

Mar 24, 2006

Hi again

First I explain my environment:
- SSIS Packages are stored on 64bit Server (Win 2003 64bit, SQL 2005 64bit)
- .NET (C#) application (on Win 2003 32bit Server) which loads and executes the SSIS Packages.

I can execute my SSIS Package localy on the 64bit Server and they work fine.
When I execute the SSIS Package from the Application I get following exeption:
Retrieving the COM class factory for component with CLSID {697AC67E-FDD5-46D1-90D7-F3D0DF1B2A47} failed due to the following error: 80040154. ...
at Microsoft.SqlServer.Dts.Runtime.Package..ctor()

The code looks like:
Microsoft.SqlServer.Dts.Runtime.Package local_Package = new Microsoft.SqlServer.Dts.Runtime.Package();
Microsoft.SqlServer.Dts.Runtime.DTSExecResult local_DTSExecResult = new Microsoft.SqlServer.Dts.Runtime.DTSExecResult();
Microsoft.SqlServer.Dts.Runtime.Application local_Application = new Microsoft.SqlServer.Dts.Runtime.Application();

local_Package = local_Application.LoadFromDtsServer("PackageName", "64bitServer", null);
local_DTSExecResult = local_Package.Execute();


Other question would be: how can I execute a package directly on the server??
I mean without "LoadPackage"?? I guess the problem on top is because on the
32bit Server there is only Framework installed but not Integration Services Tools ...


Would be happy for any comment!

Best regards
Frank Uray

View 5 Replies View Related

Executing SSIS PACKAGE Through .net

Oct 24, 2007



Hi,

I have created a SSIS package through SSIS Tool. Protection Level is 'EncryptSensitiveWithUserKey'. When I execute , it works fine.

I have written a code to execute SSIS package through .net application. When i try in my local PC. The package works fine.

When I deployed in Webserver, it throws an error,

DTS Package: Package1 failed. Error Details as follows. 1. Source : Execute SQL Task Description : Failed to acquire connection "DatabaseConnection ADO.NET". Connection may not be configured correctly or you may not have the right permissions on this connection.


"DatabaseConnection ADO.NET". is a connection which is specified in the SSIS.

Can any one pls tell me how to solve this issue.

Thanks & Regards,
Ganesan

View 4 Replies View Related

Executing SSIS Package From Linux

Jul 3, 2007

Is it possible to execute an SSIS package from Linux? Before with 2005, we were able to execute a job that would execute the DTS, now we're trying to execute an SSIS package in 2005.


---
"Try not to become a man of success but rather try to become a man of value."

View 3 Replies View Related

Error While Executing The SSIS Package

Aug 14, 2007

I migrated the DTS package to SSIS package using the migration wizard...
but when i try to execute it it gives the following error plz help ...

error : Error retrieving file name for a component failed with the error code 0x043A1034

any suggestions ...

View 8 Replies View Related

SSIS Child Package Not Executing.

Feb 26, 2008

I have a parent package that calls a child package. When this is executed on SQL server (the actual server host SSIS services) it executes fine I can see data in the correct tables and I can determine that is ran. But I try to run this from my local pc within the designer (Visual Studios) and the parent package runs a few task that are before the child package task but once it gets to the child package it stops. I have set breakpoint on several task with the parent package and I can step thru them but once it gets to the child it tries to run it then the execute package task object turns red. I can not find any error messages or codes. I even have beak points set up on the child package itself and the process never hits those break points but I'm assuming is cause is puking before I get to the child package.

The child package is stored in SQL server and is in MSDB.
I just want to debug the child packages from BIDS or visual studios?

any one know how to debug child packages from BIDS or Visual Studios?
I can if I run the child package by itself but I need to test from end to end ....so within the parent packages which calls a few child packages..

thanks

View 3 Replies View Related

Executing SSIS Package Asynchronously

Feb 22, 2008

Is it possible to execute a child package from a parent package ashnchronously.


I have a SQL Server table containing a list of packages to execute. I want to create a master package that will query this table, and execute each of the packages asynchronously.

I've tried using the Execute Package task and also executing packages programtically from a Script task, but these only seem to work synchronously.

I have also seen suggestions about executing packages asynchronously from T-SQL by starting a job, however, I'd rather not have to dynamically create jobs for each package I want to execute.

Any ideas would be welcome. Or even an answer of "No this is not possible".


Thanks in advance

View 3 Replies View Related

Executing A SSIS Package With Variable Changes From ASP.NET

Mar 17, 2008

I am trying to execute a ssis package, where a user will be able to excute the package from an ASP page, the user will select a file, this will be the source file for the SSIS package, this will change when ever the package is executed, I was thinking of just using a sqlClient.sqlCommand and editing the command text and excuting it that way, or through a SP, would this be the best solution? Is there a better solution? and if not what would be the SQL command to execute the package, along with editing the variable?

View 4 Replies View Related

SSIS Package Fails While Executing

Mar 7, 2008



While executiing an SSIS package i get the follwoing error:

Executed as user: <User>. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 PM Error: 2008-03-06 23:00:01.02 Code: 0x00000000 Source: Execute DTS 2000 Package Task Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:00 PM Finished: 11:00:01 PM Elapsed: 0.859 seconds. The package execution failed. The step failed.


The SSIS package contains one "Execute DTS 2000 package" which is written in SQL server 2000. The DTS written in SQL server 2000 and then i made the SSIS package for the same to be executed as "Execute DTS 2000 package" BUt the package execution fails.


I have seen some workaround also for same where it was saying to install some dll files for SQL server 2000 Meta data services. SQL server 200 meta data services is required for running DTS packages in SQL server 2005 server.

I installed that also but still the same error.

I have done all but all in vain.
Your help will be very helpfull since this is the production issues.

Thanks,
Ashok

View 4 Replies View Related

Problem Executing SSIS Package

Apr 2, 2008

HI,

I am facing a problem in my packages. Whn I execute them in Visual Studio, it works fine but when I execute as standalone i.e by Cmd or by Double clicking on it, it throw PRODUCTLEVELTOERROR and "This component requires higher version of SSIS". I have SSIS running on my system as service and my SQL version is as below:

Microsoft SQL Server 2005 - 9.00.3186.00 (X64) Aug 11 2007 05:31:24 Copyright 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

I went through some sites and they say the issue is SSIS service not running or Standard version of SSIS. But my SSIS version and ssis service are both fine.
I went through http://msdn2.microsoft.com/en-us/library/ms143761.aspx and as per this it should be working on my system. Can anybody explain the reason why I am getting this error. Also, in one of my components, I am using reflection but this should not be an issue.

Thanks in advance.

Regards

View 3 Replies View Related

SSIS Package Gets Stuck While Executing

Dec 19, 2007

I have a package which reads from a source, does some transformation and then loads into a target table(custom transformation build to handle multiple scenarios for insert/update/delete etc). For couple of packages, I am facing an unusual problem. The package gets stuck after executing for some records. There is no data flow in the buffer. But again, if I stop executing the package and run it again, it runs through fine.
Any idea what could be issue or what particularly I should be looking for in order to debug this issue.

Thanks,
Manish

View 1 Replies View Related

How To Check Whether An SSIS Package Is Executing

Aug 23, 2006

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

View 11 Replies View Related

Problem Executing The SSIS Package Using C# Exe

Jun 6, 2007

Hi,



I am using C# exe to run the SSIS packages. I have placed the SSIS packages in a folder. So everytime i try to execute the SSIS through the exe i get the error "

An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user '[UserName]'.".



While creating the SSIS, i have saved the password. But when i open the SSIS to check I just see that the password field is blank.



Is there any other property which i need to set to keep the password saved forever??



Regards,



View 1 Replies View Related

Executing A SSIS-Package From Web Service.

Oct 10, 2007

I have a SSIS Package that I am executing from a Web service. The SSIS Package is stored as a file. That works fine. In the SSIS package I have one ADO.NET, .Net ProvidersSqlClient Connection. For the Protection Level I have EncryptSensitiveWithUserKey.

My problem comes when I change the Protection Level to EncryptSensitiveWithPassword. Now the SSIS package is throwing an error when trying to connect to the sql database: Login failed for user _ _

I believe my problem is that I don€™t know how to pass the password of the SSIS package from the Web service into the SSIS package.

Below is part of the code for the Web service:


<WebMethod()> _
Public Function LaunchSSISPackage( _
ByVal variableName As String, _
ByVal variableValue As Object) As Integer 'DTSExecResult
Dim packagePath As String
Dim myPackage As Package
Dim integrationServices As New Application
Dim SSISvar As Variable

myPackage = integrationServices.LoadPackage(packagePath, Nothing)
SSISvar = myPackage.Variables.Add(variableName, False, "User", variableValue)
LaunchSSISPackage = myPackage.Execute()


I appreciate your help with this issue I am having. Thank You.

View 4 Replies View Related

Stored Procedure To Executing An SSIS Package

May 24, 2006

In SQL Server 2005 I need a stored procedure that will execute an SSIS Package for me. There is some earlier stuff on the board but I don't understand it. I don't want to create a Job to do it if I don't have to.

Thanks,

George Cooper



View 3 Replies View Related

Executing SSIS Package From Agent Fails

Feb 7, 2007

Hi,

I have an SSIS package that utilises a 3rd party ftp program to transfer files (over HTTPS). This software stores details in the users profile relating to addresses, user names and password for transfers. As this is the case the Package needs to be executed by the domain user who has the details set in their profile. The package needs to be executed at a scheduled interval - so I have set up an Agent job to do this, and have the the 'Run As' setting, as a proxy which maps to the required domain user.

The package works fine when executing manually when the required user is logged in. If, however, the user is not logged in - ie when the job kicks off at the schecduled time, the file transfer fails. On debugging I can see that the agent job does not load the user's profile -but instead uses the 'Default User' profile.

The job owner is set as the same domain user that the Run As setting for the step is set. The SQL Server Agent services runs as a different Domain user.

Has anyone else had similar problems - Are there any extra permissions I need to set?

cheers..

View 4 Replies View Related

User Rights And Executing Of SSIS Package

Mar 31, 2006

I have a small SSIS package which:

- drops existing table in Excel,

- creates a new one in Excel,

- copies data from SQL to newly created sheet.

So there are 2 connections: one to a excel file, and second to SQL using sa account. I am working on my account belonging to Administrators group.

SQL Server and Excel file are on my development machine. Everything works ok when I run package from file system using DTExecUI.exe (in context of my user belonging to Administrator group). But when I have created job to execute this package problem with security occured. Even assigning local account on which SQL Server and SQL Server Agent works to Administrator group do not help. I have simulated what can happen when running DTExecUI.exe utility in contex of different user. I have used Administrator (I start it using "Run As..." from context menu in Explorer"). And what occured, SSIS package can not login to SQL Server using sa user!

1. Why running DTExecUI.exe in context of different user impact login to SQL on sa accout? If it were Windows Integrated I could understand it.

2. Especially if the different user is also Administrator!!!



I have found solution for running a task in SQL Agent - SQL Agent has to work on the same account on which SSIS package was created. But this is crazy when I would like to deploy it to a production server.

Please help.

Przemo

View 1 Replies View Related

Executing SSIS Package From Stored Procedure

Mar 8, 2006

Hey guys,
I've got a problem here. I need to send the query result to a csv file then transfer the file to a website. I thought this is a good candidate for a SSIS package. The package is ready now but I don't know how can I execute it from within a stored procedure.
I thought sp_OA family of extended procedure would be helpfull. After following steps:
EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT

EXEC @hr1 = sp_OAMethod @oPKG, 'LoadFromSQLServer("foo", ,, 256, , , , "foo1")', NULL

EXEC @hr1 = sp_OAMethod @oPKG, 'exec'
EXEC @hr1 = sp_OADestroy @oPKG
it tells me command execute successfully. But no package actually gets executed and I can see no results
Thanks

View 1 Replies View Related

Executing SSIS Package In SQL SERVER 2005

Oct 5, 2007

Hi, here is my problem :

I want to run ssis package in sql server 2005 agent.
i've made a new JOB with 3 steps ( 3 ssis package)

But when i run the job, i see only 2 "actions step" in the windows :

Start job .....
Execute job ....

I would like to see at least the execution status of every ssis package and if it's possible,
the execution of every task ( to see quickly if there's some error and what's wrong)

What's the best solution to doing that

Thank for helping
BG.

View 1 Replies View Related

Executing SSIS Package - SQL 2005 Express

Jan 20, 2007

Hi,

 I have created SSIS Package using DTS vizard in SQL 2005 Express. Help me out to execute the Package.

 

Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved