List of columns in SSIS flat file connection
I am trying to create a start / length spec document from an SSIS package that exports a DB table to a flat file. I hit something Columns
against a brick wall by getting a property on the ConnectionManager object that handles the flat file connection. I can get the property Columns
, but I can't do anything about it since I can't figure out what type to use it. If I pass it to DTSProperty, I get very few useful properties for the object. If I get a property like object
, all I can define is System.__ComObject
.
How do I get the Columns property as a useful object, hopefully a collection that I can iterate over?
Sample code:
DTSRT.Application dtap = new Application();
DTSRT.Package pkg = dtap.LoadFromDtsServer(@"\MSDB\ExportSamples", "ERISIA", null);
DTSRT.ConnectionManager ffcn = pkg.Connections["DestinationConnectionFlatFile"];
DtsProperty cols = ffcn.Properties["Columns"];
source to share
UPDATE . The good thing is that the answer below seems out of place in this context (although still useful in a way, so I'll leave it behind). I didn't find the Flat File ConnectionManager had columns available to it, not via a source / target component with output / input columns
The value (GetValue) of the Columns property must be passed to IDTSConnectionManagerFlatFileColumns100 (or 90 if you are using the SQL Server 2005 API)
Alternatively, start your InnerObject configuration manager at IDTSConnectionManagerFlatFile100, which provides the Columns property.
(These interfaces can be found at Microsoft.SqlServer.Dts.Runtime.Wrapper)
Not sure how relevant this is to your context, and if you still require it, but I just did something like this, so I decided to share with him:
The code below will list all columns in an Excel 2007 file by creating an SSIS package on the fly (you need to refer to Microsoft.SqlServer.DTSPipelineWrap, Microsoft.SqlServer.DTSRuntimeWrap, Microsoft.SQLServer.ManagedDTS, and Microsoft.SqlServer.PipelineHost).
As far as I can see, the only difference is that you will need to interact with your loaded package to get the DataFlow task and the corresponding Flat File Destination component with its connection manager (in my case, I created the relevant objects myself) and get the input columns, while not output columns.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
namespace SSISListColumns
{
class Program
{
static void Main(string[] args)
{
// Create package
Package package = new Package();
// Create excel connection manager and set connection string
string fileName = "sampledata.xlsx";
ConnectionManager connection = package.Connections.Add("EXCEL");
connection.Properties["ConnectionString"].SetValue(connection, string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES""", fileName));
// Add Data Flow task
Executable e = package.Executables.Add("STOCK:PipelineTask");
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
// Add Excel Source component
IDTSComponentMetaData100 component = dataFlowTask.ComponentMetaDataCollection.New();
component.Name = "ExcelSource";
component.ComponentClassID = "DTSAdapter.ExcelSource.2";
// Set Excel Source properties (connection manager, access mode and sheet/rowset)
CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(package.Connections[0]);
component.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;
}
instance.SetComponentProperty("AccessMode", 0);
instance.SetComponentProperty("OpenRowset", "Sheet1$");
// Activate
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
// List output columns
var output = component.OutputCollection[0];
foreach (IDTSOutputColumn100 column in output.OutputColumnCollection)
{
Console.WriteLine(column.Name);
}
Console.ReadKey();
}
}
}
Strike>
source to share