How to create a SSIS package dynamically and invoke the same in C#?

Question

0.00/5 (No votes)

See more:

I created a package manually and set a sample source destination mapped say from a excel file to destination DB and invoked it programatically and saved the same.

But I need to set the source destination mapping package for the files with in the sub folders of a parent folder configured and there after invoke it dynamically. Thus the goal is to save the records of each file with in respective folders to Sql invoking the dynamically created Package.

What I have tried:

My code is as below:

string strSourceConn = @"C:\Users\employee.txt";
            string strDestConn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();

            string pkgLocation = @"D:\SSIS\Test\Integration Services Project1\Integration Services Project1\Package.dtsx";


            Package pkg;
            Microsoft.SqlServer.Dts.Runtime.Application app;
            DTSExecResult pkgResults;

            app = new Microsoft.SqlServer.Dts.Runtime.Application();
            pkg = app.LoadPackage(pkgLocation, null);
            
            //pkg.Variables["strSourceFile"].Value = strSourceConn;
            //pkg.Variables["DestinationFilePath"].Value = strDestConn; 
            
           // pkgResults = pkg.Execute();
            Variables vars;
            vars = pkg.Variables;
            vars["strSourceFile"].Value = strSourceConn;
            vars["DestinationFilePath"].Value = strDestConn;
            pkgResults = pkg.Execute(null, vars, null, null, null);
            //Response.Write(pkg.ExecutionResult);
            Response.Write("<script LANGUAGE='JavaScript' >alert('Saved Successfully')</script>");

Posted 28-Mar-18 23:59pm

ranio

Updated 29-Mar-18 2:00am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2018-03-29T02:02:00

Solution 1

I personally wouldn't do it that way.

First, I'd create a folder on a network share to hold the file(s), and always copy them there.

Next, I'd write an importer package for EACH SEPARATE FILE. That package could be hard-wired for the source file and destination database, eliminating the need to maintain any package variables required for that purpose. This prevents one importer from affecting all of the others if it fails for some reason.

If you insist on running the packages manually, you could create some sort of management object in your code to run them. However, I would personally make them run on a schedule every 10-15 minutes (unless the files are being updated in a way that you have not as yet divulged.

The whole mechanism would be easier to implement and maintain. Just sayin...

Posted 29-Mar-18 2:02am

#realJSOP

Comments

ranio 29-Mar-18 9:33am

I was able to create the package manually for a text file sample.
But what i need is to iterate the files with in sub folders of a parent folder and save the records with in each file bulk wise by invoking the dynamic package created.

http://www.sqlservercentral.com/blogs/on-the-fringe/2013/08/25/creating-and-executing-an-ssis-package-programmatically/

#realJSOP 29-Mar-18 13:56pm

Is it that you don't know how to iterate files in a folder?

ranio 3-Apr-18 0:21am

That is possible. Will try with few sample files and see and update.
As mentioned would simply iterate the files in the sub folder of parent folder and apply for each loop and pass the files as in the code done by me
string strSourceConn = @"C:\Users\employee.txt";
string strDestConn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();

string pkgLocation = @"D:\SSIS\Test\Integration Services Project1\Integration Services Project1\Package.dtsx";

Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;

app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);

//pkg.Variables["strSourceFile"].Value = strSourceConn;
//pkg.Variables["DestinationFilePath"].Value = strDestConn;

// pkgResults = pkg.Execute();
Variables vars;
vars = pkg.Variables;
vars["strSourceFile"].Value = strSourceConn;
vars["DestinationFilePath"].Value = strDestConn;
pkgResults = pkg.Execute(null, vars, null, null, null);
//Response.Write(pkg.ExecutionResult);
Response.Write("alert('Saved Successfully')");

ranio 3-Apr-18 4:33am

I am able to map the source and destination to a table in Sql Server in a package. But i need to change the source path dynamically . While invoking the package path set manually is getting worked instead of the source path dynamically.

Core code is as below

try
{

Application app = new Application();

Package package = null;

package = app.LoadPackage(@"D:\SSIS\Test3\Integration Services Project1\Integration Services Project1\Package.dtsx", null);

// Add a Connection Manager to the Package, of type, FLATFILE
var file = @"D:\SSIS\Test3\employee11.txt";//Source Path set
var connMgrFlatFile = package.Connections.Add("FLATFILE");

connMgrFlatFile.ConnectionString = file;
connMgrFlatFile.Name = "My Import File Connection";
connMgrFlatFile.Description = "Flat File Connection";

// Configure Columns and their Properties for the Flat File Connection Manager
var connMgrFlatFileInnerObj = (Wrapper.IDTSConnectionManagerFlatFile100)connMgrFlatFile.InnerObject;

connMgrFlatFileInnerObj.RowDelimiter = "\r\n";
connMgrFlatFileInnerObj.ColumnNamesInFirstDataRow = false;

//Excute Package

Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{

foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
{

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

Console.WriteLine();

}

}

Response.Write("alert('Saved Successfully')");

}

catch (DtsException ex)
{

// Exception = ex.Message;

}
}