public Package LoadPackage() { //Load Package from the file if (!(m_movedata.SavePackage == null) && m_movedata.SavePackage.Load) { if (String.IsNullOrEmpty(m_movedata.SavePackage.File)) { PrintOutput.PrintToOutput("Warning: No location to load the package from was supplied. Package can not be loaded."); m_p = this.BuildPackage(); } else if (!File.Exists(m_movedata.SavePackage.File)) { PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "Warning: File not found {0}", m_movedata.SavePackage.File), DERun.Debug); m_p = this.BuildPackage(); } else { PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "DE trying to load the Package {0}", m_movedata.SavePackage.File), DERun.Debug); try { m_p = app.LoadPackage(m_movedata.SavePackage.File, null); } catch (COMException cexp) { PrintOutput.PrintToError("Exception occured : " + cexp.TargetSite + cexp); throw; } } } else { m_p = this.BuildPackage(); } return(m_p); }
protected SSISModule(MainPipe pipe, string module_name, int module_id, string module_clsid) { //create SSIS component m_pipe = pipe; IDTSComponentMetaData100 comp = pipe.ComponentMetaDataCollection.New(); m_ID = comp.ID; Application app = new Application(); comp.ComponentClassID = (String.IsNullOrEmpty(module_clsid)) ? app.PipelineComponentInfos[module_name].CreationName : module_clsid; CManagedComponentWrapper dcomp = comp.Instantiate(); dcomp.ProvideComponentProperties(); //set common SSIS module properties if (module_id == 0) { comp.Name = String.Format(CultureInfo.InvariantCulture, "{0}", module_name); } else { comp.Name = String.Format(CultureInfo.InvariantCulture, "{0} - {1}", module_name, module_id); } PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "DE added {0}", comp.Name), DERun.Debug); }
protected virtual void MatchInputColumns(Dictionary <string, int> converted, bool needschema) { IDTSComponentMetaData100 comp = this.MetadataCollection; CManagedComponentWrapper dcomp = comp.Instantiate(); IDTSInput100 input = comp.InputCollection[0]; IDTSVirtualInput100 vInput = input.GetVirtualInput(); IDTSVirtualInputColumnCollection100 vColumns = vInput.VirtualInputColumnCollection; IDTSExternalMetadataColumnCollection100 exColumns = input.ExternalMetadataColumnCollection; if (exColumns != null && exColumns.Count > 0) { bool hasMatch = false; foreach (IDTSExternalMetadataColumn100 exColumn in exColumns) { int inputColId; if (converted.ContainsKey(exColumn.Name.ToLower())) { inputColId = (int)converted[exColumn.Name.ToLower()]; } else { inputColId = FindVirtualInputColumnId(vColumns, exColumn.Name); } if (inputColId == 0) { //the column wasn't found if the Id is 0, so we'll print out a message and skip this row. PrintOutput.PrintToOutput("DE could not map external column " + exColumn.Name + ". Skipping column.", DERun.Debug); } else { // create input column IDTSInputColumn100 vCol = dcomp.SetUsageType(input.ID, vInput, inputColId, DTSUsageType.UT_READONLY); // and then we'll map it to the input row. dcomp.MapInputColumn(input.ID, vCol.ID, exColumn.ID); hasMatch = true; } } if (!hasMatch) { throw new InvalidArgumentException("Unable to map input to destination"); } } //if output schema is required and not provided else if (needschema) { //PrintOutput.PrintToError("No destination columns available"); throw new InvalidArgumentException("No destination columns available"); } //otherwise use virtual inputs else { foreach (IDTSVirtualInputColumn100 vColumn in vColumns) { // create input column for all virtual input columns dcomp.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY); } } }
private bool test() { try { if (this.TableName == UNKNOWN) { throw new InvalidArgumentException("Destination TableName is empty"); } if (!(PartitionRange == null) && (PartitionRange.Min > PartitionRange.Max)) { throw new InvalidArgumentException("Invalid Destination Partition Settings"); } //ParseTableName returns a string array in the shape of [server].[db].[schema].[table] //Use the table and schema to test for existance //string[] tempTableName = ParseTableName(TableName); using (SqlConnection cn = new SqlConnection()) { //cn.ConnectionString = String.Format(System.Globalization.CultureInfo.InvariantCulture, "Server={0};Database={1};Trusted_Connection=yes", tempTableName[tempTableName.Length - 4], tempTableName[tempTableName.Length - 3]); //overide cn database if Staging and Destination are different if (this.tbl_database == UNKNOWN) { cn.ConnectionString = ConnectionString; } else { cn.ConnectionString = String.Format(System.Globalization.CultureInfo.InvariantCulture, "Server={0};Database={1};Trusted_Connection=yes", this.server, this.tbl_database); } cn.Open(); using (SqlCommand cmd = new SqlCommand(String.Format(System.Globalization.CultureInfo.InvariantCulture, "select OBJECTPROPERTY(object_id('{0}'),'IsTable')", this.tablename), cn)) { cmd.CommandTimeout = this.DBConnection.QueryTimeout; cmd.CommandType = CommandType.Text; using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (reader.GetSqlInt32(0).IsNull) { throw new InvalidTableNameException("The table/view is not found"); } this.IsView = (reader.GetInt32(0) == 0); } } } } return(true); } catch (Exception e) { PrintOutput.PrintToError( String.Format(System.Globalization.CultureInfo.InvariantCulture, "Test Destination {0} failed with messsage: {1}" , FullName, e.Message)); this.IsValid = false; return(false); } }
public static void DisplayHelp() { //::TODO:: Add help output for new parameters PrintOutput.PrintToOutput("DeltaExtractor - created by [email protected]"); PrintOutput.PrintToOutput("Pulls data from database/file and pushes to multiple database/file destinations."); PrintOutput.PrintToOutput("---------------------------------------------------------------"); PrintOutput.PrintToOutput("DeltaExtractor.exe <XML>"); PrintOutput.PrintToOutput("See Parameters.xsd for XML schema definition."); }
public bool CreateStagingTable(bool createflag) { if (!this.StagingBlock.Staging) { return(true); } try { if (string.IsNullOrEmpty(this.StagingBlock.StagingTableName)) { string[] tempTableName = ParseTableName(TableName); this.StagingBlock.StagingTableName = tempTableName[tempTableName.Length - 2] + ".staging_" + tempTableName[tempTableName.Length - 1]; } PrintOutput.PrintToOutput("Creating a Staging table " + this.StagingBlock.StagingTableName + " for the destination " + FullName, DERun.Debug); if (createflag) { using (SqlConnection cn = new SqlConnection()) { //cn.ConnectionString = String.Format(System.Globalization.CultureInfo.InvariantCulture, "Server={0};Database={1};Trusted_Connection=yes", this.DBConnection.Server, this.DBConnection.Database); cn.ConnectionString = ConnectionString; cn.Open(); using (SqlCommand cmd = new SqlCommand(String.IsNullOrEmpty(this.StagingBlock.StagingTablePrepare) ? "dbo.prc_StagingTablePrepare" : this.StagingBlock.StagingTablePrepare, cn)) { cmd.CommandTimeout = this.DBConnection.QueryTimeout; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@src", this.CanonicTableName); cmd.Parameters.Add(CreateParameter("@dst", this.StagingBlock.StagingTableName, System.Data.SqlDbType.VarChar, 255, System.Data.ParameterDirection.InputOutput)); cmd.Parameters.AddWithValue("@options", this.StagingBlock.UserOptions); cmd.ExecuteNonQuery(); this.StagingBlock.StagingTableName = cmd.Parameters["@dst"].Value.ToString(); PrintOutput.PrintToOutput("Staging table prepare has been executed with options (" + this.StagingBlock.UserOptions + "): " + this.StagingBlock.StagingTableName, DERun.Debug); } } } else { PrintOutput.PrintToOutput("Staging table prepare has been skipped: " + this.StagingBlock.StagingTableName, DERun.Debug); } return(true); } catch (Exception e) { this.StagingBlock.Staging = false; PrintOutput.PrintToError("An error occurred while trying to create a staging table for " + this.CanonicTableName + ": " + e.Message, DERun.Debug); throw (new CouldNotCreateStagingTableException("An error occurred while trying to create a staging table for " + this.CanonicTableName, e)); } }
/// <summary> /// Entry point to CDR Delta Extractor. Takes parameters defined by an XML string based /// on the parameters.XSD schema. /// </summary> /// <param name="args"></param> /// <returns></returns> public static void Main(string[] args) { Parameters parameters = new Parameters(); try { Version v = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version; string XML = args[0]; if (!XML.StartsWith("<?xml version=")) { byte[] base64ByteArr = Convert.FromBase64String(args[0]); XML = System.Text.UnicodeEncoding.Unicode.GetString(base64ByteArr); } parameters = Parameters.DeSerializefromXml(XML); //Enable ETLController loging //EventLoggerDE de = EventLoggerDE.Create(); debug = parameters.Debug; if (parameters.HeaderType == HeaderType.ETLHeader) { //de.SetEventContext(new Dictionary<string, string>() //{ // {"CS",String.Format(@"Data Source={0};Initial Catalog={1};Integrated Security=SSPI;",parameters.ETLHeader.Controller.Server,parameters.ETLHeader.Controller.Database)}, // {"Timeout",parameters.ETLHeader.Controller.QueryTimeout.ToString()}, // {"prcPrint", "dbo.prc_Print"} //}); ETLController.Connect(parameters.ETLHeader); } PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "Running DE v.{0} ({1})bit", v.ToString(), 8 * IntPtr.Size)); PrintOutput.PrintToOutput("Executing as: " + WindowsIdentity.GetCurrent().Name.ToString(), DERun.Debug); PrintOutput.PrintToOutput("DE XML: " + XML, DERun.Debug); DEController.Execute(parameters); } catch (Exception ex) { PrintOutput.PrintToError(ex.Message); Environment.Exit(1); } Environment.Exit(0); }
public bool UploadStagingTable(int RunId) { if (!this.StagingBlock.Staging) { return(true); } PrintOutput.PrintToOutput("Uploading staging table " + this.StagingBlock.StagingTableName + " to destination " + this.CanonicTableName, DERun.Debug); //if nothing has been staged, then the table can't be upserted. if (string.IsNullOrEmpty(this.StagingBlock.StagingTableName)) { PrintOutput.PrintToError("Staging table has not been created. There must be a staging table in order to perform an upsert."); return(false); } try { using (SqlConnection cn = new SqlConnection()) { //cn.ConnectionString = String.Format(System.Globalization.CultureInfo.InvariantCulture, "Server={0};Database={1};Trusted_Connection=yes", this.DBConnection.Server, this.DBConnection.Database); cn.ConnectionString = ConnectionString; cn.Open(); using (SqlCommand cmd = new SqlCommand(String.IsNullOrEmpty(this.StagingBlock.StagingTableUpload) ? "dbo.prc_StagingTableUpload" : this.StagingBlock.StagingTableUpload, cn)) { cmd.CommandTimeout = this.DBConnection.QueryTimeout; cmd.CommandType = CommandType.StoredProcedure; //cmd.Parameters.Add(CDRDBController.CreateParameter("@RTC", retval, System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue)); cmd.Parameters.AddWithValue("@src", this.StagingBlock.StagingTableName); cmd.Parameters.AddWithValue("@dst", this.CanonicTableName); cmd.Parameters.AddWithValue("@RunId", RunId); cmd.Parameters.AddWithValue("@options", this.StagingBlock.UserOptions); cmd.ExecuteNonQuery(); this.StagingBlock.StagingTableName = cmd.Parameters["@dst"].Value.ToString(); PrintOutput.PrintToOutput("Staging table upload has been executed with options (" + this.StagingBlock.UserOptions + "): " + this.TableName, DERun.Debug); } } return(true); } catch (Exception e) { PrintOutput.PrintToError("An error occurred while performing the Upsert for " + this.CanonicTableName + ": " + e.Message, DERun.Debug); throw (new CouldNotPerformUpsert("An error occurred while performing the Upsert for " + this.CanonicTableName + ": " + e.Message, e)); } }
private static void ExecPackageRun(Parameters p) { System.Diagnostics.Debug.Assert(p != null); RunPackage action = p.RunPackage; //Check the Source if (action == null || String.IsNullOrEmpty(action.File)) { throw new InvalidArgumentException("Error: No location to load the package from was supplied. Package can not be loaded."); } else if (!File.Exists(action.File)) { throw new InvalidArgumentException(String.Format(CultureInfo.InvariantCulture, "Error: File not found {0}", action.File)); } Application app = new Application(); Package pkg = new Package(); //load the package if (!File.Exists(action.File)) { throw new InvalidArgumentException(String.Format(CultureInfo.InvariantCulture, "Error: File not found {0}", action.File)); } else { PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "DE trying to load the Package {0}", action.File), DERun.Debug); try { pkg.LoadFromXML(action.File, null); ExecutePackageWithEvents(pkg); } catch (COMException cexp) { PrintOutput.PrintToError("Exception occured : " + cexp.TargetSite + cexp); throw; } } //int rowCount = Convert.ToInt32(pkg.Variables["RowCount"].Value, CultureInfo.InvariantCulture); //PrintOutput.PrintToOutput("DE extracted " + rowCount.ToString() + " rows from the Source."); //PrintOutput.PrintToOutput("DE Package completed."); //ETLController.CounterSet("RowsExtracted", rowCount.ToString()); }
private void SetPartitionFunctionInput(string ColumnName) { IDTSComponentMetaData100 comp = this.MetadataCollection; CManagedComponentWrapper dcomp = comp.Instantiate(); IDTSInput100 input = comp.InputCollection[0]; IDTSVirtualInput100 vInput = input.GetVirtualInput(); IDTSVirtualInputColumnCollection100 vColumns = vInput.VirtualInputColumnCollection; int inputColId = FindVirtualInputColumnId(vColumns, ColumnName); if (inputColId == 0) { //the column wasn't found if the Id is 0, so we'll print out a message and skip this row. PrintOutput.PrintToError("DE Could not find partition function input column " + ColumnName + " in the source"); } else { dcomp.SetUsageType(input.ID, vInput, inputColId, DTSUsageType.UT_READONLY); } }
public bool TruncateDestinationTable() { //if (!this.IsValid) { return false; } if (this.StagingBlock.Staging) { return(true); } try { string query = ""; if (this.IsView) { PrintOutput.PrintToOutput("Deleting view data " + FullName, DERun.Debug); query = "delete from " + this.CanonicTableName; } else { PrintOutput.PrintToOutput("Truncating table data " + FullName, DERun.Debug); query = "truncate table " + this.CanonicTableName; } using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = ConnectionString; cn.Open(); using (SqlCommand cmd = new SqlCommand(query, cn)) { cmd.CommandTimeout = this.DBConnection.QueryTimeout; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } return(true); } catch (Exception e) { this.StagingBlock.Staging = false; PrintOutput.PrintToError("Failed to truncate table " + this.FullName + ": " + e.Message, DERun.Debug); throw (new CouldNotCreateStagingTableException("Failed to truncate table " + this.CanonicTableName, e)); } }
private static void ExecutePackageWithEvents(Package pkg) { // Throw an exception if we get an error PrintOutput.PrintToOutput("Executing DE Package..."); SSISEvents ev = new SSISEvents(); DTSExecResult rc = pkg.Execute(null, null, ev, null, null); if (rc != DTSExecResult.Success) { PrintOutput.PrintToError("Error: the DE failed to complete successfully."); StringBuilder dtserrors = new StringBuilder(); foreach (DtsError error in pkg.Errors) { PrintOutput.PrintToError(error.Description); dtserrors.AppendLine(error.Description); } throw new UnexpectedSsisException(dtserrors.ToString()); //return false; } }
public bool UploadStagingTable(int RunId) { PrintOutput.PrintToOutput("Not implemented", DERun.Debug); return(true); }
public bool CreateStagingTable(bool createflag) { PrintOutput.PrintToOutput("Not implemented", DERun.Debug); return(true); }
public bool TruncateDestinationTable() { PrintOutput.PrintToOutput("Not implemented", DERun.Debug); return(true); }
private void HandleError(string type, int errorCode, string subComponent, string description) { PrintOutput.PrintToError(String.Format(CultureInfo.InvariantCulture, "[{0}-{1}] {2}: {3}", type, errorCode, subComponent, description), DERun.Debug); }
private void HandleEvent(string type, string subComponent, string description) { PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "[{0}] {1}: {2}", type, subComponent, description), DERun.Debug); }
public override bool OnError(DtsObject source, int errorCode, string subComponent, string description, string helpFile, int helpContext, string idofInterfaceWithError) { // TODO: Add custom code to handle the event. PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "Error: {0} - {1}", errorCode, description), DERun.Debug); return(true); }
public override void OnWarning(DtsObject source, int warningCode, string subComponent, string description, string helpFile, int helpContext, string idofInterfaceWithError) { // TODO: Add custom code to handle the event. PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "Warning: {0} - {1}", warningCode, description), DERun.Debug); }
//public override void OnPreExecute(Executable exec, ref bool fireAgain) //{ // // TODO: Add custom code to handle the event. // PrintOutput.PrintToOutput("Package Execution phase started... ",DERun.Debug); //} //public override void OnPreValidate(Executable exec, ref bool fireAgain) //{ // // TODO: Add custom code to handle the event. // PrintOutput.PrintToOutput("Validation phase started...", DERun.Debug); //} //public override void OnPostValidate(Executable exec, ref bool fireAgain) //{ // // TODO: Add custom code to handle the event. // PrintOutput.PrintToOutput("Validation phase finished...", DERun.Debug); //} public override void OnInformation(DtsObject source, int informationCode, string subComponent, string description, string helpFile, int helpContext, string idofInterfaceWithError, ref bool fireAgain) { // TODO: Add custom code to handle the event. PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "Information: {0} - {1}", informationCode, description), DERun.Debug); }
private Package BuildPackage() { try { PrintOutput.PrintToOutput("DE building the Package...", DERun.Debug); //SSISEvents ev = new SSISEvents(); //m_p.DesignEvents = ev; Executable ex = m_p.Executables.Add("STOCK:PipelineTask"); MainPipe pipe = (MainPipe)(((TaskHost)ex).InnerObject); // Set the IDTSComponentEvent handler to capture the details from any // COMExceptions raised during package generation SSISEventHandler events = new SSISEventHandler(); pipe.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents); // Add variable to point to staging area root dsv dsv = null; if (!String.IsNullOrEmpty(m_movedata.StagingAreaRoot)) { m_p.Variables.Add("StagingAreaRoot", true, "", m_movedata.StagingAreaRoot); dsv = new dsv(m_movedata.StagingAreaRoot); } // Add variable RowCount m_p.Variables.Add("RowCount", false, "", 0); IDTSComponentMetaData100 src = null; //create FlatFile source if (m_movedata.DataSource.Type == SourceType.FlatFile) { // use dsv as external metadata bool bFound = false; if (dsv != null) { bFound = dsv.FindTable(m_movedata.DataSource.FlatFileSource.CustomProperties.StagingAreaTableName); if (!bFound) { PrintOutput.PrintToOutput("Warning: DsvSchemaTable is not found", DERun.Debug); //throw new DsvTableNotFound(m_movedata.StagingAreaRoot,m_movedata.DataSource.FlatFileSource.CustomProperties.StagingAreaTableName); } } //Connection manager ConnectionManager cm = m_p.Connections.Add("FLATFILE"); Dictionary <string, MyColumn> colCollection = (bFound) ? dsv.ColumnCollection : null; SSISFlatFileConnection.ConfigureConnectionManager(cm, m_movedata.DataSource.FlatFileSource.CustomProperties.FlatFileConnectionProperties, colCollection); SSISFlatFileSource ssissource = new SSISFlatFileSource(m_movedata.DataSource.FlatFileSource, pipe, cm); src = ssissource.MetadataCollection; } //create Excel source else if (m_movedata.DataSource.Type == SourceType.Excel) { //Connection manager ConnectionManager cm = m_p.Connections.Add("EXCEL"); SSISExcelSource ssissource = new SSISExcelSource(m_movedata.DataSource.ExcelSource, pipe, cm); src = ssissource.MetadataCollection; } //create SharePoint source else if (m_movedata.DataSource.Type == SourceType.SPList) { SSISSharePointSource ssissource = new SSISSharePointSource(m_movedata.DataSource.SharePointSource, pipe); src = ssissource.MetadataCollection; } //create OleDb source else if (m_movedata.DataSource.Type == SourceType.OleDb) { //Add variable for SQL query if access mode is 3 m_p.Variables.Add("srcSelect", true, "", m_movedata.DataSource.OleDbSource.CustomProperties.SqlCommand); //Connection manager ConnectionManager cm = m_p.Connections.Add("OLEDB"); SSISOleDbSource ssissource = new SSISOleDbSource(m_movedata.DataSource.OleDbSource, pipe, cm); src = ssissource.MetadataCollection; } //create AdoNet source else if (m_movedata.DataSource.Type == SourceType.AdoNet) { //Connection manager ConnectionManager cm = m_p.Connections.Add("ADO.NET"); SSISAdoNetSource ssissource = new SSISAdoNetSource(m_movedata.DataSource.AdoNetSource, pipe, cm); src = ssissource.MetadataCollection; } //create Odbc source else if (m_movedata.DataSource.Type == SourceType.Odbc) { //Connection manager ConnectionManager cm = m_p.Connections.Add("ODBC"); SSISOdbcSource ssissource = new SSISOdbcSource(m_movedata.DataSource.OdbcSource, pipe, cm); src = ssissource.MetadataCollection; } else { throw new UnknownSourceType(); } //create and connect rowcount to the source SSISRowCount ssiscount = new SSISRowCount(pipe, src); src = ssiscount.MetadataCollection; if (m_movedata.Partition == null || String.IsNullOrEmpty(m_movedata.Partition.Function) || m_movedata.Partition.Function == "NONE") { //create and connect multicast to the rowcount SSISMultiCast ssissplit = new SSISMultiCast(pipe, src); src = ssissplit.MetadataCollection; } else { //create and connect partition data custom component SSISPartitionColumn ssispcol = new SSISPartitionColumn(pipe, src, m_movedata); src = ssispcol.MetadataCollection; //create and connect a partition splitter SSISPartitionSplit ssissplit = new SSISPartitionSplit(pipe, src, m_movedata); src = ssissplit.MetadataCollection; } //connect none partition destinations to multicast //connect partition destinations to partition splitter CManagedComponentWrapper dsrc = src.Instantiate(); foreach (object odst in m_movedata.DataDestination.Destinations) { //FlatFile Destinations if (((IDeDestination)odst).Type == DestinationType.FlatFile) { FlatFileDestination dst = (FlatFileDestination)odst; // use dsv as external metadata bool bFound = false; if (dsv != null) { bFound = dsv.FindTable(dst.CustomProperties.StagingAreaTableName); if (!bFound) { PrintOutput.PrintToOutput("Warning: DsvSchemaTable is not found", DERun.Debug); //throw new DsvTableNotFound(m_movedata.StagingAreaRoot, dst.CustomProperties.StagingAreaTableName); } } IDTSOutput100 output = ConfigureOutput(src, dst, dsrc); //Connection manager ConnectionManager cm = m_p.Connections.Add("FLATFILE"); //cm.Name = String.Format(CultureInfo.InvariantCulture, "FlatFile Destination Connection Manager {0}", output.ID); Dictionary <string, MyColumn> colCollection = (bFound) ? dsv.ColumnCollection : getColumnCollectionFromPipe(src); SSISFlatFileConnection.ConfigureConnectionManager(cm, dst.CustomProperties.FlatFileConnectionProperties, colCollection); SSISFlatFileDestination ssisdest = new SSISFlatFileDestination(dst, pipe, src, output.ID, cm); } // OleDb Destinations if (((IDeDestination)odst).Type == DestinationType.OleDb) { OleDbDestination dst = (OleDbDestination)odst; IDTSOutput100 output = ConfigureOutput(src, dst, dsrc); //Connection manager ConnectionManager cm = m_p.Connections.Add("OLEDB"); //cm.Name = String.Format(CultureInfo.InvariantCulture, "OLEDB Destination Connection Manager {0}", output.ID); SSISOleDbDestination ssisdest = new SSISOleDbDestination(dst, pipe, src, output.ID, cm); } //ExcelDestinations if (((IDeDestination)odst).Type == DestinationType.Excel) { ExcelDestination dst = (ExcelDestination)odst; IDTSOutput100 output = ConfigureOutput(src, dst, dsrc); //Connection manager ConnectionManager cm = m_p.Connections.Add("EXCEL"); //cm.Name = String.Format(CultureInfo.InvariantCulture, "Excel Destination Connection Manager {0}", output.ID); SSISExcelDestination ssisdest = new SSISExcelDestination(dst, pipe, src, output.ID, cm); } // Create SharePointDestinations if (((IDeDestination)odst).Type == DestinationType.SPList) { SharePointDestination dst = (SharePointDestination)odst; IDTSOutput100 output = ConfigureOutput(src, dst, dsrc); SSISSharePointDestination ssisdest = new SSISSharePointDestination(dst, pipe, src, output.ID); } // Ado Net Destinations if (((IDeDestination)odst).Type == DestinationType.AdoNet) { AdoNetDestination dst = (AdoNetDestination)odst; IDTSOutput100 output = ConfigureOutput(src, dst, dsrc); //Connection manager ConnectionManager cm = m_p.Connections.Add("ADO.NET"); //cm.Name = String.Format(CultureInfo.InvariantCulture, "ADONET Destination Connection Manager {0}", output.ID); SSISAdoNetDestination ssisdest = new SSISAdoNetDestination(dst, pipe, src, output.ID, cm); } // Odbc Destinations if (((IDeDestination)odst).Type == DestinationType.Odbc) { OdbcDestination dst = (OdbcDestination)odst; IDTSOutput100 output = ConfigureOutput(src, dst, dsrc); //Connection manager ConnectionManager cm = m_p.Connections.Add("ODBC"); //cm.Name = String.Format(CultureInfo.InvariantCulture, "ODBC Destination Connection Manager {0}", output.ID); SSISOdbcDestination ssisdest = new SSISOdbcDestination(dst, pipe, src, output.ID, cm); } // SqlBulk Destinations if (((IDeDestination)odst).Type == DestinationType.SqlBulk) { SqlBulkDestination dst = (SqlBulkDestination)odst; IDTSOutput100 output = ConfigureOutput(src, dst, dsrc); //Connection manager ConnectionManager cm = m_p.Connections.Add("OLEDB"); //cm.Name = String.Format(CultureInfo.InvariantCulture, "OLEDB Destination Connection Manager {0}", output.ID); SSISSqlBulkDestination ssisdest = new SSISSqlBulkDestination(dst, pipe, src, output.ID, cm); } } PrintOutput.PrintToOutput("DE Package is ready", DERun.Debug); } //catch (COMException cexp) catch (Exception cexp) { PrintOutput.PrintToError("Exception occured : " + cexp.TargetSite + cexp); StringBuilder dtserrors = new StringBuilder(); foreach (DtsError error in m_p.Errors) { //PrintOutput.PrintToError(error.Description); dtserrors.AppendLine(error.Description); } throw new UnexpectedSsisException(dtserrors.ToString()); } finally { //Save the Package to XML if (!(m_movedata.SavePackage == null) && m_movedata.SavePackage.Save) { if (String.IsNullOrEmpty(m_movedata.SavePackage.File)) { PrintOutput.PrintToError("No location to save the package was supplied. Package will not be saved to XML."); } else { app.SaveToXml(m_movedata.SavePackage.File, m_p, null); } } } return(m_p); }
public bool Test() { PrintOutput.PrintToOutput("Not implemented", DERun.Debug); return(true); }
private static void MoveDataRun(Parameters p) { System.Diagnostics.Debug.Assert(p != null); MoveData action = p.MoveData; //Check the Source if (action.DataSource.Type == SourceType.Unknown) { throw new UnknownSourceType(); } PrintOutput.PrintToOutput(action.DataSource.Description, p.Debug); //Check destinations int numValidDestinations = action.DataDestination.Test(); if (numValidDestinations == 0) { throw new InvalidDestinations("Error: No Valid destinations found"); } if (numValidDestinations != action.DataDestination.Destinations.Count) { throw new InvalidDestinations("Error: Invalid destinations found"); } //create and configure the package DESSISPackage Extractor = new DESSISPackage(action); Package pkg = Extractor.LoadPackage(); if (pkg == null) { throw new DeltaExtractorBuildException("Failed to Load or Build the SSIS Package"); } ExecutePackageWithEvents(pkg); int rowCount = Convert.ToInt32(pkg.Variables["RowCount"].Value, CultureInfo.InvariantCulture); PrintOutput.PrintToOutput("DE extracted " + rowCount.ToString() + " rows from the Source."); PrintOutput.PrintToOutput("DE Package completed."); ETLController.CounterSet("RowsExtracted", rowCount.ToString()); //if this is a staging extract, then call the upload sproc for each DB Destination //staging value defines which upsert type to use //IEnumerable<object> res = action.DataDestination.Destinations.Where(d => ((IDeDestination)d).Type == DestinationType.OleDb); foreach (object odest in action.DataDestination.Destinations) { IDeDestination dest = (IDeDestination)odest; if (dest.StagingBlock != null) { if (dest.StagingBlock.Staging) { if (dest.DbSupportObject == null) { throw new DeltaExtractorBuildException("Staging support is not available for this destination"); } IDeStagingSupport supp = (IDeStagingSupport)dest.DbSupportObject; if (String.IsNullOrEmpty(dest.StagingBlock.StagingTableName)) { if (!supp.CreateStagingTable(false)) { throw new CouldNotCreateStagingTableException(dest.StagingBlock.StagingTableName); } } if (!supp.UploadStagingTable(p.RunID)) { throw new CouldNotUploadStagingTableException(dest.StagingBlock.StagingTableName); } } } } }
virtual public bool Test() { PrintOutput.PrintToOutput("Not implemented", DERun.Debug); this.IsValid = true; return(true); }
public static void ConfigureConnectionManager(ConnectionManager cm, SSISFlatFileConnectionProperties prop, Dictionary <string, MyColumn> columnCollection) { cm.Description = "connect to a flat file"; PrintOutput.PrintToOutput("DE added FlatFile connection to " + prop.ConnectionString, DERun.Debug); //set connection properties mwrt.IDTSConnectionManagerFlatFile100 fcm = cm.InnerObject as mwrt.IDTSConnectionManagerFlatFile100; cm.Properties["ConnectionString"].SetValue(cm, prop.ConnectionString); fcm.CodePage = prop.CodePage; fcm.Unicode = prop.Unicode; fcm.ColumnNamesInFirstDataRow = prop.ColumnNamesInFirstDataRow; fcm.DataRowsToSkip = prop.DataRowsToSkip; fcm.Format = prop.Format; fcm.HeaderRowDelimiter = prop.HeaderRowDelimiter; fcm.HeaderRowsToSkip = prop.HeaderRowsToSkip; fcm.RowDelimiter = prop.RecordDelimiter; if (prop.TextQualifier != null) { fcm.TextQualifier = prop.TextQualifier; } mwrt.IDTSConnectionManagerFlatFileColumn100 fColumn; mwrt.IDTSName100 fName; if (columnCollection != null && columnCollection.Count > 0) { //define input column int i = 1; foreach (MyColumn dsvCol in columnCollection.Values) { fColumn = fcm.Columns.Add(); fColumn.ColumnType = fcm.Format; fColumn.DataType = dsvCol.DataType; fColumn.DataPrecision = dsvCol.Precision; fColumn.TextQualified = (fcm.TextQualifier != null && (dsvCol.DataType == mwrt.DataType.DT_STR || dsvCol.DataType == mwrt.DataType.DT_WSTR)); fColumn.ColumnDelimiter = (columnCollection.Count == i) ? prop.RecordDelimiter : prop.ColumnDelimiter; //fColumn.ColumnDelimiter = (dsv.ColumnCollection.Count == i) ? "\r\n" : "\t"; fColumn.DataScale = dsvCol.Scale; fColumn.MaximumWidth = dsvCol.Length; fName = (mwrt.IDTSName100)fColumn; fName.Name = dsvCol.Name; i++; } } else if (prop.ColumnNamesInFirstDataRow) { //use file header string header = string.Empty; using (StreamReader sr = File.OpenText(cm.ConnectionString)) { for (int l = 0; l <= fcm.HeaderRowsToSkip; l++) { header = sr.ReadLine(); } sr.Close(); } string[] del = new string[] { prop.ColumnDelimiter }; string[] cols = header.Split(del, StringSplitOptions.None); int i = 1; foreach (string col in cols) { fColumn = fcm.Columns.Add(); fColumn.ColumnType = fcm.Format; fColumn.DataType = (fcm.Unicode) ? mwrt.DataType.DT_WSTR : mwrt.DataType.DT_STR; fColumn.TextQualified = (fcm.TextQualifier != null); fColumn.ColumnDelimiter = (cols.Length == i) ? prop.RecordDelimiter : prop.ColumnDelimiter; //fColumn.ColumnDelimiter = (dsv.ColumnCollection.Count == i) ? "\r\n" : "\t"; fColumn.MaximumWidth = 255; fName = (mwrt.IDTSName100)fColumn; fName.Name = col; i++; } } }