public void CreateProjectAndAddPackage()
{
// 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 = "Sam the Man";
// create a package using the ISPackage's constructor that does NOT use ISProject parameter.
// Later call the AddToProject method on the package to add it to a project.
ISPackage packageB = new ISPackage("Package_B");
packageB.CreatorName = "SamIAm";
packageB.AddToProject(project); // Note: if the reuse flag is true, then any changes made to pacakge variable before this call are replaced with the found package's properties (if a package with the same name exists)
packageB.CreatorComputerName = "SomeOtherMachine";
packageB.Description = "My Package B";
ISPackage packageC = new ISPackage("Package_C");
packageC.Description = "Hi I am Package C";
project.AddPackage(packageC, false);
// 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();
}
public void CreatePackageWithSort()
{
// 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
Console.WriteLine("Creating Project");
ISProject mainProject = new ISProject(Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac", null);
// Flat File Connection
// 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(_sourceFile);
string[] columnNames = fh._columnNames;
int[] columnLengths = fh.InferFlatFileColumnLengths(10);
string[] columnDataTypes = fh.InferFlatColumnSSISDataType();
string[] tableDataTypes = fh.GetSqlServerDataTypes();
Console.WriteLine("Creating flatfile conn");
ISOledbConnectionManager oleConn = new ISOledbConnectionManager(@"Data Source=localhost;Initial Catalog=PegasusDemo;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;", "SourceDB", mainProject);
ISFlatFileConnectionManager fConn = new ISFlatFileConnectionManager(_sourceFile, "Flat_File", mainProject);
fConn.ColumnNamesInFirstDataRow = true;
//fConn.TextQualifier = "\"";
fConn.Format = "Delimited";
fConn.RowDelimiter = "\r\n"; // check for LF/CRLF if using git
string columnDelimiter = ",";
// 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", columnDelimiter, 0, columnLengths[i], 0, 0);
}
// Add a Package
Console.WriteLine("Creating package");
ISPackage parentPackage = new ISPackage("ParentPackage", mainProject);
// Add a data flow
Console.WriteLine("Creating dft");
ISDataFlowTask dft = new ISDataFlowTask("SortTest", parentPackage);
dft.DelayValidation = true;
dft.ParentPackage = parentPackage;
dft.ParentProject = mainProject;
// Configure the data flow
// Add a flat file source
Console.WriteLine("Creating source compo");
ISFlatFileSourceComponent sourceComponent = new ISFlatFileSourceComponent(dft, "SortInput", fConn);
// Sort Component
Console.WriteLine("Creating sort compo");
ISSortComponent sortComponent = new ISSortComponent(dft, "Sorter", sourceComponent);
sortComponent.EliminateDuplicates = true; // Should the componet remove duplicates or not?
sortComponent.MaximumThreads = -1; // How many Threads should it use
// The next two lines shows sorting the data by col_a first and then by col_c
sortComponent.SetColumnSortInformation(columnName: "col_a", sortKeyPosition: 1, stringComparisonFlag: StringComparisonFlag.IGNORE_CASE);
sortComponent.SetColumnSortInformation("col_c", 2, StringComparisonFlag.IGNORE_CASE);
// Hook it to a Row count Component for testing
Console.WriteLine("Creating a rowcount component");
ISVariable rcVar = new ISVariable("RCount", false, "User", 0, null, dft, VariableDataType.Int32);
ISRowCountComponent rowComponent = new ISRowCountComponent(dft, "RC", sortComponent);
rowComponent.AssignVariable(rcVar);
Console.WriteLine("saving package to disk at " + Constants.StorageFoldePath + @"\" + _ispacFileName + ".ispac");
mainProject.SaveToDisk();
}
public void GenerateProjectToLoadTextFilesFromSqlServerDatabase()
{
// Showcases how other C# code and libraries can be used to generate packages
/* The objective is as follows:
* For a given collection of tables
* 1. Load each table into a text file
*
* We will use SQL Server system tables to get the column metadata
*
* 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 List of Tables
List <SqlServerTable> sqlServerTables = new List <SqlServerTable>();
sqlServerTables.Add(new SqlServerTable {
Schema = "dbo", Table = "customer"
});
sqlServerTables.Add(new SqlServerTable {
Schema = "dbo", Table = "geo_location"
});
// Where should the final files be stored?
string destinationFolder = @"C:\Temp_new\Pegasus_SSISGenerator";
string baseMetadataQuery = @"select c.name as column_name, c.column_id as column_position,
ty.name as data_type,
case
when ty.name in ('decimal', 'numeric') then '(' + convert(varchar(10), c.precision) + ', ' + convert(varchar(10), c.scale) + ')'
when ty.name in ('varchar', 'char') then '(' + case c.max_length when -1 then 'max' else convert(varchar(10), c.max_length) end + ')'
when ty.name in ('nvarchar', 'nchar') then '(' + case c.max_length when -1 then 'max' else convert(varchar(10), c.max_length/2) end + ')'
else ''
end as data_type_length
from sys.objects o
inner join sys.columns c on o.object_id = c.object_id
inner join sys.types ty on c.user_type_id = ty.user_type_id
where o.type ='U'" ;
/*********************** Start SSIS Related Instructions ****************************************/
// 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;", "SourceDB", mainProject);
oleConn.ServerName = "192.168.1.107";
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 table collection and do the following for each table
foreach (SqlServerTable table in sqlServerTables)
{
System.Console.WriteLine("\nWorking on table:: " + table.Schema + "." + table.Table);
System.Console.WriteLine("\tGetting metadata");
// using the oledb conn, get the metadata; the example assumes you can connect to the target database from this application
SqlServerHelper sh = new SqlServerHelper(oleConn.ServerName, oleConn.InitialCatalog, oleConn.UserName, "password123");
// most of the time you dont want to constrcut the sql statement like belwo to account for best practices
DataSet ds = sh.GetDataSet(baseMetadataQuery + " and o.name = '" + table.Table + "' and schema_name(o.schema_id) = '" + table.Schema + "'");
DataTable dt = ds.Tables[0];
string[] columnNames = new string[dt.Rows.Count];
SSISDataTypeWithProperty[] columnDataTypes = new SSISDataTypeWithProperty[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
columnNames.SetValue(dt.Rows[i]["column_name"].ToString(), i);
columnDataTypes.SetValue(Converter.TranslateSqlServerDataTypeToSSISDataTypeWithProperty(dt.Rows[i]["data_type"].ToString(), dt.Rows[i]["data_type_length"].ToString()), i);
}
// A child package for each file; added to the main project
System.Console.WriteLine("\tCreating child package");
ISPackage packageForFile = new ISPackage(table.Schema + "_" + table.Table, mainProject);
// A execute package task in the master package for each table
System.Console.WriteLine("\tCreating exec pkg task to call child package");
ISExecutePackageTask ept = new ISExecutePackageTask("Exec " + table.Schema + "_" + table.Table, 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
System.Console.WriteLine("\tCreating Flat File Conn Mgr");
ISFlatFileConnectionManager flatFileConn = new ISFlatFileConnectionManager(destinationFolder + @"\" + table.Schema + "__" + table.Table + ".txt", table.Schema + "__" + table.Table + "_Conn", mainProject);
flatFileConn.ColumnNamesInFirstDataRow = true;
//fConn.TextQualifier = "\"";
flatFileConn.Format = "Delimited";
flatFileConn.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(flatFileConn, columnNames[i], ((i == columnNames.Length - 1) ? true : false));
fc.SetColumnProperties(columnDataTypes[i].DataType, "Delimited", ",", 0, columnDataTypes[i].Length, 0, 0);
}
// Add a data flow for each file
System.Console.WriteLine("\tData Flow in ach chld pkg ");
ISDataFlowTask dft = new ISDataFlowTask("Load Data From " + table.Schema + "__" + table.Table, packageForFile);
dft.DelayValidation = true;
dft.ParentPackage = packageForFile;
dft.ParentProject = mainProject;
// // // Now configure the data flow
// Add a flat file source
System.Console.WriteLine("\t\tCreating Oledb Src Component");
ISOleDbSourceComponent sourceComp = new ISOleDbSourceComponent(dft, "Source Table", oleConn);
string columns = string.Join(", ", columnNames);
sourceComp.SqlCommand = "select " + columns + " from " + table.Schema + "." + table.Table;
// Add a destination
System.Console.WriteLine("\t\tCreating Flat File Dest Component");
ISFlatFileDestination ffDest = new ISFlatFileDestination(dft, "Destination", sourceComp, sourceComp.GetOutputNameFromIndex(0));
ffDest.Overwrite = true;
ffDest.Connection = flatFileConn.Name;
// Now we will manually map the destination columns
// To do that, we will create one ExternalMetadataColumn for each column in the source table.
// Becuase we are creating the destination text file 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 = columnDataTypes[i];
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
}
ffDest.ExternalColumnInputColumnMap = externalInputMap;
// Now perform the manual mapping. Otherwise, SSIS will complain that atleast one column should be mapped.
ffDest.ManualMapToTargetColumns();
}
// Finally, save the project ispac to disk
System.Console.WriteLine("\n\nSaving ispac to disk");
mainProject.SaveToDisk();
}
public ISKingswaySoftCRMConnectionManager(string name, ISProject project = null, ISPackage package = null) : base("", name, "DynamicsCRM", project, package)
{
}
/// <summary>
/// This example shows Kingsway Connection generate
/// </summary>
public void KingswayConnection()
{
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 Kinsway connection manager
CrmSourceComponent crmSourceComponent1 = new CrmSourceComponent();
ISKingswaySoftCRMConnectionManager crmConnection = new ISKingswaySoftCRMConnectionManager("CrmConnection", mainProject, package);
crmConnection.ConnectionString = "";//write connection String
crmConnection.IgnoreCertificateErrors = true;
// Create DataFlow
ISDataFlowTask iSDataFlowTask = new ISDataFlowTask("Data Flow", package);
//Create Kingsway Soft Source Component
ISCrmSourceComponent crmSourceComponent = new ISCrmSourceComponent(iSDataFlowTask, "Crm source", crmConnection);
crmSourceComponent.SourceEntity = "account";
crmSourceComponent.BatchSize = 1;
crmSourceComponent.BatchSize = 1;
//Define Output for Source Component
ISOutput iSOutput = new ISOutput(crmSourceComponent, 0);
//Define Entity Column for output Name doesn't need to same with crm name
ISOutputColumn iSOutputColumn = new ISOutputColumn(crmSourceComponent, iSOutput.Name, "name");
var datatpye = Converter.GetSSISDataTypeFromADONetDataType("string", 180);
iSOutputColumn.SetDataTypeProperties(datatpye);
//Configure External Metadata for Entity, columnt name must be same with crm
ISExternalMetadataColumn iSExternalMetadataColumn = new ISExternalMetadataColumn(crmSourceComponent, iSOutput.Name, "name", false);
iSExternalMetadataColumn.SetDataType(datatpye);
iSExternalMetadataColumn.AssociateWithOutputColumn("name");
//Create Destination component
ISCrmDestination iSCrmDestination = new ISCrmDestination(iSDataFlowTask, "Crm Destination", crmSourceComponent, crmSourceComponent.GetOutputNameFromIndex(0), crmConnection);
iSCrmDestination.DestinationEntity = "contact";
iSCrmDestination.ActionType = (int)KingswaySoft.IntegrationToolkit.DynamicsCrm.CrmDestinationActionType.Create;
//Create Destination Component Input
ISInput iSInput = new ISInput(iSCrmDestination, 0);
//Map Source component output to destination input
var inputColumn = new ISInputColumn(iSCrmDestination, iSInput.Name, iSOutputColumn.Name, UsageType.UT_READWRITE);
//Set input metadata
ISExternalMetadataColumn iSExternalMetadataColumnforInput = new ISExternalMetadataColumn(iSCrmDestination, iSInput.Name, "fullname", true, "name");
iSExternalMetadataColumnforInput.SetDataType(datatpye);
//Set Crm type information, Type information must be defined.
iSExternalMetadataColumnforInput.SetCustomPropertyToExternalMetadataColumn("CrmFieldType", "String");
iSExternalMetadataColumnforInput.SetCustomPropertyToExternalMetadataColumn("LookupTypes", "");
mainProject.SaveToDisk();
}
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();
}
/// <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();
}
public void ExcelToSqlServerTable()
{
// In this example,we are loading an excel spresheet to a sql server table.
// The excel sheet has three columns named col_a(unicode string 255),col_b (unicode_string 255) and col_c(double precision float)
// The target table has three columns named my_first_col (nvarchar(255)), my_second_col (nvarchar(255)), my_third_col(float(53))
// Load as follows: col_a -> my_first_col; col_b -> my_second_col; col_c -> my_third_col
// <Mapping info that says which excel source column should go to which sql server table column
List <SourceColumnTargetColumnMap> sourceTargetMappings = new List <SourceColumnTargetColumnMap>();
// In this example, I am manullay populating the source to target map.
// Otherwise, this information can be gathered programmatically for both source and target from external config files/mapping tables etc.
// For programmatic translation of other data types to SSIS data types, refer to the code in Converter class in the DtsWrapper library
sourceTargetMappings.Add(new SourceColumnTargetColumnMap
{
SourceColumn = new SourceColumn {
Name = "col_a"
}, // excel source column
TargetColumn = new TargetColumn {
Name = "my_first_col", DataType = SSISDataType.DT_WSTR, Length = 255, CodePage = 0, Precision = 0, Scale = 0
} // target column into which source should go to
});
sourceTargetMappings.Add(new SourceColumnTargetColumnMap
{
SourceColumn = new SourceColumn {
Name = "col_b"
}, // excel source column
TargetColumn = new TargetColumn {
Name = "my_second_col", DataType = SSISDataType.DT_WSTR, Length = 255, CodePage = 0, Precision = 0, Scale = 0
} // target column into which source should go to
});
sourceTargetMappings.Add(new SourceColumnTargetColumnMap
{
SourceColumn = new SourceColumn {
Name = "col_c"
}, // excel source column
TargetColumn = new TargetColumn {
Name = "my_third_col", DataType = SSISDataType.DT_R8, Length = 0, CodePage = 0, Precision = 0, Scale = 0
} // target column into which source should go to
});
// // // Package creation related code
// 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";
// Excel Connection Manager
string file = @"C:\Temp\abc.xls";
ISExcelConnectionManager excelConn = new ISExcelConnectionManager("excelConn", project);
excelConn.ExcelFilePath = file;
excelConn.ExcelVersionNumber = ExcelVersion.DTSExcelVer_8;
excelConn.FirstRowHasColumnName = true;
// Target SQL Server Connection Manager
ISOledbConnectionManager oleConn = new ISOledbConnectionManager(@"Provider=SQLNCLI11.1;Auto Translate=False;", "TargetDB", project);
oleConn.ServerName = "192.168.1.107";
oleConn.InitialCatalog = "PegasusDemo";
oleConn.UserName = "******";
oleConn.Password = "******";
// Create a package
ISPackage packageA = new ISPackage("Package_A", project);
packageA.Description = "Package demoing loading an excel file to a sql server table with mappings specified";
packageA.CreatorName = "Me";
// Add a Data Flow
ISDataFlowTask dft = new ISDataFlowTask("ExcelDFT", packageA);
dft.DelayValidation = true;
dft.ParentPackage = packageA;
dft.ParentProject = project;
// // // Configure the data flow
// Add a excel source component
ISExcelSourceComponent es = new ISExcelSourceComponent(dft, "ExSrc", excelConn);
es.OpenRowset = "Sheet1$";
// Add a sql server destination
ISOleDbDestinationComponent dest = new ISOleDbDestinationComponent(dft, "Target Table", es, es.GetOutputNameFromIndex(0));
dest.Connection = oleConn.Name;
dest.OpenRowset = "dbo.ExcelToSqlServer";
for (int i = 0; i < sourceTargetMappings.Count; i++)
{
ExternalMetadataColumn externalColumn = new ExternalMetadataColumn();
externalColumn.ExternalColumnName = sourceTargetMappings[i].TargetColumn.Name; // the name of the column in the target?
externalColumn.DataType = sourceTargetMappings[i].TargetColumn.DataType;
externalColumn.Length = sourceTargetMappings[i].TargetColumn.Length;
externalColumn.Precision = sourceTargetMappings[i].TargetColumn.Precision;
externalColumn.Scale = sourceTargetMappings[i].TargetColumn.Scale;
externalColumn.CodePage = sourceTargetMappings[i].TargetColumn.CodePage;
dest.ExternalColumnInputColumnMap.Add(new ExternalColumnInputMap {
ExternalColumn = externalColumn, InputColumnName = sourceTargetMappings[i].SourceColumn.Name
});
}
// Now perform the manual mapping. Otherwise, SSIS will complain that atleast one column should be mapped.
dest.ManualMapToTargetColumns();
project.SaveToDisk();
}
