/** * Stored Proc Activity - usp_UpdateToArchiveStatus to ARCHIVED */ public Activity create_Activity_Archive_3(int controlDetailID, String fileName, int i) { Console.WriteLine("Creating/Executing Stored proc: " + DualLoadConfig.ACTIVITY_ARCHIVE_3 + "_" + i + " with controlDetailID= " + controlDetailID); IDictionary <string, string> sprocParams = new Dictionary <string, string>(); sprocParams.Add("@FileName", fileName); sprocParams.Add("@ControlDetailId", controlDetailID.ToString()); Activity activity = new Activity(); List <ActivityOutput> activityOutputs = new List <ActivityOutput>(); ActivityOutput activityOutput = new ActivityOutput(); activityOutput.Name = DualLoadConfig.DATASET_ARCHIVE_1_SQLDUMMY + "_" + i; activityOutputs.Add(activityOutput); SqlServerStoredProcedureActivity sqlserverStoredProcActivity = new SqlServerStoredProcedureActivity(); sqlserverStoredProcActivity.StoredProcedureName = "usp_UpdateToArchiveStatus"; sqlserverStoredProcActivity.StoredProcedureParameters = sprocParams; activity.Name = DualLoadConfig.ACTIVITY_ARCHIVE_3 + "_" + i; activity.Outputs = activityOutputs; activity.TypeProperties = sqlserverStoredProcActivity; return(activity); }
/** * Stored Proc Activity - usp_RecordFilesToBeProcessed * Add record to the ETLControlDetail table for the files that need to be processed */ public Activity create_Activity_Init_3(int controlID, String filePath, int i) { Console.WriteLine("Creating " + DualLoadConfig.ACTIVITY_INIT_3 + "_" + i); IDictionary <string, string> sprocParams = new Dictionary <string, string>(); //sprocParams.Add("@ETLControlID", "1"); //sprocParams.Add("@FileName", "Z:\\DimEmployee\\DimEmployee1.csv"); sprocParams.Add("@ETLControlID", controlID.ToString()); sprocParams.Add("@FileName", filePath); Activity activity = new Activity(); List <ActivityOutput> activityOutputs = new List <ActivityOutput>(); ActivityOutput activityOutput = new ActivityOutput(); activityOutput.Name = DualLoadConfig.DATASET_INIT_SQLDUMMY + "_" + i; activityOutputs.Add(activityOutput); SqlServerStoredProcedureActivity sqlserverStoredProcActivity = new SqlServerStoredProcedureActivity(); sqlserverStoredProcActivity.StoredProcedureName = "dbo.usp_RecordFilesToBeProcessed"; sqlserverStoredProcActivity.StoredProcedureParameters = sprocParams; activity.Name = DualLoadConfig.ACTIVITY_INIT_3 + "_" + i; activity.Outputs = activityOutputs; activity.TypeProperties = sqlserverStoredProcActivity; return(activity); }
/** * Stored Proc Activity - usp_UpdateControlDetailStatus to COMPLETE */ public Activity create_Activity_LoadProcess_5(string ETlControlID, int i) { Console.WriteLine("Creating/Executing Stored proc: " + DualLoadConfig.ACTIVITY_LOADPROCESS_5 + "_" + i + " with ETlControlID= " + ETlControlID); IDictionary <string, string> sprocParams = new Dictionary <string, string>(); sprocParams.Add("@ControlProcess", "1"); sprocParams.Add("@Id", ETlControlID); sprocParams.Add("@Status", DualLoadConfig.STATUS_COMPLETE.ToString()); Activity activity = new Activity(); List <ActivityOutput> activityOutputs = new List <ActivityOutput>(); ActivityOutput activityOutput = new ActivityOutput(); activityOutput.Name = DualLoadConfig.DATASET_LOAD_2_SQLDUMMY + "_" + i; activityOutputs.Add(activityOutput); SqlServerStoredProcedureActivity sqlserverStoredProcActivity = new SqlServerStoredProcedureActivity(); sqlserverStoredProcActivity.StoredProcedureName = "usp_UpdateControlDetailStatus"; sqlserverStoredProcActivity.StoredProcedureParameters = sprocParams; activity.Name = DualLoadConfig.ACTIVITY_LOADPROCESS_5 + "_" + i; activity.Outputs = activityOutputs; activity.TypeProperties = sqlserverStoredProcActivity; return(activity); }
public override Activity ToSdkObject() { var activity = new SqlServerStoredProcedureActivity(this.Name, this.StoredProcedureName); this.StoredProcedureParameters?.ForEach(item => activity.StoredProcedureParameters.Add(item)); activity.LinkedServiceName = this.LinkedServiceName; activity.Policy = this.Policy?.ToSdkObject(); SetProperties(activity); return(activity); }
private static void crearPipeSSIS(DataFactoryManagementClient client) { //string pipeName = "CallSP_SSIS"; //List<Activity> la = new List<Activity>(); //CopyActivity ca = new CopyActivity(); //ca.Name = "CopyParaLlamarSSIS"; //ca.Source = new SqlSource(null, 3, null, "EXEC sp_LlamarSSIS_Muestra; SELECT 1 as Ayuda"); //ca.Sink = new SqlSink(); //var inp = new List<DatasetReference>(); //var dr = new DatasetReference("DummyDatasetForSSIS"); //inp.Add(dr); //ca.Inputs = inp; //var outp = new List<DatasetReference>(); //var drO = new DatasetReference("DummyDatasetForSSIS"); //outp.Add(drO); //ca.Outputs = outp; //la.Add(ca); //PipelineResource pipe = new PipelineResource(); //pipe.Activities = la; //client.Pipelines.CreateOrUpdate(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, pipeName, pipe); //Console.Write("Pipeline " + pipeName + " creado. A continuación se intentará ejecutarlo..\n"); //Console.WriteLine("\nCreating pipeline run..."); //CreateRunResponse runResponse = client.Pipelines.CreateRunWithHttpMessagesAsync(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, pipeName).Result.Body; //Console.WriteLine("Pipeline run ID: " + runResponse.RunId); //Console.WriteLine("Checking pipeline run status..."); //PipelineRun pipelineRun; //while (true) //{ // pipelineRun = client.PipelineRuns.Get(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, runResponse.RunId); // Console.WriteLine("Status: " + pipelineRun.Status); // if (pipelineRun.Status == "InProgress") // System.Threading.Thread.Sleep(15000); // else // break; //} PipelineResource pipe = new PipelineResource(); SqlServerStoredProcedureActivity sp = new SqlServerStoredProcedureActivity("Llamar_SP", "dbo.sp_Call_SSISPack_Siniestro"); List <Activity> la = new List <Activity>(); sp.LinkedServiceName = new LinkedServiceReference(DatosGrales.linkedServiceSSIS); la.Add(sp); pipe.Activities = la; client.Pipelines.CreateOrUpdate(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, "SSIS_Siniestro", pipe); // ESTO ERA PARA LLAMAR A UN SP, NO FUNCIONO ASIQUE VAMOS CON COPY ACTIVITY // //List<Activity> la = new List<Activity>(); //var callSP = new SqlServerStoredProcedureActivity(); //callSP.Name = "CallSP_Activity"; //callSP.LinkedServiceName = new LinkedServiceReference(DatosGrales.linkedServiceSSIS); //callSP.StoredProcedureName = "dbo.sp_LlamarSSIS_Muestra"; //IDictionary<string, StoredProcedureParameter> dictionary = new Dictionary<string, StoredProcedureParameter>(); //dictionary.Add("param1", new StoredProcedureParameter(1, "int")); //callSP.StoredProcedureParameters = dictionary; //callSP.Description = "Activity que llama al SP que llama al paquete de SSIS"; //IDictionary<string, object> dic = new Dictionary<string, object>(); //dic.Add("prop1", null); //callSP.AdditionalProperties = dic; //la.Add(callSP); //PipelineResource pipe = new PipelineResource(); //pipe.Activities = la; //pipe.AdditionalProperties = null; }