Solved

Proxy for SSIS pkg

Posted on 2011-09-13
4
404 Views
Last Modified: 2012-05-12
I have created a proxy that sucessfully runs a SSIS pkg.  Now, I would like to use that proxy in a program to run the package from a program on a user's pc.  Does anyone know how to do this?  
0
Comment
Question by:McGurk1
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36532574
By "proxy', do you mean that you've scheduled a SSIS package as Job in SQL Agent

or

have you created some custom code (in Visual Studio perhaps) that has been designed to execute the Job or the SSIS package directly?

Thanks for the clarification!
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36536298
Just load the package in Application using code and execute it.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 250 total points
ID: 36536311
Microsoft.SqlServer.Dts.Runtime.Application app;
                app = new Microsoft.SqlServer.Dts.Runtime.Application();
                WindowsIdentity UserID = WindowsIdentity.GetCurrent();
                List<SectionMaster> objSubSections = new List<SectionMaster>();

                objSubSections = new DMA.Base.BusinessLogic().GetSubSectionByParentID(parentSectionID);

                foreach (SectionMaster item in objSubSections)
                {
                    try
                    {
                        Package pkgIn = new Package();
                        DTSExecResult pkgResults_Sql;
                        string strSectionName = item.sectionName;

                        strCurrentpackage = strSectionName;

                        pkgIn = app.LoadFromSqlServer("\\Server\\" + strSectionName, ConfigurationSettings.AppSettings["ServerIp"].ToString(), "", "", null);

                        pkgIn.Variables["InsuranceCompanyIDs"].Value = strSelectedCompanies;//"1,2,3,4,5,6,7";


                        SSISEventListener Listerner = new SSISEventListener();

                        pkgResults_Sql = pkgIn.Execute(null, null, Listerner, null, null);

                        if (strProcessIDs == "")
                            strProcessIDs = pkgIn.Variables["ProcessIDList"].Value.ToString();
                        else
                            strProcessIDs = strProcessIDs + "," + pkgIn.Variables["ProcessIDList"].Value.ToString();

                        int EventCount = Listerner.EventCount;
                        pkgIn.Dispose();

                        Package pkgIn_Archive = new Package();
                        DTSExecResult pkgResults_Sql_Archive;
                        pkgIn_Archive = app.LoadFromSqlServer("\\Server\\ServerDB", ConfigurationSettings.AppSettings["ServerIp"].ToString(), "", "", null);

                        pkgIn_Archive.Variables["InsuranceCompanyIDs"].Value = strSelectedCompanies;//"1,2,3,4,5,6,7";
                        pkgIn_Archive.Variables["SectionID"].Value = item.sectionID;//"1,2,3,4,5,6,7";

                        SSISEventListener Listerner_Archive = new SSISEventListener();
                        pkgResults_Sql_Archive = pkgIn_Archive.Execute(null, null, Listerner_Archive, null, null);

                        pkgIn_Archive.Dispose();

                    }

Open in new window

0
 
LVL 21

Accepted Solution

by:
huslayer earned 250 total points
ID: 36543114
Schedule an agent job using the package and the proxy, and let the application call that sql agent job.

that way you don't need to hard code the proxy and package in the application.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now