Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Proxy for SSIS pkg

Posted on 2011-09-13
4
Medium Priority
?
448 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 1000 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 1000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

636 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