Tuesday, May 28, 2013

Run time SSIS

Refer :http://www.selectsifiso.net/?p=82
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138216

This sample creates a data flow package with an OleDB Source component which reads from a local AdventureWorksDW2008 database, and writes to a different table in the same database using an OleDB Destination.
Items of interest:
  • The AccessMode property is an int value which controls how you’re retrieving/sending data. Once you set a value, you need to set a value for the related property (see the table below). For example, if you set the AccessMode for the OLEDB Source to 2 (SQL Command), you should set a SQL statement value for the SqlCommand property.
OLEDB Source
ValueTextRelated PropertyDescription
0Open RowsetOpenRowsetYou provide the name of a table or view.
1Open Rowset from VariableOpenRowsetVariableYou provide the name of a variable that contains the name of a table or view.
2SQL CommandSqlCommand
You provide a SQL statement.
3SQL Command from VariableSqlCommandVariableYou provide the name of a variable that contains the SQL statement you wish to execute.
OLEDB Destination
ValueTextRelated PropertyDescription
0Open RowsetOpenRowset
You provide the name of a table or view.
1Open Rowset from VariableOpenRowsetVariableYou provide the name of a variable that contains the name of a table or view.
2SQL CommandSqlCommand
You provide a SQL statement.
3Open Rowset Using FastloadOpenRowsetYou provide the name of a table or view.
4Open Rowset Using Fastload from VariableOpenRowsetVariable
You provide the name of a variable that contains the name of a table or view.
  • Calling ReinitializeMetadata() causes the component to fetch the table metadata. This should only be called after setting the AccessMode and related property.


Code to do the Task.
---------------------------------------------------------------------------------------------------------


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;



namespace DataFlow_Task_Program
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a package
            Package package = new Package();
           
            // Add a Executable data flow task
            Executable dataFlowTask = package.Executables.Add("STOCK:PipelineTask");
           
            // Setting the name of the data flow task
            TaskHost taskHost = dataFlowTask as TaskHost;
            taskHost.Name = "Data Flow Task";

            // Reference to the InnerObject to add items to the data flow
            MainPipe pipeline = taskHost.InnerObject as MainPipe;


            IDTSComponentMetaData100 srcComponent = pipeline.ComponentMetaDataCollection.New();
            IDTSComponentMetaData100 destComponent = pipeline.ComponentMetaDataCollection.New();
            ConnectionManager connection = package.Connections.Add("OLEDB");
            IDTSDesigntimeComponent100 destDesignTimeComponent = destComponent.Instantiate();



            // Connection Managers
            #region Section for adding Connection Managers
            try
            {

                // ConnectionManager connection = package.Connections.Add("OLEDB");
                connection.Name = "localhost";
                connection.ConnectionString = "Data Source=localhost;Initial Catalog=TestDb;Integrated Security=True";
            }
            catch { }

            #endregion
        

            // OLEDB Source Connection Manager
            #region Section for Adding an OLEDB Source
            // Source for OLEDB Connection
            try
            {
               // IDTSComponentMetaData100 srcComponent = pipeline.ComponentMetaDataCollection.New();
                srcComponent.ComponentClassID = "DTSAdapter.OleDbSource";
                srcComponent.ValidateExternalMetadata = true;
                IDTSDesigntimeComponent100 srcDesignTimeComponent = srcComponent.Instantiate();
                srcDesignTimeComponent.ProvideComponentProperties();
                srcComponent.Name = "OleDb Source";

                // Configuring  it to read from the given table
                srcDesignTimeComponent.SetComponentProperty("AccessMode", 0);
                srcDesignTimeComponent.SetComponentProperty("OpenRowset", "[Test1]");

                // Setting the connection manager
                srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connection);
                srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ID;

                // Retrieveing the column metadata
                srcDesignTimeComponent.AcquireConnections(null);
                srcDesignTimeComponent.ReinitializeMetaData();
                srcDesignTimeComponent.ReleaseConnections();
            }
            catch { }
            #endregion

            //OLEDB Destination Connection Manager
            #region Section for Adding OLEDB Destination
            try
            {

                //IDTSComponentMetaData100 destComponent = pipeline.ComponentMetaDataCollection.New();
                destComponent.ComponentClassID = "DTSAdapter.OleDbDestination";
                destComponent.ValidateExternalMetadata = true;

                //IDTSDesigntimeComponent100 destDesignTimeComponent = destComponent.Instantiate();
                destDesignTimeComponent.ProvideComponentProperties();
                destComponent.Name = "OleDb Destination";

                destDesignTimeComponent.SetComponentProperty("AccessMode", 3);
                destDesignTimeComponent.SetComponentProperty("OpenRowset", "[Test2]");

                // Setting the connection
                destComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connection);
                destComponent.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ID;

                // Geting metadata
                destDesignTimeComponent.AcquireConnections(null);
                destDesignTimeComponent.ReinitializeMetaData();
                destDesignTimeComponent.ReleaseConnections();
            }
            catch { }
            #endregion


            // Connecting the source and Destination OLEDB Connetction
            try
            {
                IDTSPath100 path = pipeline.PathCollection.New();
                path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], destComponent.InputCollection[0]);

                // Configuring the Destination

                IDTSInput100 destInput = destComponent.InputCollection[0];
                IDTSVirtualInput100 destVirInput = destInput.GetVirtualInput();
                IDTSInputColumnCollection100 destInputCols = destInput.InputColumnCollection;
                IDTSExternalMetadataColumnCollection100 destExtCols = destInput.ExternalMetadataColumnCollection;
                IDTSOutputColumnCollection100 sourceColumns = srcComponent.OutputCollection[0].OutputColumnCollection;

                // The OLEDB destination requires you to hook up the external columns
                foreach (IDTSOutputColumn100 outputCol in sourceColumns)
                {
                    // Get the external column id
                    IDTSExternalMetadataColumn100 extCol = (IDTSExternalMetadataColumn100)destExtCols[outputCol.Name];
                    if (extCol != null)
                    {
                        // Create an input column from an output col of previous component.
                        destVirInput.SetUsageType(outputCol.ID, DTSUsageType.UT_READONLY);
                        IDTSInputColumn100 inputCol = destInputCols.GetInputColumnByLineageID(outputCol.ID);
                        if (inputCol != null)
                        {
                            // map the input column with an external metadata column
                            destDesignTimeComponent.MapInputColumn(destInput.ID, inputCol.ID, extCol.ID);
                        }
                    }
                }
            }
            catch { }


        }
    }
}




