.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Procedure For Executing a SSIS package from .Net Application

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :Sql Server


I developed a ssis package and deployed to the MSDB location of the SQL server2005 server.

I developed a vb.net application and calling the SSIS package.

Its working fine  and executing the package.

Right now in my system SQL server 2005 is installed.

When I am executing the same application from the system in which SQL server environment is not there I am getting error.

What should I need to do If I want to run the same application from the system which don't have any SQL server environment.

Thanks in advance.

View Complete Post

More Related Resource Links

Executing an SSIS package from an stored procedure and passing a parameter to it:

Hello all, I'm trying to execute an SSIS package from an stored procedure, using xp_cmdshell. When I execute the package, hard coding the value on the variable, I get rows inserted to my destination tables, which indicates my package pulled the correct data based on the TransactionID parameter. When I execute the package from the stored procedure, the package executes without issues, but I get no rows inserted into my destination tables, which leads me to think it is a data type issue, between the package and the variables I'm using within the stored procedure. The variable TransactionID on the SSIS package, is a string data type, therefore I'm passing it as a varchar from the stored procedure. Am I doing this correctly? Maybe I'm missing something, can you guys see anything wrong? Also, I tried login into the server and executing these 2 commands on the cmd prompt: One with "" quotes between the TransactionID value: dtexec /FILE "D:\SSISPackages\MyOrderCheckProcess\MyOrderCheck_RenewalOrder.dtsx" /CONFIGFILE "D:\SSISPackages\MyOrderCheckProcess\MyOrderCheckProcess_Config.dtsConfig" /CHECKPOINTING OFF  /REPORTING EWCDI  /SET \Package.Variables[User::TransactionID].Properties[Value];"470219"   And also one without quotes between the TransactionID value: dtexec /FILE "D:\SSISPackages\MyOrderCheckProcess\MyOrde

Error while executing SSIS package from a Stored Procedure


I am getting an error while executing a SSIS Package from SQL Server Stored Procedure.  But when I run it in BIDS, it executes successfully. Any help on this ???

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured.  Error Code: 0x80040ED. An OLE DB record is available. Source "Microsoft OLE DB Provider for Oracle" Hresult 0x80040eD Description: "ORA-01017: invalid username/password; logon denied"

Execute SSIS package stored in Database - From Stored Procedure

Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 

Procedure timeout when executing from the ASP.NET application

Hello,I'm working with ASP.NET 3.5 and SQL Server 2008.I have a very weird problem. When I execute the procedure from my ASP.NETapplication (either from my PC or the Web server), it's timing out. At thesame time when I execute the same procedure with the same parameters fromthe SQL Server Management Studio - it executes instantly, so aparently thereis nothing wrong with it.This applies only to the few procedures, with most of them I don't have anyissues.To add to this mistery, this problem is intermittent, i.e. 50% of the time everything works OK.It seems that something is happening somewhere in the middle tier between myapplication and the SQL Server, but I have no idea what could it be and howto find the problem. I've increased the command timeout time to the max, and this is not an issue - the procedure in the SQL server works almost instantly. The number of connections is not an issue either - usually no more than one or two users are connected at the time. This is not an IIS issue either - when the application doesn't work, it doesn't work on the web server and my computer at the same time. When it does work - it also works in both places. I'm just lost. It seems that this problem started after we upgraded our SQL Server to 2008, never had any issues like this before.I would appreciate any comments on this.Thank you,Peter 

Can a stored procedure programmatically determine if an SSIS package is running?



If I have the 'ExecutionInstanceGUID' value for an SSIS package that I know was started, is there a way that a stored procedure can programmatically determine whether the SSIS package still is running?

Thanks -



Running SSIS package from Stored Procedure using dtexec and Pass boolean value



We are using SSIS 2005 and sql server 2005. My package has a boolean type package level variable. Eg: IsClientNull boolean type.

I call this package from stored procedure. In addition to passing values for other parameters, how can I pass boolean value.

My code is like this:

SET @CMD='dtexec ' +
'/FILE ' + @pShareName + @pPackageName + ' ' +
'/MAXCONCURRENT " -1 " ' +
'/SET "\Package.Variables[User::StartDate].Properties[Value]";' +
CONVERT(char(10), @pStartDate, 120) + ' ' +
'/SET "\Package.Variables[User::EndDate].Properties[Value]";' +
CONVERT(char(10), @pEndDate, 120)
IF (@pClient IS NULL)

 SET @CMD = @CMD + ' ' +
 '/SET "\Package.Variables[User::IsClientNull].Properties[Value]";' +

