Introduction
If you're dealing with the SSIS package in C#, you'll need to know how to load and run it. Because sending the SSIS package arguments to an SSIS package and executing it from C# can make your job easier.
Wondering how you can load and execute the SSIS package in C#? Here is a guide for you.
In this guide, we'll help you understand a step-by-step process on how to easily load and execute the SSIS package in C#.
What Is SSIS?
SSIS stands for SQL Server Integration Services. It is a Microsoft SQL Server database platform that is used to build enterprise-level data integration and transformation solutions.
Designed to be fast and flexible, SSIS is a great data warehousing tool for performing high-performance data integrations and solving complex business problems. Integration services can be used to manage databases, load data warehouses, copy files, download data, cleanse and analyze data, and control SQL server objects.
Different Types Of SSIS Components
- Control Flow:
- Data Flow:
- Event Handler:
- Package Explorer:
- Parameters:
It allows storing containers that provide structures in packages and tasks, different task execution, and organizing a workflow between tasks.
This includes multiple data flows that comprise various sources and destinations, plus allow users to transform, clean, and modify data.
It is one of the most useful and powerful SSIS components. It gives the ability to handle various events connected with the task and container execution. Moreover, it helps perform tasks such as cleaning up temporary data storage, retrieving system information, refreshing data in a table, and so on.
This offers a hierarchical view of all of the elements in a package, like event handlers, configurations, connections, executable objects, log providers, and variables.
SSIS parameters are useful for fostering user interaction. It is used to send data outside of the package, such as usernames, passwords, connection strings, etc.
Key Functions Of SSIS
The most common uses of SSIS include
- Data Archiving:
- Data Loading:
- Data Indexing:
- Data Cleansing:
SSIS is commonly used to homogenize the information for seamless management of vast volumes of data coming from different sources like databases, flat files, XML, and web services. Further, by cleansing, aggregating, splitting, formatting, and merging valuable data sources, it transforms archived information into powerful assets for companies.
Another function of SSIS is to efficiently bulk-load information from the flat files in the SQL database. It can quickly handle various types of errors occurring due to complex data loading and can rerun packages smoothly. Moreover, it is capable of denormalization that helps source data from various destination types like SQL Server databases, Excel spreadsheets, or other data warehouses.
SSIS uses "Slowly Changing Dimension Wizard”, which allows dynamic creation and configuration of data transformation tasks like adding records, new tables, columns, and rows to simplify history management.
SSIS can be used for standardizing various information, where it can help perform data transformation tasks such as data cleaning, converting, and enriching. In addition to this, the SSIS grouping transformation feature can also help identify duplicate data records and remove them before loading.
Other than these, SSIS is also useful for workflow automation, error handling, data integration, task scheduling, package development, security, logging, and auditing.
How Do You Load And Run The SSIS
Package In C#?
Loading and running an SSIS package with C#, especially in your .NET app, can be tricker. Hence, here are the processes that you can use to get better results. Have a look.
Take Note Of The Prerequisites
Before starting with your task, you need to make sure that you have Visual Studio or Visual Studio Code installed on your device. If you do not have it, then you can download the free community edition of Visual Studio or Visual Studio Code.
If you are trying to build a connection with an Azure SQL Database Server from a firewall, then make sure the port is open in the corporate firewall so you can connect successfully.
Get The Connection Info
If you are using the Azure SQL Database, then you have to derive all the necessary connection information necessary to connect with the SSIS Catalog database. For this process, you will need a completely qualified server name along with login information.
First of all, log in to your Azure portal. Then you have to select SQL Database and SSISDB database from the SQL Database page. From the overview page, you need to review the complete qualities of the server name. To see this, go to the Click to Copy option. Then you have to enter your Azure SQL Database server login information. Click on Show database connection strings. After that, you have to review the complete ADO.NET connection string.
Make Up A New Visual Studio Project
To proceed further, you need to create a visual studio project. To create it, choose File, New, Project, and then at the New Project dialog expands to Visual C#. Then you need to select the Console App to enter your run_ssis_ project as the project name. Click on Ok to create and open a new project with Visual Studio, where you can add the necessary references.
Add The C# Code
To add the C# code, you need to open the Program. cs file. Then you have to replace the Program.cs with the necessary code. After that, you need to add the appropriate value for your database, user, server, and password.
After adding it, you can run the code by pressing F5. Also, you need to verify that the package is running as you expected.
How Do You Execute The SSIS Package With C#?
To execute your SSIS Package with C#, you have to add a Reference to run the DTS package. The Reference you will need is – Microsoft.SqlServer.Dts.Runtime.
This reference is used to help in loading the SSIS Package and executing the SSIS Package. To use this, first of all, you will need to load the SSIS Package. Then you have to store all the Package details in the variables for Packages. Then set the Package Variable with the use of proper code. Then you have to set the variable into SSIS Package.
After that, you can execute the SSIS package with the package.Execute(). Then store it in the DTSExecResult variable. Now you can show any result based on Execution for SSIS Package. After implementing the code, execute the Stored Procedure from C# Code to Execute your SSIS Package.
FAQs:
- How To Execute The SSIS Package From The Stored Procedure?
- How To Open The SSIS Package In Visual Studio?
- How To Connect To SSIS From Visual Studio?
- How To Run An SSIS Package In SQL Server?
- How To Load SSIS Package In SQL Server?
Follow these steps to execute the SSIS package from the stored procedure:
Step 1: Create A Stored Procedure
Create at least two stored procedures to execute the SSIS package. From the SSISDB database – create_execution and start_execution. Here in create_execution, specify details needed to identify the package to be executed. These details include folder name, project name, and package name.
Step 2: Execute An SSIS Package
After filling in all the details and executing other stored procedures, execute an SSIS package. To check the execution status of the SSIS package, check the [SSISDB].[catalog].[executions] view.
Steps to open the SSIS package in Visual Studio code:
Step 1: Open Visual Studio code and select FILE> NEW > PROJECT.
Step 2: Browse the SSIS Package location or click CREATE to create a new project.
Step 3: Now open the project in the Visual Studio for editing.
Step 1: Open the VS code and press CTRL+SHIFT+P or F1 to open the Command Palette.
Step 2: Type sqlcon and press ENTER.
Step 3: Select Create Connection Profile to create a connection for the SQL Server instance.
Step 4: Specify the connection properties for the new connection profile and press ENTER to continue.
Step 5: Press the ESC key to close the message informing that profile is created and connected.
Step 6: Lastly, verify the connection in the status bar.
Step 1: Select the package you want to run in Object Explorer.
Step 2: Right-click and select Execute to open the Execute Package dialog box.
Step 3: In the Execute Package dialog box, use the settings on the Parameters, Connection Managers, and Advanced tabs to configure the package execution.
Step 4: Click OK to run the SSIS package.
Step 1: In SQL Server Management Studio, expand the Integration Services Catalogs > SSISDB node in Object Explorer.
Step 2: Right-click the Projects folder, and then click Deploy/Load Projects.
Step 3: If you see the Introduction page, click Next to continue.
Step 4: Now, on the Select Source page, switch to the Package Deployment model, select the folder containing source packages, and configure them.
Step 5: Complete the wizard and follow the remaining steps described in Package Deployment Model.
Wrapping Up
While loading and executing the SSIS package in C#, you need to keep in mind that you are using the right code for it.
In this brief guide, we have outlined every necessary step required to contribute to effective and quick results.
So make sure to use all the above-mentioned steps for loading and executing the SSIS package in C# so that you can reach out to the result quicker.
Moreover, if you are looking for a trusted development partner, look no further than Polyxer Systems.
Polyxer is a renowned outsourcing and technology consultancy helping enterprises with smart development solutions meeting high-quality standards.
Contact us now, and we'll help you build the application using the soundest frameworks and the latest technologies to meet your requirements.