Create SSIS Package with SQL Tasks using C#


 private void button2_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                Microsoft.SqlServer.Dts.Runtime.Application SIFISO_app = new Microsoft.SqlServer.Dts.Runtime.Application();


                //Adding Connection Manger
                Package package = new Package();
                ConnectionManager ConMgr = package.Connections.Add("OLEDB");
                ConMgr.ConnectionString = "Provider=SQLOLEDB.1;" +
                "Integrated Security=SSPI;Initial Catalog=Tracker;" +
                "Data Source=S4UAGP-PC\\ASHOKINSTANCE;";
                ConMgr.Name = "SourceConnectionOLEDB";
                ConMgr.Description = "OLE DB connection to the AdventureWorks2008R2 database.";
               

               
                Executable e1 = package.Executables.Add("STOCK:PipelineTask");
                TaskHost thMainPipe = e1 as TaskHost;
                thMainPipe.Name = "Data Flow Task 1";
                //thMainPipe.Properties["Connection"].SetValue(thMainPipe, "SourceConnectionOLEDB");
                MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

               // string Conn = "Data Source=S4UAGP-PC\\ASHOKINSTANCE;Initial Catalog=MMBD;Integrated Security=True";
                // Create the source component.  


                ///Adding Source
                ///
                IDTSComponentMetaData100 srcComponent =   dataFlowTask.ComponentMetaDataCollection.New();
                srcComponent.ComponentClassID = "DTSAdapter.OleDbSource";
                CManagedComponentWrapper srcDesignTime = srcComponent.Instantiate();
                srcComponent.Name = "Source - MMBD_LN_MSTR1";
                srcComponent.Description = "OLE DB Source";
                IDTSDesigntimeComponent100 srcDesignTimeComponent = srcComponent.Instantiate();
                srcDesignTimeComponent.ProvideComponentProperties();

                // Configuring  it to read from the given table
                //srcDesignTimeComponent.SetComponentProperty("AccessMode", 2);
                //srcDesignTimeComponent.SetComponentProperty("SqlCommand", "SELECT  * FROM   [MMBD_LN_MSTR]");

                srcDesignTimeComponent.SetComponentProperty("AccessMode", 0);
                srcDesignTimeComponent.SetComponentProperty("OpenRowset", "[dbo].[MMBD_LN_MSTR1]");

                // Setting the connection manager
                srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(ConMgr);
                srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID = ConMgr.ID;
                srcDesignTime.AcquireConnections(null);
                srcDesignTime.ReinitializeMetaData();

               

                //Adding Data Conversion

                IDTSComponentMetaData100 dataConvertComponent = dataFlowTask.ComponentMetaDataCollection.New();
                dataConvertComponent.ComponentClassID = "DTSTransform.DataConvert";
                dataConvertComponent.Name = "Data Convert";
                dataConvertComponent.Description = "Data Conversion Component";

                CManagedComponentWrapper conversionInstance = dataConvertComponent.Instantiate();
                conversionInstance.ProvideComponentProperties();

                conversionInstance.AcquireConnections(null);
                conversionInstance.ReinitializeMetaData();

                // Connect the source and the transform
                dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0],   dataConvertComponent.InputCollection[0]);

                IDTSOutput100 output = dataConvertComponent.OutputCollection[0];

                IDTSVirtualInput100 virtualInput = dataConvertComponent.InputCollection[0].GetVirtualInput();

                int inputId = dataConvertComponent.InputCollection[0].ID;

              

                foreach (IDTSVirtualInputColumn100 vColumn in virtualInput.VirtualInputColumnCollection)
                {
                    string test = vColumn.DataType.ToString();
                    if (vColumn.Name == "LN_DRVD_RMNG_TERM") MessageBox.Show("ff");
                    Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dtype = new Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType();
                    dtype = getSSISDataTypes(vColumn.DataType);
                    if (dtype != Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR)
                    {
                        IDTSInputColumn100 inputColumn = conversionInstance.SetUsageType(inputId, virtualInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
                        IDTSOutputColumn100 outputColumn = conversionInstance.InsertOutputColumnAt(output.ID, 0, vColumn.Name, string.Empty);
                        //conversionInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn.ID, dtype, vColumn.Length, 0, 0, 0);
                        conversionInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn.ID, dtype, 1000, 0, 0, 0);
                        outputColumn.CustomPropertyCollection[0].Value = inputColumn.LineageID;
                    }
                }

                conversionInstance.ReleaseConnections();
               

                //Adding Destination
                ConnectionManager SconMgr = package.Connections.Add("EXCEL");
                SconMgr.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\tesing.xls;Extended Properties=""Excel 8.0;HDR=Yes""";
                SconMgr.Name = "ExcelConnection";
              

                IDTSComponentMetaData100 destComponent = dataFlowTask.ComponentMetaDataCollection.New();
                destComponent.ComponentClassID = "DTSAdapter.ExcelDestination";
                CManagedComponentWrapper srcDesignTime1 = destComponent.Instantiate();
                destComponent.Name = "Source - MMBD_LN_MSTR1";
                destComponent.Description = "OLE DB Source";
                IDTSDesigntimeComponent100 srcDesignTimeComponentdest = destComponent.Instantiate();
                srcDesignTimeComponentdest.ProvideComponentProperties();

                srcDesignTimeComponentdest.SetComponentProperty("AccessMode", 0);
                srcDesignTimeComponentdest.SetComponentProperty("OpenRowset", "[MMBD_LN_MSTR]");

                destComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr);
                destComponent.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;

                dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(dataConvertComponent.OutputCollection[0], destComponent.InputCollection[0]);

                //Mapping

                //IDTSOutput100 outputdest = destComponent.OutputCollection[0];
                //IDTSOutput100 inputputdest = dataConvertComponent.OutputCollection[0];

                //IDTSVirtualInput100 virtualInputdest = destComponent.InputCollection[0].GetVirtualInput();
             
                //foreach (IDTSVirtualInputColumn100 vColumn in virtualInput.VirtualInputColumnCollection)
                //{
                //    IDTSExternalMetadataColumn100 exMetaColumn = inputputdest.ExternalMetadataColumnCollection.New();
                //    srcDesignTimeComponentdest.MapInputColumn(inputputdest.ID, vColumn.ID, exMetaColumn.ID);
                //}


                // Iterate through the inputs of the component.
                foreach (IDTSInput100 input in destComponent.InputCollection)
                {
                    // Get the virtual input column collection for the input.
                    IDTSVirtualInput100 vInput = input.GetVirtualInput();

                    // Iterate through the virtual column collection.
                    foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
                    {
                        // Call the SetUsageType method of the design time instance of the component.

                        srcDesignTime1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);


                    }
                    //map external metadata to the inputcolumn
                    //int index = 0;
                    foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
                    {

                        IDTSExternalMetadataColumn100 exMetaColumn = input.ExternalMetadataColumnCollection.New();
                        exMetaColumn.CodePage = inputColumn.CodePage;
                        exMetaColumn.DataType = inputColumn.DataType;
                        exMetaColumn.Length = inputColumn.Length;
                        exMetaColumn.Name = inputColumn.Name;
                        inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
                        srcDesignTime1.MapInputColumn(input.ID, inputColumn.ID, exMetaColumn.ID);
                    }
                }


                SIFISO_app.SaveToXml("D:\\C Drive\\Desktop\\DataExportWizard_Source\\SSIS\\SSIS\\pkg_Execute_Sql_Tasks.dtsx", package, null);

               
                DTSExecResult pkgResults = package.Execute();

                if (pkgResults == DTSExecResult.Success)
                    Console.WriteLine("Package ran successfully");
                else
                    Console.WriteLine("Package failed");
            }
            catch (Exception ex)
            { 
            }
        }

        public Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType getSSISDataTypes(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType DataType)
        {
            int codepage = 0; ;
            int length = 0; ;
            int scale = 0;
            int precision = 0;


            Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR;
            switch (DataType)
            {
            
                case  Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR:
                    codepage = 0;
                    length = 3000;
                    scale = 0;
                    precision = 0;
                    Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
                    break;

            }
          
            return Dtype;
        }
        

1 comment:

  1. http://ssisjunkie.blogspot.com/2012/09/programatically-developing-oledb-source.html

    ReplyDelete