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
| Value | Text | Related Property | Description |
| 0 | Open Rowset | OpenRowset | You provide the name of a table or view. |
| 1 | Open Rowset from Variable | OpenRowsetVariable | You provide the name of a variable that contains the name of a table or view. |
| 2 | SQL Command | SqlCommand |
You provide a SQL statement.
|
| 3 | SQL Command from Variable | SqlCommandVariable | You provide the name of a variable that contains the SQL statement you wish to execute. |
OLEDB Destination
| Value | Text | Related Property | Description |
| 0 | Open Rowset | OpenRowset |
You provide the name of a table or view.
|
| 1 | Open Rowset from Variable | OpenRowsetVariable | You provide the name of a variable that contains the name of a table or view. |
| 2 | SQL Command | SqlCommand |
You provide a SQL statement.
|
| 3 | Open Rowset Using Fastload | OpenRowset | You provide the name of a table or view. |
| 4 | Open Rowset Using Fastload from Variable | OpenRowsetVariable |
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;
}