コード例 #1
0
        static void Demo2m02()
        {
            //  Configs
            //  --------------------------------------------------------------------

            List <ConManConfigRow> coms = new List <ConManConfigRow>
            {
                new ConManConfigRow(@"CM_OLEDB_contoso", "OLEDB", @"Data Source=ITK\DEV17;Initial Catalog=ContosoRetailDW;Provider=SQLNCLI11.1; Integrated Security=SSPI;Auto Translate=False;"),
                new ConManConfigRow(@"CM_OLEDB_sandbox", "OLEDB", @"Data Source=ITK\DEV17;Initial Catalog=SSISPGenDemo;Provider=SQLNCLI11.1; Integrated Security=SSPI;Auto Translate=False;")
            };

            List <Tuple <string, string> > packs = new List <Tuple <string, string> >
            {
                new Tuple <string, string>("DimAccount", "[dbo].[DimAccount]"),
                new Tuple <string, string>("DimCustomer", "[dbo].[DimCustomer]")
            };

            //  --------------------------------------------------------------------

            IProjectGenerator pg = new ProjectGenerator();

            pg.LoadProjectTemplate();

            //  --------------------------------------------------------------------

            pg.AddConManagers(coms);

            foreach (Tuple <string, string> p in packs)
            {
                Package pt = new Package
                {
                    Name = p.Item1
                };

                //  CF
                //  --------------------------------------------------------------------

                ModifierCF pm = new ModifierCF(pt);

                Sequence seq1 = pm.Add_Sequence("Seq1");
                Sequence seq2 = pm.Add_Sequence("Seq2A");
                Sequence seq3 = pm.Add_Sequence("Seq2B");

                pm.Add_PrecConstr(seq1, seq2);
                pm.Add_PrecConstr(seq1, seq3, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Failure);

                Executable exs1 = pm.AddTask_ExecSQL("Nazwa ESQLTsk",
                                                     "CM_OLEDB_contoso",
                                                     $"SELECT COUNT(*) AS [cnt] FROM {p.Item2}",
                                                     seq1,
                                                     SqlStatementSourceType.DirectInput,
                                                     ResultSetType.ResultSetType_None
                                                     );

                Executable exd1 = pm.AddTask_DataFlow("Load", seq2);

                //  DF
                //  --------------------------------------------------------------------

                ModifierDFT dm1 = new ModifierDFT(pg.Prj(), pt, exd1);

                IDTSComponentMetaData100 com0101 = dm1.AddComp_OleDBSource("OLEDB Src",
                                                                           "CM_OLEDB_contoso",
                                                                           p.Item2,
                                                                           0
                                                                           );

                Variable v1 = pm.Add_Variable("ReadRowsCount", 0);
                IDTSComponentMetaData100 com0102 = dm1.AddComp_RowCount("RRC",
                                                                        "ReadRowsCount",
                                                                        com0101.OutputCollection[0]
                                                                        );

                IDTSComponentMetaData100 com0103 = dm1.AddComp_DerivedCol("DRVCol",
                                                                          com0102.OutputCollection[0]
                                                                          );

                IDTSOutputColumn100 dc010301 = dm1.ModifyComp_DerivedCol_AddCol(com0103,
                                                                                "NewCol1",
                                                                                "123",
                                                                                Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4
                                                                                );

                //  --------------------------------------------------------------------

                pg.Prj().PackageItems.Add(pt, p.Item1 + ".dtsx");

                Console.WriteLine($"{DateTime.Now}:\t{pt.Name}");
            }

            //  --------------------------------------------------------------------

            pg.SaveAsNewProject(@"C:\Users\tomek\Desktop\DCDemoSSIS\IspacFiles\Demo2b.ispac");
        }
