This code sample programmatically creates a package with an OLE-DB Source and a Flat File Destination, and the resulting package exports data from SQL Server to a CSV file.
The finished package just has the one Data Flow Task shown below.
The code creates the package, configures the task, and components, then saves the package to disk, useful for checking the package and testing, before finally executing.
namespace Konesans.Dts.Samples
{
using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
internal class SqlToFlatFile
{
public void CreatePackage()
{
Package package = new Package();
package.Name = "SqlToFlatFile";
// Add the SQL OLE-DB connection
ConnectionManager connectionManagerOleDb = package.Connections.Add("OLEDB");
connectionManagerOleDb.Name = "OLEDB";
connectionManagerOleDb.ConnectionString =
"Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;";
// Add the Flat File DB connection, basic info only, will define add columns later
ConnectionManager connectionManagerFlatFile = package.Connections.Add("FLATFILE");
connectionManagerFlatFile.ConnectionString = @"C:\Temp\FlatFile.txt";
connectionManagerFlatFile.Name = "FlatFile";
connectionManagerFlatFile.Properties["Format"].SetValue(connectionManagerFlatFile, "Delimited");
connectionManagerFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManagerFlatFile, true);
// Add the Data Flow Task
package.Executables.Add("STOCK:PipelineTask");
// Get the task host wrapper, and the Data Flow task
TaskHost taskHost = package.Executables[0] as TaskHost;
MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject;
// Add OLE-DB source component
IDTSComponentMetaData90 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
componentSource.Name = "OLEDBSource";
componentSource.ComponentClassID = "DTSAdapter.OleDbSource.1";
// Get OLE-DB source design-time instance, and initialise component
CManagedComponentWrapper instanceSource = componentSource.Instantiate();
instanceSource.ProvideComponentProperties();
// Set source connection
componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerOleDb.ID;
componentSource.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(connectionManagerOleDb);
// Set the source properties
instanceSource.SetComponentProperty("AccessMode", 2);
instanceSource.SetComponentProperty("SqlCommand", "SELECT * FROM sysobjects");
// Reinitialize the metadata, refresh columns
instanceSource.AcquireConnections(null);
instanceSource.ReinitializeMetaData();
instanceSource.ReleaseConnections();
// Add Flat File destination
IDTSComponentMetaData90 componentDestination = dataFlowTask.ComponentMetaDataCollection.New();
componentDestination.Name = "FlatFileDestination";
componentDestination.ComponentClassID = "DTSAdapter.FlatFileDestination.1";
// Get Flat File destination design-time instance, and initialise component
CManagedComponentWrapper instanceDestination = componentDestination.Instantiate();
instanceDestination.ProvideComponentProperties();
// Set destination connection
componentDestination.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerFlatFile.ID;
componentDestination.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(connectionManagerFlatFile);
IDTSPath90 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(componentSource.OutputCollection[0],
componentDestination.InputCollection[0]);
// Get input and virtual input for destination to select and map columns
IDTSInput90 destinationInput = componentDestination.InputCollection[0];
IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
IDTSVirtualInputColumnCollection90 destinationVirtualInputColumns =
destinationVirtualInput.VirtualInputColumnCollection;
// Get native flat file connection
RuntimeWrapper.IDTSConnectionManagerFlatFile90 connectionFlatFile =
connectionManagerFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;
// Create flat file connection columns to match pipeline
int indexMax = destinationVirtualInputColumns.Count - 1;
for (int index = 0; index <= indexMax; index++)
{
// Get input column to replicate in flat file
IDTSVirtualInputColumn90 virtualInputColumn = destinationVirtualInputColumns[index];
// Add column to Flat File connection manager
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 flatFileColumn =
connectionFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90;
flatFileColumn.ColumnType = "Delimited";
flatFileColumn.ColumnWidth = virtualInputColumn.Length;
flatFileColumn.DataPrecision = virtualInputColumn.Precision;
flatFileColumn.DataScale = virtualInputColumn.Scale;
flatFileColumn.DataType = virtualInputColumn.DataType;
RuntimeWrapper.IDTSName90 columnName = flatFileColumn as RuntimeWrapper.IDTSName90;
columnName.Name = virtualInputColumn.Name;
if (index < indexMax)
flatFileColumn.ColumnDelimiter = ",";
else
flatFileColumn.ColumnDelimiter = Environment.NewLine;
}
// Reinitialize the metadata, generating external columns from flat file columns
instanceDestination.AcquireConnections(null);
instanceDestination.ReinitializeMetaData();
instanceDestination.ReleaseConnections();
// Select and map destination columns
foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInputColumns)
{
// Select column, and retain new input column
IDTSInputColumn90 inputColumn = instanceDestination.SetUsageType(destinationInput.ID,
destinationVirtualInput, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
// Find external column by name
IDTSExternalMetadataColumn90 externalColumn =
destinationInput.ExternalMetadataColumnCollection[inputColumn.Name];
// Map input column to external column
instanceDestination.MapInputColumn(destinationInput.ID, inputColumn.ID, externalColumn.ID);
}
#if DEBUG
// Save package to disk, DEBUG only
new Application().SaveToXml(String.Format(@"C:\Temp\{0}.dtsx", package.Name), package, null);
Console.WriteLine(@"C:\Temp\{0}.dtsx", package.Name);
#endif
package.Execute();
foreach (DtsError error in package.Errors)
{
Console.WriteLine("ErrorCode : {0}", error.ErrorCode);
Console.WriteLine(" SubComponent : {0}", error.SubComponent);
Console.WriteLine(" Description : {0}", error.Description);
}
package.Dispose();
}
}
}
Sample code file and example package produced by the code.
SqlToFlatFile.cs
SqlToFlatFile.dtsx