SQLTeam.com | Weblogs | Forums

SSIS/C# Help


#1

Hi Guys,

I am not sure it is possible or not. Never done it before so not sure. Need guidance....

  1. The first step Here is my Table Looks like in SQL

Table Name = ETL_Execution
ID,PackageName,PackagePath,Status
1,UniqueName,C:\temp\UniqueName.dtsx,Active
2,UniqueName1,C:\temp\UniqueName1.dtsx,Active
3,UniqueName2,C:\temp\UniqueName2.dtsx,InActive
4,UniqueName3,C:\temp\UniqueName3.dtsx,Active

  1. Second Step:-

I want to create a .Net Page in my application

Where I have Two tab

First Tab = Package Name
Second Tab = Execute

What I want, User will Pick the package from the drop down and Hit EXECUTE to EXECUTE THAT SSIS Package.

Please advise from where I can start. Any advice would be great appreciated.

Note:- FYI User doesn't have VS (Visual Studio) installed on the machine. However on Server VS Installed.

Thank You.


#2

Hi Sonu..

I hope you know how to code in .Net ,you can write a code in click event or execute button where you can pass
selected package name and retrieve the path from table for that package in your code .

after that you can utilize DTExec utility of ssis to execute your package.

https://msdn.microsoft.com/en-us/library/ms162810.aspx


#3

yes you can and you can do many different ways.

I would recommend you create a processing table that is tied to your .net application. The .net app writes to this table which could have UserName, ID (foreign key to ID from ETL_Execution), RunRequestStatus , RunRequestDate

When user clicks button to run it creates a row into this table. You create a sql job that runs every few minutes and leverages this table. After ssis package finishes running, you update RunRequestStatus to finished. This table also becomes your audit trail.

Maybe this can work?


#4

If you want some examples of how to do this I would recommend looking at the link below. It uses some of the suggestions already made but has specific examples and may expand a little on what you want to do. My experience has been that executing applications via a web page is a pain and has serious security considerations. The suggestions on the link also have security implications but may be more acceptable depending on your environment.

Execute SSIS Packages via Database