public void AddTasksAndAssignPrecedenceConstraints() { // Delete the ispac if exists; otherwise the code will modify the existing ispac. For clarity in showcasing the demo we will delete the existitng ispac if (File.Exists(Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac")) { File.Delete(Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac"); } // create a project ISProject project = new ISProject(Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac", null); // change some project properties project.Name = "Test"; project.Description = "This project is created from Pegasus.DtsWrapper code"; // create a package using the ISPackage's constructor that uses the ISProject parameter ISPackage packageA = new ISPackage("Package_A", project); packageA.Description = "My Package A"; packageA.CreatorName = "Me"; ISPackage packageB = new ISPackage("Package_B", project); packageB.Description = "My Package B"; packageB.CreatorName = "Me again"; // Add a container to Package_A ISSequence mySqlTaskContainer = new ISSequence("My_Container", packageA); // Add a Project connection for the Sql Tasks ISOledbConnectionManager oleConn = new ISOledbConnectionManager("Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;", "my_database_server", project, null); oleConn.ServerName = "localhost"; oleConn.UserName = "******"; oleConn.Password = "******"; oleConn.InitialCatalog = "my_database"; // Add a execute sql task to the above sequence container ISExecuteSqlTask sqlTask = new ISExecuteSqlTask("Some Sql Task", mySqlTaskContainer); sqlTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput; sqlTask.SqlStatementSource = "insert into test_table (id) values (10), (20)"; sqlTask.Connection = oleConn.Name; // Add another execute sql task to the above sequence container ISExecuteSqlTask anotherSqlTask = new ISExecuteSqlTask("Another Sql Task", mySqlTaskContainer); anotherSqlTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput; anotherSqlTask.SqlStatementSource = "insert into test_table_two (id) values (10), (20)"; anotherSqlTask.Connection = oleConn.Name; // Add a precedence constraint that says execute the second sqltask task after hte successful completion of the first sql task ISPrecedenceConstraint pc1 = new ISPrecedenceConstraint(mySqlTaskContainer, sqlTask, anotherSqlTask, PrecedenceEvalOp.Constraint, ExecResult.Success); // Add a Execute Package Task to the parent package (Package A) ISExecutePackageTask ept = new ISExecutePackageTask("Execute Package B", packageA); ept.PackageName = packageB.Name; ept.UseProjectReference = true; // Add a precedence constraint that says execute the Package B after the sql tasks are completed ISPrecedenceConstraint pc2 = new ISPrecedenceConstraint(packageA, mySqlTaskContainer, ept, PrecedenceEvalOp.Constraint, ExecResult.Completion); // save the project project.SaveToDisk(); }
public void GenerateProjectToLoadTextFilesToSqlServerDatabase() { // Showcases how other C# code and libraries can be used to generate packages /* The objective is as follows: * For all the txt files in a folder * 1. Load each text file into a table * 2. Before loading trim all string values * * We will use the lumenworks.framework.io library to parse a text file and help infer data types * * The package design needs to be as follows: * 1. A package for each file in the folder * 2. A master package that executes each of the above packages thru a ExecutePackage Task * 3. In the master package, put all Execute Package Tasks inside a Sequence container, in serial. */ // Get files _fileCollection = Directory.GetFiles(_sourceFolder); // Delete the ispac if exists; otherwise the code will modify the existing ispac. For clarity in showcasing the demo we will delete the existitng ispac if (File.Exists(Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac")) { File.Delete(Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac"); } // create a project ISProject mainProject = new ISProject(Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac", null); // Add a project connection to the sql server we are loading into ISOledbConnectionManager oleConn = new ISOledbConnectionManager(@"Provider=SQLNCLI11.1;Auto Translate=False;", "TargetDB", mainProject); oleConn.ServerName = "localhost"; oleConn.InitialCatalog = "PegasusDemo"; oleConn.UserName = "******"; oleConn.Password = "******"; // Add a Package ISPackage parentPackage = new ISPackage("ParentPackage", mainProject); // Add a sequence container; this container will contain the individual data flows for each text file ISSequence childPackageContainer = new ISSequence("Child Packages", parentPackage); // Iterate thru our folder and do the following for each file foreach (string file in _fileCollection) { string fileName = Path.GetFileNameWithoutExtension(file); // use the lumenworks library to get the column names, lenghts, data types etc. This metadata is later used to configure the ssis project FileHelper fh = new FileHelper(file); string[] columnNames = fh._columnNames; int[] columnLengths = fh.InferFlatFileColumnLengths(10); string[] columnDataTypes = fh.InferFlatColumnSSISDataType(); string[] tableDataTypes = fh.GetSqlServerDataTypes(); // A child package for each file; added to the main project ISPackage packageForFile = new ISPackage(fileName, mainProject); // A execute package task in the master package for each file ISExecutePackageTask ept = new ISExecutePackageTask("Exec " + fileName, childPackageContainer); ept.UseProjectReference = true; ept.PackageName = packageForFile.Name; // this execute package task will call the child package // A connection manager for each file; added to the main project ISFlatFileConnectionManager fConn = new ISFlatFileConnectionManager(file, fileName, mainProject); fConn.ColumnNamesInFirstDataRow = true; //fConn.TextQualifier = "\""; fConn.Format = "Delimited"; fConn.RowDelimiter = "\r\n"; // check for LF/CRLF if using git // create a FlatFile column for each column the in the source file for (int i = 0; i < columnNames.Length; i++) { ISFlatFileColumn fc = new ISFlatFileColumn(fConn, columnNames[i], ((i == columnNames.Length - 1) ? true : false)); fc.SetColumnProperties(DtsUtility.StringToEnum <SSISDataType>(columnDataTypes[i]), "Delimited", ",", 0, columnLengths[i], 0, 0); } // Add a execute sql task which will create the table in the destiantion server. The file helper class gives the create statement to use ISExecuteSqlTask createTable = new ISExecuteSqlTask("Create Target Table", packageForFile); createTable.Connection = oleConn.Name; createTable.SqlStatementSourceType = SqlStatementSourceType.DirectInput; createTable.SqlStatementSource = fh.GetCreateStatement(); // Add a data flow for each file ISDataFlowTask dft = new ISDataFlowTask("Load Data From " + fileName, packageForFile); dft.DelayValidation = true; dft.ParentPackage = packageForFile; dft.ParentProject = mainProject; // Add a precedence constraint that executes the data flow after the create table sql task is a success ISPrecedenceConstraint pc1 = new ISPrecedenceConstraint(packageForFile, createTable, dft, PrecedenceEvalOp.Constraint, ExecResult.Success); // // // Now configure the data flow // Add a flat file source ISFlatFileSourceComponent sourceComp = new ISFlatFileSourceComponent(dft, fileName, fConn); // Add a derive column component that trims a column (in place) if it is of String data type ISDerivedColumnComponent dCom = new ISDerivedColumnComponent(dft, "Trim Columns", sourceComp); foreach (ISFlatFileColumn column in fConn.Columns) { if (column.DataType == SSISDataType.DT_STR) { ISDerivedColumn dCol = new ISDerivedColumn(dCom, DerivedColumnAction.Replace, column.Name); dCol.Expression = "TRIM(" + column.Name + ")"; } } // Add a destination table in the target sql server. ISOleDbDestinationComponent destination = new ISOleDbDestinationComponent(dft, "Target Table", dCom, dCom.GetOutputNameFromIndex(0)); destination.Connection = oleConn.Name; destination.OpenRowset = "dbo." + fileName; // Because, the table may not be available during package generation time, we will manually map the destination columns // To do that, we will create one ExternalMetadataColumn for each column the source file. // Becuase we are creating the target table with the same column names, we will directly map on the name List <ExternalColumnInputMap> externalInputMap = new List <ExternalColumnInputMap>(); for (int i = 0; i < columnNames.Length; i++) { SSISDataTypeWithProperty sdt = Converter.TranslateSqlServerDataTypeToSSISDataTypeWithProperty(tableDataTypes[i], columnLengths[i].ToString()); ExternalMetadataColumn externalColumn = new ExternalMetadataColumn(); externalColumn.ExternalColumnName = columnNames[i]; externalColumn.DataType = sdt.DataType; externalColumn.Length = sdt.Length; externalColumn.Precision = sdt.Precision; externalColumn.Scale = sdt.Scale; externalColumn.CodePage = sdt.CodePage; externalInputMap.Add(new ExternalColumnInputMap { ExternalColumn = externalColumn, InputColumnName = columnNames[i] }); // // Becuase we are creating the target table with the same column names, we will directly map on the name } destination.ExternalColumnInputColumnMap = externalInputMap; // Now perform the manual mapping. Otherwise, SSIS will complain that atleast one column should be mapped. destination.ManualMapToTargetColumns(); } mainProject.SaveToDisk(); }
/// <summary> /// This example shows how to populate a ado net destination table from a ado net source; also, if there is a collection, it can be looped over. /// </summary> public void DFTWithAdoNetSourceAndDestination() { if (File.Exists(Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac")) { File.Delete(Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac"); } // create a project ISProject mainProject = new ISProject(Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac", null); // create a package ISPackage package = new ISPackage("ExamplePackage", mainProject); // Create a Source ADO.net connection manager ISAdoNetConnectionManager srcAdoNetConn = new ISAdoNetConnectionManager(@"Application Name=mySsisApp;", "SourceADONetConnection", mainProject); srcAdoNetConn.InitialCatalog = "PegasusDemo"; srcAdoNetConn.ServerName = "192.168.1.106"; srcAdoNetConn.UserName = "******"; srcAdoNetConn.Password = "******"; // Create a Target ADO.net connection manager ISAdoNetConnectionManager trgAdoNetConn = new ISAdoNetConnectionManager(@"Application Name=mySsisApp;", "TargetADONetConnection", mainProject); trgAdoNetConn.InitialCatalog = "PegasusDemo"; trgAdoNetConn.ServerName = "192.168.1.106"; trgAdoNetConn.UserName = "******"; trgAdoNetConn.Password = "******"; List <SourceTargetTableMap> srcTrgMap = new List <SourceTargetTableMap>(); srcTrgMap.Add( new SourceTargetTableMap { SourceSchema = "dbo", SourceTable = "customer", TargetSchema = "dbo", TargetTable = "customer" } ); srcTrgMap.Add( new SourceTargetTableMap { SourceSchema = "dbo", SourceTable = "shipment", TargetSchema = "dbo", TargetTable = "shipment" } ); ISDataFlowTask prevDft = (ISDataFlowTask)null; foreach (SourceTargetTableMap st in srcTrgMap) { // Create a DataFlow Task ISDataFlowTask dft = new ISDataFlowTask(st.SourceTable + " Data Flow", package); dft.ParentPackage = package; dft.ParentProject = mainProject; // In the Data Flow, create a ADO.net Source ISAdoNetSourceComponent adoNetSrc = new ISAdoNetSourceComponent(dft, "AdoNetSource", srcAdoNetConn); adoNetSrc.TableOrViewName = "\"" + st.SourceSchema + "\"" + "." + "\"" + st.SourceTable + "\""; ISAdoNetDestinationComponent dest = new ISAdoNetDestinationComponent(dft, "AdoNetDestination", adoNetSrc, adoNetSrc.GetOutputNameFromIndex(0)); dest.Connection = trgAdoNetConn.Name; dest.TableOrViewName = "\"" + st.TargetSchema + "\"" + "." + "\"" + st.TargetTable + "\""; if (prevDft == null) { // NO precedence assingment to make } else { ISPrecedenceConstraint pc = new ISPrecedenceConstraint(package, prevDft, dft, PrecedenceEvalOp.Constraint, ExecResult.Success); } prevDft = dft; } mainProject.SaveToDisk(); }