public int ExecuteSSISPackage(string ServerName,string UserID,string Password,string tpackageName,string tPackagePwd,List<parameters> tParams) { ServerConnection svcCon = null; JobServer js = null; string jobName = string.Empty; JobCategory jc = null; try { //serverName = Properties.Settings.Default.ServerName; svcCon = new ServerConnection(ServerName, UserID, Password); Server svr = new Server(svcCon); //WriteTrace("Connected to {0} {1}", svr.InstanceName, svr.Information.Version); +" "+ "Information "+svr.Information.Version.ToString()); js = svr.JobServer; jobName = "dtexecRemote_AM_job_" + Guid.NewGuid().ToString(); jc = new JobCategory(js, "dtexecRemote"); jc.CategoryType = CategoryType.LocalJob; jc.Refresh(); if (jc.State != SqlSmoState.Existing) { jc.Create(); } Job job = new Job(js, jobName); job.Category = "dtexecRemote"; job.Create(); try { job.ApplyToTargetServer(svr.InstanceName); JobStep step = new JobStep(job, "run package"); string cmd = Environment.CommandLine; //cmd = @"/sq AM_RemotePackage /De ""K1PG"" /set \package.variables[AILevel1ID].Value;" ; cmd = @"/sq "+tpackageName+@" /De """+tPackagePwd+@"""" ; StringBuilder sbAppendParams = null; sbAppendParams = new StringBuilder(); foreach (Parameters paramlist in tParams) { if (paramlist.ParmeterValue.ToString().Trim() == string.Empty) { paramlist.ParmeterValue = "''"; } Utility.WriteLog("Reading Parameters of PackageName :"+tpackageName.ToUpper()+"\n"); Utility.WriteLog("ParamaterName :" + paramlist.ParameterName.ToString() + " " + "ParameterValue :" + paramlist.ParmeterValue.ToString()+"\n"); sbAppendParams.Append(@" /set \package.variables["+paramlist.ParameterName.ToString()+"].Value;"+paramlist.ParmeterValue.ToString()); } cmd = cmd + sbAppendParams.ToString(); if (cmd == "") { return 1; } //WriteTrace("Job Step Command: " + cmd); Utility.WriteLog("Job Step Command: " + cmd); step.Command = cmd; step.SubSystem = AgentSubSystem.Ssis; step.OnSuccessAction = StepCompletionAction.QuitWithSuccess; step.OnFailAction = StepCompletionAction.QuitWithFailure; step.Create(); //run the job job.Start(); Thread.Sleep(TimeSpan.FromSeconds(1)); job.Refresh(); //check the status of the job and loop while the job is running while (job.CurrentRunStatus == JobExecutionStatus.Executing) { Thread.Sleep(TimeSpan.FromSeconds(2)); job.Refresh(); } JobHistoryFilter hf = new JobHistoryFilter(); hf.JobID = job.JobID; DataTable history = js.EnumJobHistory(hf); history.TableName = "JobHistory"; System.IO.StringWriter sw = new System.IO.StringWriter(); history.WriteXml(sw); //WriteTrace(sw.ToString()); if (outcome == CompletionResult.Succeeded) { //WriteTrace("Package Execution Suceeded"); return 0; } else { } } finally //drop the job { js.DropJobByID(job.JobID); } }</parameters>
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)