Exemple #1
0
        public void CreateProjectAndAddParameter()
        {
            //  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);

            //  changesome project properties
            project.Name        = "Test";
            project.Description = "This project is created from Pegasus.DtsWrapper code";

            // Add a project parameter using the ISParameter constructor
            ISParameter paramA = new ISParameter(project, "AParam", System.TypeCode.Boolean);

            paramA.Required  = false;
            paramA.Sensitive = false;

            // Add more project parameters using the ISProject's AddParameter instance method
            project.AddParameter("BParam", System.TypeCode.String);

            ISParameter paramC = project.AddParameter("CParam", System.TypeCode.Int32);

            paramC.Description = "This is parameter c";

            //  save the project
            project.SaveToDisk();
        }
Exemple #2
0
        public void CreateAndModifyProjectWithProtectionLevel()
        {
            //  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";
            project.TargetServerVersion = TargetServerVersion.SQLServer2016;
            project.ProtectionLevel     = ProtectionLevel.EncryptSensitiveWithPassword;
            project.Password            = "******";

            project.VersionBuild    = 1;
            project.VersionComments = "The first version of the " + project.Name + " project";
            project.VersionMajor    = 1;
            project.VersionMinor    = 0;

            //  save the project...otherwise modifications wont be persisted to the disk
            project.SaveToDisk();

            System.Console.WriteLine(project.VersionComments);
        }
Exemple #3
0
        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();
        }
Exemple #4
0
        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();
        }
Exemple #5
0
        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();
        }
Exemple #6
0
        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();
        }
        /// <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();
        }
Exemple #10
0
        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();
        }