How to send any collection object from .NET Application to object variable created in SSIS Package


HI All,

I have a question regarding SSIS -- How to send list object from .NET Application to object variable created in SSIS Package. In List object there are one lack records stored..This list object needs to be passed to SSIS Package. Please reply me back with any suggestion/solutions ...Required ASAP.

Waiting for your replies.





Executing an SSIS package from VB.NET Web Appliaction from the client'd desktop


I want to ask the following:

Does anyone have an example of some code which executes an SSIS package from a ASP.NET web app using VB.NET? 

I have built a web application and want to be able to execute the SSIS package when the user clicks a button on the web page.

So far I have:

Dim app As New Application
Dim PkgName As New Package
PkgName = app.LoadPackage(strPackagePathName, Nothing)
app.PackagePassword = strSSISPassword
Dim execResult = New DTSExecResult()
execResult = PkgName.Execute()

What I know is that this does not work from a client's desktop as the above code executes the package locally and it needs to be exectuted remotely. It does work if I execute it from the server.

I need to know how to set this up so it runs remotely.  I have searched MSDN (useless), codeproject.com and sqlservercentral. No luck.




How to execute ssis package from stored procedure

how to excute ssis package from stored procedure and get the parameters back from ssis into the stored procedure.

How do you set SSIS package variable equal to an output parameter from a stored procedure that is ty


I have a stored procedure insertXXX that has one OUTPUT parameter of type numeric. The stored procedure looks like:



PROCEDURE [dbo].[insertIntoTable]
AS numeric OUTPUT




Error on getting return value from Oracle procedure in SSIS package

In my case, I want to execute a stored procedure in Oracle database using SSIS package and if there has error in Oracle, I want to get the error message from Oracle and pass it to SSIS to log the error.

I can execute the stored procedure through SSIS successfully, but the problem is I cannot get the error message using OUT parameter of procedure in Oracle, the following part is what I have done to test whether SSIS can fulfill my requirement:

1. Oracle client installed, use Microsoft OLE DB Provider for Oracle, Test Connection to Oracle succeeded.

2. The stored procedure created in Oracle database:
    create or replace p1(in1 varchar2, out1 OUT varchar2) as begin
        out1 := 'nice';

3. In the SSIS package, I add a "Execute SQL Task" component:

Variable defined = User::output (String datatype)
ResultSet = Single Row
SQLSourceType = Direct Input
SQLStatement =

        in1 varchar2(10);
        out1 varchar2(10);
        in1 := 'wow';
        out1 := '1a2';

Executing a SSIS pkg from a stored procedure



I have a SSIS 2005 pkg and I want to launch it into a loop inside a stored procedure in SQL Server 2005.

Is it possible, please? How? Many thanks

using SharePoint List for SSIS package

I need to use a sharepoint list as data source for SSIS package, it works fine until the sharepoint site implement Kerberos only (IIS 7) authentication, anyone knows how to get around this? thanks.

Using the BDC API how to catch the returned code when executing a stored procedure?

using BDC API, how can i catch the return code a stored procedure returns?

I tried Entity.Execute to execute a generic method. However, it returns an empty Entity table.

I know you can do it by setting up an output variable. I prefer returning an integer for tracking errors.

Any ideas?



Error Executing CLR Stored Procedure "Item has already been added. Key in dictionary"

Hello, I'm consuming a web service through assemblies in a Sql Server 2005 database. The client was made with VB.net 2005. Everything was fine in the deploy phase but once I compile the assembly generated in my sql server database and try to execute the CLR stored procedure defined in the assembly it crashes throwing the following error: Error: There was an error generating the XML document. Inner Exception: Item has already been added. Key in dictionary: 'SqlCifin.InfoComercial.ParametrosConsultaDTO' Key being added: 'SqlCifin.InfoComercial.ParametrosConsultaDTO' Where SqlCifin is the name of the assembly, InfoComercial is the web reference namespace and ParametrosConsultaDTO is a complex type defined in the WSDL to encapsulate the request parameters. I tried almost everything but nothing seems to work: Already checked the enviroment variables and . I would appreciate any help you can provide me.  PD: I'm using WSE 3.0. Thanks, Andres Diab.

SSIS Package Transaction locks entire table

Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend