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); }
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 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; } }
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 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); }