コード例 #2
0
ファイル: FullOrMerge.cs プロジェクト: TKostyrka/SSISPGen
        void FullOrMerge(Project pj, Package pi, PackageConfigRow pcr)
        {
            ModifierCF pm = new ModifierCF(pi);

            //  Variables
            //  --------------------------------------------------------------------

            Variable v1 = pm.Add_Variable("AuditKey", 0);
            Variable v2 = pm.Add_Variable("maxUdate", "1990-01-01 00:00:00.000");
            Variable v3 = pm.Add_Variable("SQLCmd", "", true, $"\"SELECT * FROM {pcr.SrcCode} WHERE [UpdateDate] > '\" + @[User::maxUdate]" + "+\"'\"");

            Variable rc1 = pm.Add_Variable("ReadRowsCount", 0);
            Variable rc2 = pm.Add_Variable("InsertRowsCount", 0);
            Variable rc3 = pm.Add_Variable("UpdateRowsCount", 0);
            Variable rc4 = pm.Add_Variable("FailedRowsCount", 0);

            //
            //  --------------------------------------------------------------------

            Executable exs1 = pm.AddTask_ExecSQL(
                "usp_ETL_Audit_Start",
                "CM_OLEDB_sandbox",
                $"EXEC [etl].[usp_ETL_Audit_Start] '{pcr.DesTable}',?,?",
                null,
                SqlStatementSourceType.DirectInput,
                ResultSetType.ResultSetType_SingleRow
                );

            pm.Modify_ExecSQL_AddParameterBinding(exs1, "System::PackageName", OleDBDataTypes.VARCHAR);
            pm.Modify_ExecSQL_AddParameterBinding(exs1, "System::PackageID", OleDBDataTypes.GUID);

            pm.Modify_ExecSQL_AddResultSetBinding(exs1, "AuditPK", "User::AuditKey");

            //
            //  --------------------------------------------------------------------

            Executable exs2 = pm.AddTask_ExecSQL(
                "TRUNC TAB",
                "CM_OLEDB_sandbox",
                $"TRUNCATE TABLE {pcr.DesTable};"
                );

            //
            //  --------------------------------------------------------------------

            Executable exs3 = pm.AddTask_ExecSQL(
                "Get MaxDate",
                "CM_OLEDB_sandbox",
                $"SELECT ISNULL(MAX(CONVERT(VARCHAR,[UpdateDate],121)),'1990-01-01 00:00:00.000') AS maxUDATE FROM {pcr.DesTable}",
                null,
                SqlStatementSourceType.DirectInput,
                ResultSetType.ResultSetType_SingleRow
                );

            pm.Modify_ExecSQL_AddResultSetBinding(exs3, "maxUDATE", "User::maxUdate");

            //  ModifyDFT exd1: FULL
            //  --------------------------------------------------------------------

            Executable exd1 = pm.AddTask_DataFlow("Full");

            ModifierDFT dm1 = new ModifierDFT(pj, pi, exd1);

            IDTSComponentMetaData100 com0101 = dm1.AddComp_OleDBSource("OLEDB Src", "CM_OLEDB_contoso", pcr.SrcCode, 0);

            IDTSComponentMetaData100 com0102 = dm1.AddComp_RowCount("RRC", "ReadRowsCount", com0101.OutputCollection[0]);

            IDTSComponentMetaData100 com0103  = dm1.AddComp_DerivedCol("AddAuditKey", com0102.OutputCollection[0]);
            IDTSOutputColumn100      dc010301 = dm1.ModifyComp_DerivedCol_AddCol(com0103, "AuditKey", "@[User::AuditKey]", Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4);

            IDTSComponentMetaData100 com0104 = dm1.AddComp_RowCount("IRC", "InsertRowsCount", com0103.OutputCollection[0]);

            IDTSComponentMetaData100 com0105 = dm1.AddComp_OleDBDestination("OLEDB Dest", pcr.DesTable, "CM_OLEDB_sandbox", com0104.OutputCollection[0], CreateTableFlag.DropAndCreate);
            string tmpName = dm1.CreateTempTableSQL(pcr.DesTable, "CM_OLEDB_sandbox");


            //
            //  --------------------------------------------------------------------

            Executable exs3b = pm.AddTask_ExecSQL(
                "TRUNC temp TAB",
                "CM_OLEDB_sandbox",
                $"TRUNCATE TABLE {tmpName};"
                );

            //  ModifyDFT exd2: MERGE DELETE
            //  --------------------------------------------------------------------

            Executable  exd2 = pm.AddTask_DataFlow("Partial 01 - DELETE");
            ModifierDFT dm2  = new ModifierDFT(pj, pi, exd2);

            IDTSComponentMetaData100 com0201 = dm2.AddComp_OleDBSource("StagingTable", "CM_OLEDB_sandbox", pcr.DesTable, 0);

            string        sqlCmd0202  = $"SELECT OID FROM {pcr.SrcCode}";
            List <string> joinColumns = new List <string> {
                "OID"
            };
            Dictionary <string, string> newColumns = new Dictionary <string, string> {
                { "OID", "oldOID" }
            };

            IDTSComponentMetaData100 com0202 = dm2.AddComp_Lookup(
                "LookupToSource",
                "CM_OLEDB_contoso",
                sqlCmd0202,
                com0201.OutputCollection[0],
                joinColumns,
                newColumns,
                DTSRowDisposition.RD_NotUsed,
                1
                );

            string sqlCmd0203 = $"DELETE FROM {pcr.DesTable} WHERE [OID] = ?";
            Dictionary <string, string> paramMapping0203 = new Dictionary <string, string>
            {
                { "Param_0", "OID" }
            };

            IDTSComponentMetaData100 com0203 = dm2.AddComp_OleDBCommand("OLE OLE Command!",
                                                                        "CM_OLEDB_sandbox",
                                                                        com0202.OutputCollection["Lookup No Match Output"],
                                                                        sqlCmd0203,
                                                                        paramMapping0203
                                                                        );

            //  ModifyDFT exd2: MERGE Match->TempTbl, NoMatch->Dest
            //  --------------------------------------------------------------------

            Executable  exd3 = pm.AddTask_DataFlow("Partial 02 - INSERT");
            ModifierDFT dm3  = new ModifierDFT(pj, pi, exd3);

            IDTSComponentMetaData100 com0300 = dm3.AddComp_OleDBSource("OLEDB Src", "CM_OLEDB_contoso", "User::SQLCmd", 3);

            IDTSComponentMetaData100 com0301 = dm3.AddComp_DerivedCol("AddAuditKey", com0300.OutputCollection[0]);
            IDTSOutputColumn100      dc0301  = dm3.ModifyComp_DerivedCol_AddCol(com0301, "AuditKey", "@[User::AuditKey]", Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4);

            IDTSComponentMetaData100 com0302 = dm3.AddComp_RowCount("RRC", "ReadRowsCount", com0301.OutputCollection[0]);

            string        sqlCmd0303      = $"SELECT OID FROM {pcr.DesTable}";
            List <string> joinColumns0303 = new List <string> {
                "OID"
            };
            Dictionary <string, string> newColumns0303 = new Dictionary <string, string>();

            IDTSComponentMetaData100 com0303 = dm3.AddComp_Lookup("LookupToSource",
                                                                  "CM_OLEDB_sandbox",
                                                                  sqlCmd0303,
                                                                  com0302.OutputCollection[0],
                                                                  joinColumns0303,
                                                                  newColumns0303,
                                                                  DTSRowDisposition.RD_NotUsed,
                                                                  1
                                                                  );

            IDTSComponentMetaData100 com0304I = dm3.AddComp_RowCount("IRC", "InsertRowsCount", com0303.OutputCollection["Lookup No Match Output"]);
            IDTSComponentMetaData100 com0304U = dm3.AddComp_RowCount("URC", "UpdateRowsCount", com0303.OutputCollection["Lookup Match Output"]);

            IDTSComponentMetaData100 com0305I = dm3.AddComp_OleDBDestination("OLEDB Dest - INSERT", pcr.DesTable, "CM_OLEDB_sandbox", com0304I.OutputCollection[0], CreateTableFlag.NoAction);
            IDTSComponentMetaData100 com0305U = dm3.AddComp_OleDBDestination("OLEDB Dest - UPDATE", tmpName, "CM_OLEDB_sandbox", com0304U.OutputCollection[0], CreateTableFlag.NoAction);

            //
            //  --------------------------------------------------------------------

            Executable exs4 = pm.AddTask_ExecSQL(
                "MERGE TempAndDest 01 - DELETE",
                "CM_OLEDB_sandbox",
                $"DELETE f \nFROM {pcr.DesTable} AS f \nINNER JOIN {tmpName} AS t ON f.[OID] = t.[OID]"
                );

            //
            //  --------------------------------------------------------------------

            Executable exs5 = pm.AddTask_ExecSQL(
                "MERGE TempAndDest 02 - INSERT",
                "CM_OLEDB_sandbox",
                $"INSERT INTO {pcr.DesTable} \nSELECT * \nFROM {tmpName} AS t"
                );

            //
            //  --------------------------------------------------------------------

            Executable exs6 = pm.AddTask_ExecSQL(
                "usp_ETL_Audit_Stop",
                "CM_OLEDB_sandbox",
                "[etl].[usp_ETL_Audit_Stop] ?,?,?,?,?"
                );

            pm.Modify_ExecSQL_AddParameterBinding(exs6, "User::AuditKey", OleDBDataTypes.LONG);
            pm.Modify_ExecSQL_AddParameterBinding(exs6, "User::ReadRowsCount", OleDBDataTypes.LONG);
            pm.Modify_ExecSQL_AddParameterBinding(exs6, "User::InsertRowsCount", OleDBDataTypes.LONG);
            pm.Modify_ExecSQL_AddParameterBinding(exs6, "User::UpdateRowsCount", OleDBDataTypes.LONG);
            pm.Modify_ExecSQL_AddParameterBinding(exs6, "User::FailedRowsCount", OleDBDataTypes.LONG);

            //  ModifyCF - Precedence Constraints
            //  --------------------------------------------------------------------

            pm.Add_PrecConstr(exs1, exs2, null, DTSPrecedenceEvalOp.ExpressionAndConstraint, DTSExecResult.Success, "@[$Project::ETLMode] == 1");
            pm.Add_PrecConstr(exs1, exs3, null, DTSPrecedenceEvalOp.ExpressionAndConstraint, DTSExecResult.Success, "@[$Project::ETLMode] == 2");
            pm.Add_PrecConstr(exs2, exd1, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success);
            pm.Add_PrecConstr(exs3, exs3b, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success);
            pm.Add_PrecConstr(exs3b, exd2, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success);

            pm.Add_PrecConstr(exd2, exd3, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success);
            pm.Add_PrecConstr(exd3, exs4, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success);
            pm.Add_PrecConstr(exs4, exs5, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success);

            pm.Add_PrecConstr(exd1, exs6, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success, null, false);
            pm.Add_PrecConstr(exs5, exs6, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success, null, false);
        }
