none
hi , how to add sort component in ssis package programiticaly, i write code but i dont know how to set sort key column and sort key position RRS feed

  • Question

  • namespace CreatingPacakge
    {
        class sorting
        {

            static void Main(string[] args)
            {
                Application app = new Application();
                string filePath = @"C:\SqlTestFiles\GeneratedSSISPackages\SortTransformation.dtsx";
                Package pkg = new Package();

                //PipelineComponentInfos info = app.PipelineComponentInfos;
                //foreach (var p in info)
                //{
                //    Console.WriteLine(p.Name + "\n" + p.CreationName);

                //}
                //Console.ReadKey();
                // Creating Dataflow task
                Executable EXEC = pkg.Executables.Add("STOCK:PipelineTask");
                TaskHost thMainPipe = EXEC as TaskHost;
                MainPipe Dataflowtask = thMainPipe.InnerObject as MainPipe;

                //  creating connection manager
                ConnectionManager SrcCon = pkg.Connections.Add("Oledb");
                SrcCon.Name = "SRC_CM";
                SrcCon.ConnectionString = "Provider=SQLNCLI10;Data Source=183.82.48.194;Initial Catalog=CDCTest; User id=dev; password=dev";



                ////Creating OledbSource
                IDTSComponentMetaData100 OledbSourceComponent = Dataflowtask.ComponentMetaDataCollection.New();
                OledbSourceComponent.Name = "OledbSource";
                OledbSourceComponent.ComponentClassID = "DtsAdapter.OledbSource.2";

       //Selecting DataAcessMode ( table or view, sqlCommand, sqlCommand as variable, table name or view name as varible )

    CManagedComponentWrapper SrcDesigneComponent = OledbSourceComponent.Instantiate();
                SrcDesigneComponent.ProvideComponentProperties();

                if (OledbSourceComponent.RuntimeConnectionCollection.Count > 0)
                {
                    OledbSourceComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(pkg.Connections[0]);
                    OledbSourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections[0].ID;
                }

                SrcDesigneComponent.SetComponentProperty("AccessMode", 2);
                SrcDesigneComponent.SetComponentProperty("SqlCommand", "Select * from sudheer2");



                SrcDesigneComponent.AcquireConnections(SrcCon);
                SrcDesigneComponent.ReinitializeMetaData();
                SrcDesigneComponent.ReleaseConnections();




                IDTSComponentMetaData100 SortComponent = Dataflowtask.ComponentMetaDataCollection.New();
                SortComponent.Name = "Sorting";
                SortComponent.ComponentClassID = "DTSTransform.Sort.2";


                // SortComponent.InputCollection[0].CustomPropertyCollection[1].Value = 1;

                CManagedComponentWrapper SortComponetDesigne = SortComponent.Instantiate();
                SortComponetDesigne.ProvideComponentProperties();


                //  IDTSInput100 sortinput = SortComponent.InputCollection[0];
                // IDTSVirtualInput100 sortvirinput = sortinput.GetVirtualInput();
                // IDTSInputColumnCollection100 sortcollection = sortinput.InputColumnCollection;
                // SortComponetDesigne.SetComponentProperty(

                //IDTSCustomPropertyCollection100 data = (IDTSCustomPropertyCollection100)SortComponent.InputCollection[0];
                ////OledbCommandDesigne.AcquireConnections(SrcCon);
                //SortComponetDesigne.ReinitializeMetaData();
                //SortComponetDesigne.ReleaseConnections();
                ConnectionManager Destcon = pkg.Connections.Add("Oledb");
                Destcon.Name = "DST_CM";
                Destcon.ConnectionString = "Provider=SQLNCLI10;Data Source=183.82.48.194;Initial Catalog=CDCTest; User id=dev; password=dev";

                IDTSComponentMetaData100 destComponent = Dataflowtask.ComponentMetaDataCollection.New();
                destComponent.ComponentClassID = "DTSAdapter.OleDbDestination.2";
                destComponent.Name = "OleDbDestination";
                destComponent.ValidateExternalMetadata = true;

                CManagedComponentWrapper destComponetDesigne = destComponent.Instantiate();
                destComponetDesigne.ProvideComponentProperties();


                if (destComponent.RuntimeConnectionCollection.Count > 0)
                {
                    destComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(Destcon);
                    destComponent.RuntimeConnectionCollection[0].ConnectionManagerID = Destcon.ID;
                }

                destComponetDesigne.SetComponentProperty("AccessMode", 0);
                destComponetDesigne.SetComponentProperty("OpenRowset", "sudheertrg");

                destComponetDesigne.AcquireConnections(SrcCon);
                destComponetDesigne.ReinitializeMetaData();
                destComponetDesigne.ReleaseConnections();

                IDTSPath100 path1 = Dataflowtask.PathCollection.New();
                path1.AttachPathAndPropagateNotifications(OledbSourceComponent.OutputCollection[0], SortComponent.InputCollection[0]);


                IDTSPath100 path2 = Dataflowtask.PathCollection.New();
                path2.AttachPathAndPropagateNotifications(SortComponent.OutputCollection[0], destComponent.InputCollection[0]);


                IDTSInput100 sortinput = SortComponent.InputCollection[0];

                int j = sortinput.InputColumnCollection.Count;
                IDTSVirtualInput100 sortVirinput = sortinput.GetVirtualInput();
                IDTSVirtualInputColumnCollection100 OledbcolCollection = sortVirinput.VirtualInputColumnCollection;

                //IDTSInputColumnCollection100 col = sortinput.InputColumnCollection[0];
                IDTSInputColumnCollection100 var = sortinput.InputColumnCollection;

                //  SortComponent.InputCollection[0].InputColumnCollection[0].CustomPropertyCollection[1].Value = 1;
                string name = SortComponent.InputCollection[0].Name;
                // SortComponent.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections[0].ID;
                // OledbCommandDesigne.SetComponentProperty("SqlCommand", "EXEC sp_insertValues ?,?,?");
                //  SortComponetDesigne.SetComponentProperty();




                //try
                //{

                //    IDTSInput100 OledbcommandInput = OledbCommand1.InputCollection[0];
                //    IDTSVirtualInput100 OledbVirinput = OledbcommandInput.GetVirtualInput();
                //    IDTSInputColumnCollection100 OledbcolCollection = OledbcommandInput.InputColumnCollection;
                //    IDTSExternalMetadataColumnCollection100 Extdatacollection = OledbcommandInput.ExternalMetadataColumnCollection;
                //    IDTSOutputColumnCollection100 SrcCols = OledbSourceComponent.OutputCollection[0].OutputColumnCollection;

                //    foreach (IDTSOutputColumn100 outputcol in SrcCols)
                //    {
                //        IDTSExternalMetadataColumn100 Extcols = (IDTSExternalMetadataColumn100)Extdatacollection[outputcol.ID];


                //        OledbVirinput.SetUsageType(outputcol.ID, DTSUsageType.UT_READONLY);
                //        IDTSInputColumn100 inputCol = OledbcolCollection.GetInputColumnByLineageID(outputcol.ID);

                //        if (inputCol != null)
                //        {
                //            OledbCommandDesigne.MapInputColumn(OledbcommandInput.ID, inputCol.ID, Extcols.ID);
                //        }

                //    }
                //}
                //catch (Exception ex)
                //{
                //}

                app.SaveToXml(filePath, pkg, null);


            }


        }

    }

                                                                                   
    Wednesday, July 31, 2013 2:02 PM