Solved

Proxy for SSIS pkg

Posted on 2011-09-13
4
428 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Jason Yousef, MS 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Open dialog with server side controls in it 3 47
Get sourcecode path 14 48
Nested forach loop to linq 3 30
Check only one toolstripmenu item 12 31
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

730 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