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