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