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