/// <summary>
/// Fix incorporated as suggessted in http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/974c43ae-0e2f-48a5-a75c-cb2ddbbdb425
/// </summary>
/// <param name="absolutePackagePath"></param>
/// <param name="overwrite"></param>
/// <param name="packagePassword"></param>
private void SaveWithLayoutWithWorkAround(string absolutePackagePath, bool overwrite, string packagePassword)
{
string strXML = default(string);
ssisPackage.SaveToXML(out strXML, null);
//remove all the layout information
while (strXML.IndexOf("<DTS:PackageVariable>") > -1)
{
strXML = strXML.Remove(strXML.IndexOf("<DTS:PackageVariable>"), strXML.IndexOf("</DTS:PackageVariable>") - strXML.IndexOf("<DTS:PackageVariable>") + 22);
}
Package pkg2 = new Package();
if (!string.IsNullOrEmpty(packagePassword))
{
pkg2.PackagePassword = packagePassword;
}
pkg2.LoadFromXML(strXML, null);
string tempName = absolutePackagePath;
if (!overwrite)
{
tempName = tempName.Insert(tempName.LastIndexOf(".dtsx", StringComparison.InvariantCultureIgnoreCase), "-Mod");
}
ssisApplication.SaveToXml(tempName, pkg2, null);
}
private void Save()
{
_DTSApplication.UpdatePackage = true;
_DTSApplication.UpdateObjects = true;
DirectoryInfo packageDirectoryInfo = System.IO.Directory.CreateDirectory(ProjectFolder);
_DTSApplication.SaveToXml(PackagePath, DTSPackage, null);
ProjectManager.Save();
MessageEngine.Global.Trace(Severity.Alert, "Saved DTS package {0}", PackagePath);
}
private void Save()
{
try
{
_DTSApplication.UpdatePackage = true;
_DTSApplication.UpdateObjects = true;
Directory.CreateDirectory(PackageFolder);
_DTSApplication.SaveToXml(PackagePath, DtsPackage, null);
SsisProject.Save();
}
catch (Exception)
{
MessageEngine.Trace(AstNamedNode, Severity.Error, "V0101", "Failed to write package {0}. Files may be locked by another process.", Name);
}
}
public void Save()
{
_DTSApplication.UpdatePackage = true;
_DTSApplication.UpdateObjects = true;
///TODO: vsabella: move this out to a common utility function
string qualifiedProjectPath = QualifiedProjectPath;
DirectoryInfo packageDirectoryInfo = System.IO.Directory.CreateDirectory(qualifiedProjectPath);
qualifiedProjectPath = packageDirectoryInfo.FullName
+ _packageName
+ Resources.ExtensionDTSXProjectFile;
_DTSApplication.SaveToXml(qualifiedProjectPath, _package, null);
_projectManager.Save(QualifiedProjectPath);
Message.Trace(Severity.Alert, "Saved DTS package {0}", qualifiedProjectPath);
}
public void ExecutePackage()
{
try
{
if (this.projectItem.DTE.Mode == vsIDEMode.vsIDEModeDebug)
{
MessageBox.Show("Please stop the debugger first.");
return;
}
lTickerCounter = 0;
ExecutionCancelled = false;
this.lblStatus.Text = "Status: Preparing Temporary Package";
RefreshProjectAndPackageProperties();
this.modifiedPackage = ssisApp.LoadPackage(projectItem.get_FileNames(0), null);
this.modifiedPackagePath = System.IO.Path.GetTempFileName();
this.logFilePath = System.IO.Path.GetTempFileName();
SetupCustomLogging(this.modifiedPackage, this.logFilePath);
ssisApp.SaveToXml(this.modifiedPackagePath, this.modifiedPackage, null);
eventParser = new DtsPerformanceLogEventParser(this.modifiedPackage);
//setup Process object to call the dtexec EXE
process = new System.Diagnostics.Process();
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardError = false;
process.StartInfo.RedirectStandardOutput = false;
process.StartInfo.WorkingDirectory = System.IO.Directory.GetCurrentDirectory(); //inherit the working directory from the current BIDS process (so that relative dtsConfig paths will work)
process.StartInfo.CreateNoWindow = true;
process.StartInfo.FileName = "\"" + this.dtexecPath + "\"";
process.StartInfo.Arguments = "/Rep N /F \"" + this.modifiedPackagePath + "\"";
if (!string.IsNullOrEmpty(this.packagePassword))
{
process.StartInfo.Arguments += " /Decrypt \"" + this.packagePassword + "\"";
}
process.Start();
timer1.Enabled = true;
timer1.Start();
logFileLoader = new DtsTextLogFileLoader(this.logFilePath);
//TODO: capture perfmon: SQL Server:SSISPipeline:Buffers Spooled
this.dtsStatisticsTrendGrid1.AddNewColumnOnNextDataBinding = true;
this.iDtsGanttGridRowDataBindingSource.DataSource = eventParser.GetAllDtsGanttGridRowDatas();
this.ganttGrid.Refresh();
this.StopButton.Enabled = true;
this.StartButton.Enabled = false;
this.lblStatus.Text = "Status: Executing";
this.menuGantt.Visible = true;
this.menuGrid.Visible = true;
this.menuTrend.Visible = true;
if (this.dtsPipelineBreakdownGrid.Visible)
{
SwitchToGanttGridMenuClicked(null, null);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
}
}
static void Main(string[] args)
{
// Dont try and make sense of any of this code....I still don't get it....this was a 2 day hack....seriously, rewrite it if you want to make it into a useful tool
// WARNING: I catch general exceptions. TA-DA!!
int result = -2;
OleDbConnection conn = null;
OleDbCommand entire_excel_file = null;
OleDbDataReader config_data_reader = null;
// truncate bobby tables
System.IO.StreamWriter bt = new StreamWriter("bobby_tables.sql");
bt.Close();
// populate the variables.
num_cols = Enum.GetValues(typeof(ConfigColumnNames)).Length;
//args = new string[] { "", @"C:\Users\koln01\Desktop\ESPN\AD_Scope\pip_config.xls" };
app = new Microsoft.SqlServer.Dts.Runtime.Application();
// parameter check, pretty lame one at that.
if (args.Length != 1)
{
Console.WriteLine("This program takes a single argument. The path to an Excel (xlsx) file with config parameters");
System.Environment.Exit(0);
}
//query the excel file to get the entire result set so we can traverse it.
string config_cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + args[0] + ";Extended Properties=\"Excel 12.0;HDR=YES\";";
try
{
conn = new OleDbConnection(config_cs);
conn.Open();
}
catch (Exception)
{
Console.WriteLine("There was an error opening the connection to the excel file you gave, make sure you gave the correct path to the correct config");
Console.WriteLine("This is the path I have: {0}", args[0]);
Console.WriteLine("IF that is the correct path, Make sure there isn't a lock on the file (ie. it is open by another program)");
System.Environment.Exit(0);
}
try
{
entire_excel_file = new OleDbCommand("select * from [PIP$] where TableName is not NULL", conn);
config_data_reader = entire_excel_file.ExecuteReader();
}
catch (Exception)
{
Console.WriteLine("I tried to query all of the info in PIP and I couldn't.");
Console.WriteLine("Do you have a tab named 'PIP' in the config file? PS: no trailing spaces or hidden chars?");
Console.WriteLine("Do you have a header called 'TableName'? PS: no trailing spaces or hidden chars?");
System.Environment.Exit(0);
}
//begin the traversal of the excel file. Keeping the current and previous so we know when we encounter a new table.
string[] current_row = new string[num_cols];
string[] previous_row = new string[num_cols];
// hashtable that hashes to a hashtable with values of string...to map the column name to its respective datatypes.
Dictionary<string, Dictionary<string, string>> hash = new Dictionary<string, Dictionary<string, string>>();
Dictionary<string, Dictionary<string, string>> metas = new Dictionary<string, Dictionary<string, string>>();
// Print all table that are about to be dropped and ask "are you sure?"
int count = 0;
string previous = "";
string current = "";
while (config_data_reader.Read())
{
current = config_data_reader[ConfigColumnNames.TableName.ToString()].ToString();
if (config_data_reader["DropTable"].ToString().Trim().ToUpper() == "TRUE" && current != previous)
{
Console.WriteLine("\t" + config_data_reader["TableName"].ToString());
count++;
}
previous = config_data_reader[ConfigColumnNames.TableName.ToString()].ToString();
}
if (count > 0)
{
Console.WriteLine("Um, you are about to drop all of these tables....are you sure you know what you are doing? [y/n/idk]");
string response = Console.ReadLine().Trim().ToLower();
while (response != "y" && response != "n" && response != "idk")
{
Console.WriteLine("Invalid Response. I am expecting one of these: y, n, idk");
response = Console.ReadLine().Trim().ToLower();
}
switch (response)
{
case "y":
break;
case "n":
Console.WriteLine("Whew, that was close.");
System.Environment.Exit(0);
break;
case "idk":
Console.WriteLine("Well, then you probably shouldn't be doing this should you.");
System.Environment.Exit(0);
break;
default:
Console.WriteLine("Ummmm.....Sum Ting Wong. You should never see dis.");
System.Environment.Exit(0);
break;
}
config_data_reader.Close();
}
// Start of the GUTS of PIP
try
{
entire_excel_file = new OleDbCommand("select * from [PIP$] where TableName is not NULL", conn);
config_data_reader = entire_excel_file.ExecuteReader();
}
catch (Exception)
{
Console.WriteLine("I tried to query all of the info in PIP and I couldn't.");
Console.WriteLine("Do you have a tab named 'PIP' in the config file? PS: no trailing spaces or hidden chars?");
Console.WriteLine("Do you have a header called 'TableName'? PS: no trailing spaces or hidden chars?");
System.Environment.Exit(0);
}
while (config_data_reader.Read())
{
// this seems a bit retarded...because it is. I don't know now to cast this to some form of an array that I can keep a previous and current on.
// someone who knows the wonderful world of .NET could probably do this some justice.
foreach (ConfigColumnNames col in Enum.GetValues(typeof(ConfigColumnNames)))
{
string col_value = "";
try
{
col_value = config_data_reader[col.ToString()].ToString();
}
catch (Exception)
{
Console.WriteLine("More than likely, this column '{0}' doesn't exist in PIP config...and PIP expects it to.", col.ToString());
Console.WriteLine("Otherwise, if you are 100% sure the column exists EXACTLY as you see it spelled in this error, check for leading/trailing spaces");
Console.WriteLine("Before and after the column name or any phantom character that don't get displayed...If you have tried all of this and it STILL fails");
Console.WriteLine("This might be worthy of a bug report.");
System.Environment.Exit(0);
}
current_row[(int)col] = col_value;
}
if (current_row[(int)ConfigColumnNames.TableName] != previous_row[(int)ConfigColumnNames.TableName])
{
#region "High Level Algo"
//TODO: this is where we build the create table script & populate the columns in the ssis package with the correct data types.
//This is also where we build the batchfile, using current_row as the list of parameters to obtain.
//Console.WriteLine("{0} is not equal to {1}", current_row[1], previous_row[1]);
// open the template package of currentrow[18]
// update flatfile connection manager with flatfile=currentrow[0]
// x = select * from [packagebot$] where TableName = currentrow[1]
// iterate through the result set.
// update flatfile columns
// update SSIS object model columns
// build create table script. execute create table script.
// save and execute the package.
// load template package so we have something to work off of.
#endregion
if (previous_row[(int)ConfigColumnNames.TemplateSSISPackage] != null)
{
hash.Add(previous_row[(int)ConfigColumnNames.FieldName], new Dictionary<string, string>() {
{"SSIS", previous_row[(int)ConfigColumnNames.SSISFieldFormat]},
{"SQL", previous_row[(int)ConfigColumnNames.SqlFieldFormat]},
{"Default", previous_row[(int)ConfigColumnNames.ClientFieldFormat]},
{"rowdelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter])},
{"coldelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter])},
{"precision", previous_row[(int)ConfigColumnNames.Precision]},
{"scale", previous_row[(int)ConfigColumnNames.Scale]},
{"textqualified", previous_row[(int)ConfigColumnNames.Text_Qualifier]},
{"TruncationRowDisposition", previous_row[(int)ConfigColumnNames.TruncationRowDisposition]},
{"Description", previous_row[(int)ConfigColumnNames.Field_Comments]}});
//packagename = previous_row[27]; // this isn't the real index of package name....change it when you change the config file
// this needs to be an option in the config file
string connection_string = previous_row[(int)ConfigColumnNames.ConnectionString]; // this isn't the real value, need to change it.
//"Data Source=10.1.0.110;User ID=app_etl;Password=clt2010!;Initial Catalog=" + previous_row[22] + ";Provider=NZOLEDB;Persist Security Info=True;";
bool execute = false;
// check config file to see if we should drop this table
bool drop = false;
if (previous_row[(int)ConfigColumnNames.DropTable].ToUpper().Trim() == "TRUE")
{
drop = true;
}
// Drop Recreate Reload Table
if (previous_row[(int)ConfigColumnNames.Drop_Recreate_Reload].ToUpper().Trim() == "TRUE")
{
string tablename = previous_row[(int)ConfigColumnNames.TableName];
string new_name = tablename + "_TEMP";
string cmd = "ALTER TABLE " + tablename + " RENAME TO " + new_name + ";";
string cmd2 = "INSERT INTO " + tablename + " (select * from " + new_name + ");";
StreamWriter sw = new StreamWriter(new_name + ".sql");
sw.WriteLine(cmd);
sw.WriteLine("-- CREATE TABLE is called here...but I am reusing this script {0}", tablename + ".sql");
sw.WriteLine(cmd2);
sw.Close();
// Execute the Command: this will be in a file with tablename + _TEMP.sql in the current directory
OleDbConnection connection = new OleDbConnection(connection_string);
connection.Open();
OleDbCommand command = new OleDbCommand(cmd, connection);
// Rename Table
Console.Write("Creating Temp Table {0}_TEMP...", tablename);
try
{
result = command.ExecuteNonQuery();
if (result != -1)
{//FIXME: Really you should query the number of records in the table, check to see it that matches result
Console.WriteLine("\nThe world will come to and end. The DB should return -1 for this command and it returned {0}", result);
Console.WriteLine("This was the statement '{0}'", command.CommandText);
System.Environment.Exit(0);
}
}
catch (Exception e)
{
Console.Write("Failed.\n");
Console.WriteLine("Could not alter table {0}", tablename);
Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", command.CommandText);
Console.WriteLine(e.Message);
System.Environment.Exit(0);
}
Console.Write("Done.\n");
// Create New Table
string tf = previous_row[(int)ConfigColumnNames.TableFooter].ToString();
CreateSqlTableScript(hash, metas, tablename,connection_string, tf, conn, true, false);
Console.Write("Inserting records into Table {0}...", tablename);
// Insert Old Records into new table
command = new OleDbCommand(cmd2, connection);
try
{
result = command.ExecuteNonQuery();
if (result < 0)
{//FIXME: Really you should query the number of records in the table, check to see it that matches result
Console.WriteLine("\nBUG!! DB Returned {0}", result);
Console.WriteLine("This was the statement '{0}'", command.CommandText);
System.Environment.Exit(0);
}
}
catch (Exception e)
{
Console.Write("\nFailed.\n");
Console.WriteLine("Could not insert records.");
Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", command.CommandText);
Console.WriteLine(e.Message);
System.Environment.Exit(0);
}
Console.Write("Done.\nInserted {0} Records\n", result);
// Drop Temp Table
Console.Write("Dropping Temp Table {0}...", tablename);
try
{
command = new OleDbCommand("DROP TABLE " + tablename + "_TEMP;", connection);
result = command.ExecuteNonQuery();
if (result != -1)
{//FIXME: Really you should query the number of records in the table, check to see it that matches result
Console.WriteLine("\nBUG!! DB Returned {0} Should have been -1", result);
Console.WriteLine("This was the statement '{0}'", "DROP TABLE " + tablename + "_TEMP;");
System.Environment.Exit(0);
}
}
catch (Exception e)
{
Console.Write("Failed.\n");
Console.WriteLine("Dropping {0}_TEMP Failed", tablename);
Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", "DROP TABLE " + tablename + "_TEMP;");
Console.WriteLine(e.Message);
System.Environment.Exit(0);
}
Console.Write("Done.\n");
// Clean up
connection.Close();
sw.Close();
}
// check config file to see if we should execute this table
if (previous_row[(int)ConfigColumnNames.CreateTable].ToUpper().Trim() == "TRUE")
{
execute = true;
string tf = previous_row[(int)ConfigColumnNames.TableFooter];
CreateSqlTableScript(hash, metas, previous_row[(int)ConfigColumnNames.TableName], connection_string, tf, conn, execute, drop);
}
// modify the metadata structure
if (previous_row[(int)ConfigColumnNames.UpdateMetadata].ToUpper().Trim() == "TRUE")
{
string msource_connection_string = previous_row[(int)ConfigColumnNames.MetaDataConnectionString]; // this insn't the right index...needs to change
InsertMSourceEntity(msource_connection_string, previous_row);
}
// Build the package
if (previous_row[(int)ConfigColumnNames.BuildPackage].ToUpper().Trim() == "TRUE")
{
//reset the flat file connection manager with settings in the excel file
Console.Write("Building Package {0}.....", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
p = app.LoadPackage(previous_row[(int)ConfigColumnNames.TemplateSSISPackage], null);
//GetDataFlowTask(p.Executables, p.Executables.GetEnumerator());
Microsoft.SqlServer.Dts.Runtime.Sequence seq = null;
Microsoft.SqlServer.Dts.Runtime.TaskHost th = null;
// looking for sequence container....this shit is very specific to package structure....I have a recursive function
// That can handle this...but not in this code.
try
{
seq = p.Executables["Sequence Container"] as Microsoft.SqlServer.Dts.Runtime.Sequence;
}
catch (Exception)
{
Console.WriteLine("Failed.\nCould not find the Sequence Container in the Package");
Console.WriteLine("the Sequence container MUST BE NAMED: 'Sequence Container'");
Console.WriteLine("Otherwise, you will always see this error message");
System.Environment.Exit(0);
}
// Looking for data flow task
try
{
th = seq.Executables["Data Flow Task"] as Microsoft.SqlServer.Dts.Runtime.TaskHost;
}
catch (Exception)
{
Console.WriteLine("Failed.\nCould not find the Data Flow Task in the Package");
Console.WriteLine("the Sequence container MUST BE NAMED: 'Data Flow Task'");
Console.WriteLine("Otherwise, you will always see this error message");
System.Environment.Exit(0);
}
// Error Checking
if (seq == null || th == null)
{
Console.WriteLine("Failed.\nSum Ting Wong. You should never see this error message");
System.Environment.Exit(0);
}
data_flow_task = th.InnerObject as MainPipe;
try
{
cm_destination = p.Connections["destination"];
}
catch (Exception)
{
Console.WriteLine("Failed\nERROR: your destination connection manager MUST BE NAMED: 'destination'");
System.Environment.Exit(0);
}
// uncomment for packages with source component, should use a try catch
//cm_source = p.Connections["source"];
//p.PackagePassword = "******";
//p.Variables.Remove("filePathName");
//p.Variables.Add("filePathName", false,"User", previous_row[21]);
cm_destination.ConnectionString = connection_string;
string coldelim = ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter]);
string rowdelim = ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter]);
string ssis_var = "@[User::filePathName]";
// uncomment the flat file stuff when working with staging that deals with flatfiles
ResetFlatFileConnectionManager(ssis_var, rowdelim, coldelim, ssis_var, previous_row[(int)ConfigColumnNames.Text_Qualifier], p.Connections);
// reset the column properties in the FlatFile Source Component
IDTSConnectionManagerFlatFile100 cmff = cm_flat_file.InnerObject as IDTSConnectionManagerFlatFile100;
ResetFlatFileColumnProperties(cmff, hash);
// modify columns; types; reinitialize source and destination components; remap columns
string table = previous_row[(int)ConfigColumnNames.TableName].Trim().ToUpper();
ModifyComponents(hash, metas, table, table);
Console.Write("{0}\n", p.Validate(p.Connections, p.Variables, null, null));
try
{
app.SaveToXml(previous_row[(int)ConfigColumnNames.NewSSISPackage], p, null);
}
catch (Exception)
{
Console.WriteLine("Failed.\nCould not save the package. Is your path name in the config correct? I have '{0}'", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
Console.WriteLine("Is this package '{0}' open somewhere else?", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
Console.WriteLine("Do you have the correct permissions?");
Console.WriteLine("Is there something else going on that would cause a lock?");
System.Environment.Exit(0);
}
}
if (previous_row[(int)ConfigColumnNames.ExecutePackage].ToUpper().Trim() == "TRUE")
{
if (p == null)
p = app.LoadPackage(previous_row[(int)ConfigColumnNames.NewSSISPackage], null);
Console.Write("Executing Package {0}...", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
Console.WriteLine(p.Execute());
}
hash = new Dictionary<string, Dictionary<string, string>>();
metas = new Dictionary<string, Dictionary<string, string>>();
} // END IF
} // END IF
else
{
hash.Add(previous_row[(int)ConfigColumnNames.FieldName], new Dictionary<string, string>() {
{"SSIS", previous_row[(int)ConfigColumnNames.SSISFieldFormat]},
{"SQL", previous_row[(int)ConfigColumnNames.SqlFieldFormat]},
{"Default", previous_row[(int)ConfigColumnNames.ClientFieldFormat]},
{"rowdelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter])},
{"coldelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter])},
{"precision", previous_row[(int)ConfigColumnNames.Precision]},
{"scale", previous_row[(int)ConfigColumnNames.Scale]},
{"textqualified", previous_row[(int)ConfigColumnNames.Text_Qualifier]},
{"TruncationRowDisposition", previous_row[(int)ConfigColumnNames.TruncationRowDisposition]},
{"Description", previous_row[(int)ConfigColumnNames.Field_Comments]}});
}
// copy the values in current row to previous row.
for (int i = 0; i < num_cols; i++)
{
previous_row[i] = current_row[i];
}
} // END While
Console.WriteLine("Done.");
Console.ReadLine();
}
static void Main(string[] args)
{
string testDTSX = "LargeExample.dtsx";
string path = Path.Combine(SSISFolder, testDTSX);
// Traverse through every single EDS and save a snapshot of it
//foreach (EDSSettings EDS in SettingsStore.EDSSettings.Where(e => e.Name != "Template"))
//{
// MetaDataSnapshot metaDataSnapshot = new MetaDataSnapshot(EDS.ConnectionString);
// Console.WriteLine(metaDataSnapshot);
// MetaDataStore.Provider.SaveSnapshot(EDS, metaDataSnapshot);
//}
// Key = EDS connection string
Dictionary <string, DatabaseMetaChange> latestChanges = MetaDataStore.GetLatestChanges(SettingsStore.EDSSettings);
_databaseMetaChange = latestChanges.Values.First(); //TODO: change this later.
// Print out changes
Console.WriteLine(_databaseMetaChange);
// Create a new application to host packages, and load a package within it
Application application = new Application();
Package package = application.LoadPackage(path, null);
Graph g = new Graph(application, package, testDTSX, new Options.Options())
{
Options = new Options.Options()
};
// Print attributes, which is used in graph
List <Dictionary <string, Attribute> > list = g.AttributeTable.Values.SelectMany(d => d.Values).ToList();
List <Attribute> attributes = list.SelectMany(d => d.Values).ToList();
string s = string.Join("\n", attributes.Select(a => $" -{a.Id}/{a.AttributeRef?.ID}: {a.Name}"));
Console.WriteLine(s);
// Iterate through all meta changes
foreach (TableMetaChange tableMetaChange in _databaseMetaChange.Tables.Values)
{
foreach (ColumnMetaChange columnMetaChange in tableMetaChange.Columns.Values.Where(c => c.ColumnChanges > 0))
{
foreach (ColumnChanges change in columnMetaChange.ListChanges())
{
g.Alter(columnMetaChange, change);
}
}
}
// Validate package before saving it
package.Validate(package.Connections, package.Variables, new PackageValidateErrorEvent(), null);
// Save package
application.SaveToXml(Path.Combine(TestOutputFolder, "packages", testDTSX), package, null);
Console.WriteLine("Done");
Console.ReadKey();
}
public void Insert_DataFlow()
{
if(Package_File==string.Empty)
{
throw new System.ArgumentException("Empty Package Name", "Package_File");
}
if (Source_Conection_Name == string.Empty)
{
throw new System.ArgumentException("Empty Source Conection Name", "Source_Conection_Name");
}
if (Destination_Conection_Name == string.Empty)
{
throw new System.ArgumentException("Empty Destination Conection Name", "Destination_Conection_Name");
}
if (Source_Table_Name == string.Empty && Source_Query==string.Empty ) //Mast fill source table or source Query
{
throw new System.ArgumentException("Empty Source Table Name and Source Query", "Source_Table_Name");
}
Source_Query = Source_Query != string.Empty ? Source_Query : "select * from " + Source_Table_Name;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package pkg = app.LoadPackage(Package_File, null);
ConnectionManager S_con = GetConnectionByName(pkg, Source_Conection_Name);
if (S_con == null )
{
throw new System.ArgumentException("Can not find source connection by name in Package", "Source_Conection_Name");
}
ConnectionManager D_con = GetConnectionByName(pkg, Destination_Conection_Name);
if (D_con == null)
{
throw new System.ArgumentException("Can not find destination connection by name in Package", "Destination_Conection_Name");
}
TaskHost DataFlowTaskHost = null;
if (Package_Container != string.Empty)
{
DataFlowTaskHost = getNewDataFlowTaskHost(pkg.Executables, Package_Container);
if (DataFlowTaskHost == null)
{
throw new System.ArgumentException("Can not find Sequence Container ", "Package_Container");
}
}
else
{
Executable DFT = pkg.Executables.Add("STOCK:PipelineTask");
DataFlowTaskHost = DFT as TaskHost;
}
//
string s = string.IsNullOrEmpty(Source_Table_Name) ? Destination_Table_Name.Replace("[","_") : Source_Table_Name.Replace("[", "_");
s=s.Replace("]", "_");
s=s.Replace(".", "_");
DataFlowTaskHost.Name = "DTF Load from " + s;
MainPipe DataFlowTask = DataFlowTaskHost.InnerObject as MainPipe;
// / \ : [ ] . =
//Variables 0
string d = Destination_Table_Name.Replace("[", "");
d = d.Replace("]", "");
Variable Var_DestTable = DataFlowTaskHost.Variables.Add("Destination_table", false, "User", "");
Var_DestTable.EvaluateAsExpression = true;
Var_DestTable.Expression = "\"[loading].["+(Destination_Table_Name==string.Empty?Source_Table_Name:Destination_Table_Name)+"_\"+ (DT_STR, 10, 1251) @[User::system_source_id]+\"]\"";
Variable myVar2 = DataFlowTaskHost.Variables.Add("SourceSQL", false, "User", Source_Query );
IDTSComponentMetaData100 source = DataFlowTask.ComponentMetaDataCollection.New();
source.Name = "OLE DB Source";
source.ComponentClassID = "DTSAdapter.OleDbSource";
CManagedComponentWrapper source_instance = source.Instantiate();
// Initialize the component
source_instance.ProvideComponentProperties();
// Specify the connection manager.
if (source.RuntimeConnectionCollection.Count > 0)
{
source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(S_con);
source.RuntimeConnectionCollection[0].ConnectionManagerID =S_con.ID;
}
// Set the custom properties.
source_instance.SetComponentProperty("AccessMode", 2);
source_instance.SetComponentProperty("SqlCommand",Source_Query);
if (Source_Query.IndexOf('?') > -1)
{
string ParamMap = string.Empty ;
string cd = string.Empty; //replace string.Empty with name of variable
foreach (Variable var_day in pkg.Variables )
{
if(var_day.Name==cd)
{
ParamMap = @"""Parameter0:Input""," + var_day.ID + ";";
}
}
if(!string.IsNullOrEmpty(ParamMap))
{
source_instance.SetComponentProperty("ParameterMapping", ParamMap);
}
}
// Reinitialize the metadata.
try
{
source_instance.AcquireConnections(null);
source_instance.ReinitializeMetaData();
source_instance.ReleaseConnections();
}
catch
{
throw new System.Exception("Can not access source Table/Query");
}
IDTSComponentMetaData100 destination = DataFlowTask.ComponentMetaDataCollection.New();
destination.Name = "OLE DB Dest";
destination.ComponentClassID = "DTSAdapter.OLEDBDestination";
CManagedComponentWrapper destination_instance = destination.Instantiate();
// Initialize the component
destination_instance.ProvideComponentProperties();
// Specify the connection manager.
if (destination.RuntimeConnectionCollection.Count > 0)
{
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(D_con);
destination.RuntimeConnectionCollection[0].ConnectionManagerID = D_con.ID;
}
// Set the custom properties.
destination_instance.SetComponentProperty("AccessMode", 1);
destination_instance.SetComponentProperty("OpenRowsetVariable", "User::Destination_table");
// "select * from dbo.system_source");
// Reinitialize the metadata.
try
{
destination_instance.AcquireConnections(null);
destination_instance.ReinitializeMetaData();
destination_instance.ReleaseConnections();
}
catch
{
throw new System.Exception("Can not access destination Table");
}
IDTSPath100 path = DataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0],
destination.InputCollection[0]);
// Get the destination's default input and virtual input.
IDTSInput100 input = destination.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the destination
// to add each available virtual input column as an input column.
IDTSInputColumn100 vCol = destination_instance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
destination_instance.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);
}
app.SaveToXml(Package_File, pkg, null);
}