Calling storedprocedure in SSIS Package with Input Parameters

I am invoking a Storedprocedure from SSIS Package. This storedprocedure has three input parameters like Start Date, End Date and a String Variable.

We are calling t开发者_StackOverflow中文版he SSIS Package from .Net Application. So, we have to pass paramters from C# Code to SSIS Package which in turn passes to Storedprocedure.

I am looking for articles and coders to suggest me.

Here is a possible solution. You can declare variables in SSIS package and set the stored procedure to use them as input variables. Then, pass values to variables from C#.

Here is an example:

In this scenario, I am inserting state and country into a table by passing values from C#.

1. On the SSIS package, create three parameters StoredProcedure, Country and State.
2. Assign the value EXEC dbo.InsertData @Country, @State to the variable StoredProcedure. Refer screenshot #1.
3. Drag and drop a Execute SQL Task on to the Control Flow tab.
4. On the Execute SQL Task Editor, configure it to use an ADO.NET connection.
5. Change SQLSourceType to Variable.
6. Assign User::StoredProcedure to SourceVariable. Refer screenshot #2.
7. Refer screenshot #3 to see how Parameter Mapping section is configured.
8. In C#, you can pass the parameter values as shown below. This code requires reference to DLL Microsoft.SQLServer.ManagedDTS.
9. Screenshot #4 shows the data inserted into the table.

.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;

namespace CS.ExecuteSSIS
{
    class Program
    {
        static void Main(string[] args)
        {
            Application app = new Application();

            Package package = app.LoadPackage(@"C:\Learn\SSIS\Learn.SSIS\Learn.SSIS\CallFromCS.dtsx", null);
            Variables vars = package.Variables;
            vars["Country"].Value = "US";
            vars["State"].Value = "California";

            DTSExecResult result = package.Execute();

            Console.WriteLine("Package Execution results: {0}", result.ToString());
        }
    }
}

Stored Procedure dbo.InsertData:

CREATE PROCEDURE [dbo].[InsertData]
(
        @Country    NVARCHAR(50)
    ,   @State      NVARCHAR(50)        
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.SSISUsingCS (Country, State) VALUES(@Country, @State)
END

Hope that helps.

Screenshot #1:

Screenshot #2:

Screenshot #2:

Screenshot #3:

See info on the Application object and the Package object where the Execute(Connections, Variables, IDTSEvents, IDTSLogging, Object) method allows passing in parameters.

    var app = new Application();
    Package myPackage = app.LoadFromSqlServer("myPackage", "server", "login", "password", null);

    Variables params = myPackage.Variables;
    params["StartDate"].Value = DateTime.Now;
    params["EndDate"].Value = DateTime.Now;
    params["StringVariable"].Value = "ooh a string";

    var packageResult = myPackage.Execute(null, params, null, null, null);