コード例 #3
0
        void FullOrIncremental(Project pj, Package pi, PackageConfigRow pcr)
        {
            //  obróbka CF
            //  --------------------------------------------------------------------

            ModifierCF pm = new ModifierCF(pi);

            Variable v1 = pm.Add_Variable("AuditKey", 0);
            Variable v2 = pm.Add_Variable("maxUdate", "1990-01-01 00:00:00.000");
            Variable v3 = pm.Add_Variable("SQLCmd", "", true, $"\"SELECT * FROM {pcr.SrcCode} WHERE [UpdateDate] > '\" + @[User::maxUdate]" + "+\"'\"");

            Variable rc1 = pm.Add_Variable("FailedRowsCount", 0);
            Variable rc2 = pm.Add_Variable("InsertRowsCount", 0);
            Variable rc3 = pm.Add_Variable("ReadRowsCount", 0);
            Variable rc4 = pm.Add_Variable("UpdateRowsCount", 0);

            //
            //  --------------------------------------------------------------------

            Executable exs1 = pm.AddTask_ExecSQL(
                "usp_ETL_Audit_Start",
                "CM_OLEDB_sandbox",
                $"EXEC [etl].[usp_ETL_Audit_Start] '{pcr.DesTable}',?,?",
                null,
                SqlStatementSourceType.DirectInput,
                ResultSetType.ResultSetType_SingleRow
                );

            pm.Modify_ExecSQL_AddParameterBinding(exs1, "System::PackageName", OleDBDataTypes.VARCHAR);
            pm.Modify_ExecSQL_AddParameterBinding(exs1, "System::PackageID", OleDBDataTypes.GUID);

            pm.Modify_ExecSQL_AddResultSetBinding(exs1, "AuditPK", "User::AuditKey");

            //
            //  --------------------------------------------------------------------

            Executable exs2 = pm.AddTask_ExecSQL(
                "TRUNC TAB",
                "CM_OLEDB_sandbox",
                $"TRUNCATE TABLE {pcr.DesTable};"
                );

            //
            //  --------------------------------------------------------------------

            Executable exs3 = pm.AddTask_ExecSQL(
                "Get MaxDate",
                "CM_OLEDB_sandbox",
                $"SELECT ISNULL(MAX(CONVERT(VARCHAR,[UpdateDate],121)),'1990-01-01 00:00:00.000') AS maxUDATE FROM {pcr.DesTable}",
                null,
                SqlStatementSourceType.DirectInput,
                ResultSetType.ResultSetType_SingleRow
                );

            pm.Modify_ExecSQL_AddResultSetBinding(exs3, "maxUDATE", "User::maxUdate");

            ////  ModifyDFT exd1: FULL
            ////  --------------------------------------------------------------------

            Executable  exd1 = pm.AddTask_DataFlow("Full");
            ModifierDFT dm1  = new ModifierDFT(pj, pi, exd1);

            IDTSComponentMetaData100 com0101 = dm1.AddComp_OleDBSource("OLEDB Src", "CM_OLEDB_contoso", pcr.SrcCode, 0);

            IDTSComponentMetaData100 com0102 = dm1.AddComp_RowCount("RRC", "ReadRowsCount", com0101.OutputCollection[0]);

            IDTSComponentMetaData100 com0103  = dm1.AddComp_DerivedCol("AddAuditKey", com0102.OutputCollection[0]);
            IDTSOutputColumn100      dc010301 = dm1.ModifyComp_DerivedCol_AddCol(com0103, "AuditKey", "@[User::AuditKey]", Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4);

            IDTSComponentMetaData100 com0104 = dm1.AddComp_RowCount("IRC", "InsertRowsCount", com0103.OutputCollection[0]);

            IDTSComponentMetaData100 com0105 = dm1.AddComp_OleDBDestination("OLEDB Dest", pcr.DesTable, "CM_OLEDB_sandbox", com0104.OutputCollection[0], CreateTableFlag.DropAndCreate);

            //  ModifyDFT exd2: INCREMENTAL
            //  --------------------------------------------------------------------

            Executable  exd2 = pm.AddTask_DataFlow("Incremental");
            ModifierDFT dm2  = new ModifierDFT(pj, pi, exd2);

            IDTSComponentMetaData100 com0201 = dm2.AddComp_OleDBSource("OLEDB Src", "CM_OLEDB_contoso", "User::SQLCmd", 3);

            IDTSComponentMetaData100 com0202 = dm2.AddComp_RowCount("RRC", "ReadRowsCount", com0201.OutputCollection[0]);

            IDTSComponentMetaData100 com0203  = dm2.AddComp_DerivedCol("AddAuditKey", com0202.OutputCollection[0]);
            IDTSOutputColumn100      dc020301 = dm2.ModifyComp_DerivedCol_AddCol(com0203, "AuditKey", "@[User::AuditKey]", Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4);

            IDTSComponentMetaData100 com0204 = dm2.AddComp_RowCount("IRC", "InsertRowsCount", com0203.OutputCollection[0]);

            IDTSComponentMetaData100 com0205 = dm2.AddComp_OleDBDestination("OLEDB Dest", pcr.DesTable, "CM_OLEDB_sandbox", com0204.OutputCollection[0], CreateTableFlag.NoAction);

            //
            //  --------------------------------------------------------------------

            Executable exs4 = pm.AddTask_ExecSQL(
                "usp_ETL_Audit_Stop",
                "CM_OLEDB_sandbox",
                "[etl].[usp_ETL_Audit_Stop] ?,?,?,?,?"
                );

            pm.Modify_ExecSQL_AddParameterBinding(exs4, "User::AuditKey", OleDBDataTypes.LONG);
            pm.Modify_ExecSQL_AddParameterBinding(exs4, "User::ReadRowsCount", OleDBDataTypes.LONG);
            pm.Modify_ExecSQL_AddParameterBinding(exs4, "User::InsertRowsCount", OleDBDataTypes.LONG);
            pm.Modify_ExecSQL_AddParameterBinding(exs4, "User::UpdateRowsCount", OleDBDataTypes.LONG);
            pm.Modify_ExecSQL_AddParameterBinding(exs4, "User::FailedRowsCount", OleDBDataTypes.LONG);

            //  ModifyCF - Precedence Constraints
            //  --------------------------------------------------------------------

            pm.Add_PrecConstr(exs1, exs2, null, DTSPrecedenceEvalOp.ExpressionAndConstraint, DTSExecResult.Success, "@[$Project::ETLMode] == 1");
            pm.Add_PrecConstr(exs1, exs3, null, DTSPrecedenceEvalOp.ExpressionAndConstraint, DTSExecResult.Success, "@[$Project::ETLMode] == 2");
            pm.Add_PrecConstr(exs2, exd1, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success);
            pm.Add_PrecConstr(exs3, exd2, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success);
            pm.Add_PrecConstr(exd1, exs4, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success, null, false);
            pm.Add_PrecConstr(exd2, exs4, null, DTSPrecedenceEvalOp.Constraint, DTSExecResult.Success, null, false);
        }