.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

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

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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"

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




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 

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 -



calling a SSIS package by parameter passing using AcvtiveX script (VB SCRIPT)


please find the following code, if i comment the parameter passing line, the code is executing fine, but at the paramter passing line giving all the trouble...any suggestions....

Dim objDTSPackage
Dim sTableName

Set DTSPk = CreateObject("MSDTS.Application")

Set objDTSPackage = DTSPk.LoadPackage("c:\SSIS\SplittingATabe.dtsx",  true, nothing)

sTableName = ltrim(rtrim(INPUTBO"Please enter the Table Data to split into files.")))
objDTSPackage.Variables["sTableName"].value = sTableName     



Procedure For Executing a SSIS package from .Net Application



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.

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 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.

Passing a parameter into a stored procedure from ASP.NET


Via the button1_ click event I retrieve all files in a directory (Example Follows)

protected void Button1_Click(object sender, EventArgs e)



DirectoryInfo di = new DirectoryInfo(@"C:\Test");

foreach (FileInfo filename in di.GetFiles())






I now need to check whether this filename exists as a field in a SQL Server table. For example, if the directory retrieves a file named ExampleFile.txt and does not exist in the (Table = TableFileStore;s Field = FileRetrievals) then I want to insert the filename.

How do I construct a stored procedure to pass the filename(s) into a stored procedure as a parameter? Do I need to also create an array of filenames and pass the array into my stored procedure? Does the stored procedure belong within the foreach loop?

How to call Oracle Stored Procedure which has an output parameter from SSIS?


I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:


    PROCEDURE Interface_Begin

    (p_from_dttm       OUT varchar2,

     p_error_code            OUT number,

     p_error_text       OUT            varchar2,

     p_proc_name        OUT varchar2);  


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

Passing a SSIS Variable with a string datatype to a Stored Procedure requiring a uniqueidentifier


I have a SSIS package that uses a SQL Server stored procedure as a data source. The parameter in the stored procedure is pulling data from a column with a uniqueidentifer (guid) data type. I created a package level variable in SSIS with a string datatype. SSIS variables do not offer a uniqueidentifier datatype.

I have the following text in the SQL Command text box:

exec dbo.spBillingReferenceSource
@BatchID = ? 

When I click the parameters button the mappings are @BatchID for the parameter (which is the exact name of the parameter in the stored procedure) and User::BatchID (the SSIS variable) 

When I run the stored procedure in preview mode in the SSIS dataflow, it returns an error message -- "No value given for one or more of the required parameters"

If I run the stored procedure by typing the value in the datasource box such as listed below, it works and delivers the proper records. I have even modified the stored procedure to convert the column to use varchar(36). However, it still returns the same error message. Does anyone have any suggestions on how I can get this to work. Thank you.


Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />

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?



TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

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.
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