create table with recursive relationship

I am fairly new to SQL and I am currently trying to create
a SQL table (using Microsoft SQL) that has a recursive
relationship, let me try to explain:

I have a piece of Data let's call it "Item" wich may again contain one
more "Items". Now how would I design a set of SQL Tables that are
capable of storing this information?

I tried the following two approaches:

1.) create a Table "Item" with Column "ItemID" as primary key, some
colums for the Data an Item can store and a Column "ParentItemID". I
set a foreign key for ParentItemID wich links to the primarykey
"ItemID" of the same table.

2.) create separate Table "Item_ParentItem" that stores
ItemID-ParentItemID-pairs. Each column has a foreign key linked to
primary key of the "Item" Column "ItemID".

In both approaches when I try to delete an Item I get an Exception
saying that the DELETE command could not be executed because it
violates a COLUMN REFERENCE constraint. The goal behind these FK_PK
relations is is that when an Item gets deleted, all childItems should
automatically be deleted recursively.

How is this "standard-problem" usually solved in sql? Or do I inned to
implement the recursive deletion myself using stored
procedures or something ?You can get away with the first approach. However, you may not use ON
DELETE CASCADE. Rather, you are looking at a trigger that can manage this.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Robert Ludig" <schwertfischtrombose@.gmx.de> wrote in message
news:1147782885.171670.231170@.i40g2000cwc.googlegr oups.com...
I am fairly new to SQL and I am currently trying to create
a SQL table (using Microsoft SQL) that has a recursive
relationship, let me try to explain:

I have a piece of Data let's call it "Item" wich may again contain one
more "Items". Now how would I design a set of SQL Tables that are
capable of storing this information?

I tried the following two approaches:

1.) create a Table "Item" with Column "ItemID" as primary key, some
colums for the Data an Item can store and a Column "ParentItemID". I
set a foreign key for ParentItemID wich links to the primarykey
"ItemID" of the same table.

2.) create separate Table "Item_ParentItem" that stores
ItemID-ParentItemID-pairs. Each column has a foreign key linked to
primary key of the "Item" Column "ItemID".

In both approaches when I try to delete an Item I get an Exception
saying that the DELETE command could not be executed because it
violates a COLUMN REFERENCE constraint. The goal behind these FK_PK
relations is is that when an Item gets deleted, all childItems should
automatically be deleted recursively.

How is this "standard-problem" usually solved in sql? Or do I inned to
implement the recursive deletion myself using stored
procedures or something ?|||On 16 May 2006 05:34:45 -0700, Robert Ludig wrote:

>I am fairly new to SQL and I am currently trying to create
>a SQL table (using Microsoft SQL) that has a recursive
>relationship, let me try to explain:
>I have a piece of Data let's call it "Item" wich may again contain one
>more "Items". Now how would I design a set of SQL Tables that are
>capable of storing this information?
>
>I tried the following two approaches:
(snip)

Hi Robert,

I agree with Tom that the first approach is better than the first. But
there are also some radically different ways to store a recursive
relationship or hierarchy. One of the more popular variants is the
nested set model. It's not nearly as intuitive as the model you are
proposing, but it performs far superior in some scenario's.

Google for "Nested Set Model" if you want to know the details.

--
Hugo Kornelis, SQL Server MVP|||>> How is this "standard-problem" usually solved in sql? <<

Get a copy of TREES & HIERARCHIES IN SQL for several ways to model this
kind of problem.

>> Or do I inned to implement the recursive deletion myself using stored
procedures or something ? <<

No need for recursive procedural code if you use the nested sets model.
Younger programmers who learned HTML, XML, etc. find it to be
intuitive. Older programmers who grew up with pointer chains need to
adjust their mind-set.

Create table from Text

Hi, all. I'm fairly new to SQL, and I have been trying to create a table
from a text file. I have been looking at this for days, and can't find the
problem. I get a syntax error " Line 55: Incorrect syntax near
'DateUpdated'." Here is the query. Any suggestions would be appreciated,
as I am trying to learn and improve.

Use ACH
go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[ImportFiles]
GO

CREATE Procedure ImportFiles
@.FilePath varchar(1000),
@.MergeProc varchar(128) = 'MergeData'
AS
DECLARE @.cmd varchar(2000),
@.Command_String varchar(3000)

DECLARE @.FileName varchar(1000),
@.File varchar(1000)

CREATE table ##Import (datarow varchar(200))
CREATE table #Dir (datarow varchar(200))

DROP TABLE ACHParticipants

select @.cmd = 'dir /B' + @.FilePath
delete #Dir
insert #Dir exec master..xp_cmdshell @.cmd

delete #Dir where datarow is null or datarow like '%not found%'

while exists (select * from #Dir)

BEGIN
select @.FileName = min(datarow) from #Dir
select @.file= @.FilePath + @.FileName
select @.cmd = 'bulk insert'
select @.cmd = @.cmd + ' ##Import'
select @.cmd = @.cmd + ' from'
select @.cmd = @.cmd + ' @.File,'
select @.cmd = @.cmd + ' with (FIELDTERMINATOR=''\n'''
select @.cmd = @.cmd + ',ROWTERMINATOR = '':\n'')'

truncate table ##Import

-- import the data
exec (@.cmd)

-- remove filename just imported
delete #Dir where datarow = @.FileName

exec @.MergeProc
END

drop table ##Import
drop table #Dir
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MergeData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MergeData]
GO

CREATE PROCEDURE MergeData
AS
CREATE table ACHParticipants
(RoutingNum varchar(9),
OfficeCode varchar(1),
ServicingFRBNum varchar(9),
RecordType varchar(1),
ChangeDate varchar(8),
NewRoutingNum varchar(9),
BankName varchar(36),
BankAddress varchar(36),
City varchar(20),
State varchar(2),
Zipcode varchar(10),
Phone varchar(14),
StatusCode varchar(1),
DataView varchar(1),
Filler varchar(5),
DateUpdated datetime)

INSERT INTO ACHParticipants
(Routing_Number
, Office_Code
, Servicing_FRB_Number
, Record_Type_Code
, Change_Date
, New_Routing_Number
, Customer_Name
, Address
, City
, State_Code
, Zipcode
, Telephone
, Institution_Status_Code
, Data_View_Code
, Filler
, DateUpdated)

SELECT Substring(DataRow,1,9) AS RoutingNum,
Substring(DataRow,10,1) AS OfficeCode,
Substring(DataRow,11,9) AS ServicingFRBNum,
Substring(DataRow,20,1) AS RecordType,
convert(datetime,Substring(DataRow,21,6)) AS ChangeDate,
Substring(DataRow,27,9) AS NewRoutingNum,
Substring(DataRow,36,36) AS BankName,
Substring(DataRow,72,36) AS BankAddress,
Substring(DataRow,108,20) AS City,
Substring(DataRow,128,2) AS State,
Substring(DataRow,130,5) + '-' + Substring(DataRow,135,4) AS Zipcode,
Substring(DataRow,139,3) + '-' + Substring(DataRow,142,3) + '-' +
Substring(DataRow,145,4) AS Phone,
Substring(DataRow,149,1) AS StatusCode,
Substring(DataRow,150,1) AS DataView,
Substring(DataRow,151,5) AS Filler
DateUpdated datetime AS DateUpdated
FROM ##Import
GO

Thanks,
KarenThe error is probably because you are missing a comma after "AS
Filler". In general, you should avoid creating permanent tables from
within stored procedures, as it makes it very hard to control your data
model correctly, and if the proc is run multiple times you may have
problems.

A common approach is to create a permanent staging table (instead of
using a temporary one as you are), and bulk load your files into that.
A stored proc can then do the final INSERT into ACHParticipants, after
making any other data changes that might be needed.

You might also want to consider loading the data using bcp.exe instead
of BULK INSERT - it can often be easier to deal with file names etc.
outside the database, in a batch file or a script of some other sort.

Simon|||Thank you, Simon. I have put the comma in, and I am still getting the
error. I am going to try setting up a staging table - thanks again for the
suggestion.

Thank you for the good advice.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1117006217.229228.129070@.g14g2000cwa.googlegr oups.com...
> The error is probably because you are missing a comma after "AS
> Filler". In general, you should avoid creating permanent tables from
> within stored procedures, as it makes it very hard to control your data
> model correctly, and if the proc is run multiple times you may have
> problems.
> A common approach is to create a permanent staging table (instead of
> using a temporary one as you are), and bulk load your files into that.
> A stored proc can then do the final INSERT into ACHParticipants, after
> making any other data changes that might be needed.
> You might also want to consider loading the data using bcp.exe instead
> of BULK INSERT - it can often be easier to deal with file names etc.
> outside the database, in a batch file or a script of some other sort.
> Simon

Create output columns based on input in custom component

I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.

I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.

I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).

Are there any good examples for creating output columns dynamically based on the input buffer?

Should I just give up on on the transform and create a custom source component instead?

Since there aren't any rows in the buffer until runtime, I don't see how this will work. Packages can't change their metadata (inputs / outputs) at runtime. You could write a source that uses the connection manager at design time to read the first line from the file, and add the output columns, but that would be by directly reading the flat file, not by using a row from the buffer.|||

You could try something like this-

IDTSInput90 input = ComponentMetaData.InputCollection[inputID];

This is a design-time action, in the same way as you would "normally" use the flat file source to load a CSV file, and let the designer UI figure out the columns. This will not allow you to change the file layout at run-time, and magically load any file you happen to find. You area aware of this distinction?

From a design pattern perspective, this is not the place to be selecting and generating columns. It would be more sensible to do this either in a UI or ReinitializeMetadata. Validate could detect the stupid state of no input columns selected, and/or it not matching the input, and call RMD.

A source may be cleaner, or even a package generator. I am not clear on what you are really trying to do, and what problem you need to solve.

|||

Thanks,

I'm trying to solve the problem related to a CSV source missing columns for some rows, it's been brought up a few times here in the past but I haven't seen a generic solution that is suitable for multiple DTS packages that are dependent on multiple CSV files all with 50+ columns.

Here's a forum entry on it:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2025483&SiteID=17

And Jamie T's explanation with more links:

http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_--Flat-File-Connection-Manager-issues.aspx

I'll see if I can get the custom source component working today.

|||

I was able to get the source component working based off an example from Professional SQL Server 2005 Integration Services

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html

(The site has a page for downloading the examples).

The example for creating a source component had a couple errors in it (probably from being based off a pre-release version of SSIS).

Here's some of the key code:

Code Snippet

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager);

if (cm.CreationName != "FLATFILE")

{

throw new Exception("The Connection Manager is not a FILE Connection Manager");

}

else

{

_fileExist = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);

if (_fileExist != Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)

{

throw new Exception("The type of FILE connection manager must be an Existing File");

}

else

{

_filename = ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager.AcquireConnection(transaction).ToString();

if (_filename == null || _filename.Length == 0)

{

throw new Exception("Nothing returned when grabbing the filename");

}

}

}

}

}

The original example checked "if (cm.CreationName != "FILE")" which should actually be "if (cm.CreationName != "FLATFILE")"

Code Snippet

private void CreateOutputAndMetaDataColumns(IDTSOutput90 output)

{

if (_filename != null || _filename.Length > 0)

{

TextReader tr = File.OpenText(_filename);

string columns = tr.ReadLine();

tr.Close();

_columnNames = columns.Split(",".ToCharArray());

foreach (string columnName in _columnNames)

{

IDTSOutputColumn90 outName = output.OutputColumnCollection.New();

outName.Name = columnName.Trim();

outName.Description = columnName.Trim();

outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);

//Create an external metadata column to go alongside with it

CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outName);

}

}

}

Just to get the sample working all columns are strings for the moment, for my needs this is all I needed anyways.

Code Snippet

private bool DoesEachOutputColumnHaveAMetaDataColumnAndDoDatatypesMatch(int outputID)

{

IDTSOutput90 output = ComponentMetaData.OutputCollection.GetObjectByID(outputID);

IDTSExternalMetadataColumn90 mdc;

bool rtnVal = true;

int cCount = 0;

foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)

{

if (col.ExternalMetadataColumnID == 0)

{

rtnVal = false;

}

else

{

//mdc = output.ExternalMetadataColumnCollection[col.ExternalMetadataColumnID];

mdc = output.ExternalMetadataColumnCollection[cCount];

if (mdc.DataType != col.DataType || mdc.Length != col.Length || mdc.Precision != col.Precision

|| mdc.Scale != col.Scale || mdc.CodePage != col.CodePage)

{

rtnVal = false;

}

cCount++;

}

}

return rtnVal;

}

This was the other change I needed to make to the example, the collection index doesn't match the column ID.

I'll try to post the full source code online if I get some time so that hopefully it saves someone else the trouble.

Create output columns based on input in custom component

I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.

I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.

I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).

Are there any good examples for creating output columns dynamically based on the input buffer?

Should I just give up on on the transform and create a custom source component instead?

Since there aren't any rows in the buffer until runtime, I don't see how this will work. Packages can't change their metadata (inputs / outputs) at runtime. You could write a source that uses the connection manager at design time to read the first line from the file, and add the output columns, but that would be by directly reading the flat file, not by using a row from the buffer.|||

You could try something like this-

IDTSInput90 input = ComponentMetaData.InputCollection[inputID];

This is a design-time action, in the same way as you would "normally" use the flat file source to load a CSV file, and let the designer UI figure out the columns. This will not allow you to change the file layout at run-time, and magically load any file you happen to find. You area aware of this distinction?

From a design pattern perspective, this is not the place to be selecting and generating columns. It would be more sensible to do this either in a UI or ReinitializeMetadata. Validate could detect the stupid state of no input columns selected, and/or it not matching the input, and call RMD.

A source may be cleaner, or even a package generator. I am not clear on what you are really trying to do, and what problem you need to solve.

|||

Thanks,

I'm trying to solve the problem related to a CSV source missing columns for some rows, it's been brought up a few times here in the past but I haven't seen a generic solution that is suitable for multiple DTS packages that are dependent on multiple CSV files all with 50+ columns.

Here's a forum entry on it:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2025483&SiteID=17

And Jamie T's explanation with more links:

http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_--Flat-File-Connection-Manager-issues.aspx

I'll see if I can get the custom source component working today.

|||

I was able to get the source component working based off an example from Professional SQL Server 2005 Integration Services

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html

(The site has a page for downloading the examples).

The example for creating a source component had a couple errors in it (probably from being based off a pre-release version of SSIS).

Here's some of the key code:

Code Snippet

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager);

if (cm.CreationName != "FLATFILE")

{

throw new Exception("The Connection Manager is not a FILE Connection Manager");

}

else

{

_fileExist = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);

if (_fileExist != Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)

{

throw new Exception("The type of FILE connection manager must be an Existing File");

}

else

{

_filename = ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager.AcquireConnection(transaction).ToString();

if (_filename == null || _filename.Length == 0)

{

throw new Exception("Nothing returned when grabbing the filename");

}

}

}

}

}

The original example checked "if (cm.CreationName != "FILE")" which should actually be "if (cm.CreationName != "FLATFILE")"

Code Snippet

private void CreateOutputAndMetaDataColumns(IDTSOutput90 output)

{

if (_filename != null || _filename.Length > 0)

{

TextReader tr = File.OpenText(_filename);

string columns = tr.ReadLine();

tr.Close();

_columnNames = columns.Split(",".ToCharArray());

foreach (string columnName in _columnNames)

{

IDTSOutputColumn90 outName = output.OutputColumnCollection.New();

outName.Name = columnName.Trim();

outName.Description = columnName.Trim();

outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);

//Create an external metadata column to go alongside with it

CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outName);

}

}

}

Just to get the sample working all columns are strings for the moment, for my needs this is all I needed anyways.

Code Snippet

private bool DoesEachOutputColumnHaveAMetaDataColumnAndDoDatatypesMatch(int outputID)

{

IDTSOutput90 output = ComponentMetaData.OutputCollection.GetObjectByID(outputID);

IDTSExternalMetadataColumn90 mdc;

bool rtnVal = true;

int cCount = 0;

foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)

{

if (col.ExternalMetadataColumnID == 0)

{

rtnVal = false;

}

else

{

//mdc = output.ExternalMetadataColumnCollection[col.ExternalMetadataColumnID];

mdc = output.ExternalMetadataColumnCollection[cCount];

if (mdc.DataType != col.DataType || mdc.Length != col.Length || mdc.Precision != col.Precision

|| mdc.Scale != col.Scale || mdc.CodePage != col.CodePage)

{

rtnVal = false;

}

cCount++;

}

}

return rtnVal;

}

This was the other change I needed to make to the example, the collection index doesn't match the column ID.

I'll try to post the full source code online if I get some time so that hopefully it saves someone else the trouble.

Create output columns based on input in custom component

I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.

I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.

I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).

Are there any good examples for creating output columns dynamically based on the input buffer?

Should I just give up on on the transform and create a custom source component instead?

Since there aren't any rows in the buffer until runtime, I don't see how this will work. Packages can't change their metadata (inputs / outputs) at runtime. You could write a source that uses the connection manager at design time to read the first line from the file, and add the output columns, but that would be by directly reading the flat file, not by using a row from the buffer.|||

You could try something like this-

IDTSInput90 input = ComponentMetaData.InputCollection[inputID];

This is a design-time action, in the same way as you would "normally" use the flat file source to load a CSV file, and let the designer UI figure out the columns. This will not allow you to change the file layout at run-time, and magically load any file you happen to find. You area aware of this distinction?

From a design pattern perspective, this is not the place to be selecting and generating columns. It would be more sensible to do this either in a UI or ReinitializeMetadata. Validate could detect the stupid state of no input columns selected, and/or it not matching the input, and call RMD.

A source may be cleaner, or even a package generator. I am not clear on what you are really trying to do, and what problem you need to solve.

|||

Thanks,

I'm trying to solve the problem related to a CSV source missing columns for some rows, it's been brought up a few times here in the past but I haven't seen a generic solution that is suitable for multiple DTS packages that are dependent on multiple CSV files all with 50+ columns.

Here's a forum entry on it:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2025483&SiteID=17

And Jamie T's explanation with more links:

http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_--Flat-File-Connection-Manager-issues.aspx

I'll see if I can get the custom source component working today.

|||

I was able to get the source component working based off an example from Professional SQL Server 2005 Integration Services

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html

(The site has a page for downloading the examples).

The example for creating a source component had a couple errors in it (probably from being based off a pre-release version of SSIS).

Here's some of the key code:

Code Snippet

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager);

if (cm.CreationName != "FLATFILE")

{

throw new Exception("The Connection Manager is not a FILE Connection Manager");

}

else

{

_fileExist = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);

if (_fileExist != Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)

{

throw new Exception("The type of FILE connection manager must be an Existing File");

}

else

{

_filename = ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager.AcquireConnection(transaction).ToString();

if (_filename == null || _filename.Length == 0)

{

throw new Exception("Nothing returned when grabbing the filename");

}

}

}

}

}

The original example checked "if (cm.CreationName != "FILE")" which should actually be "if (cm.CreationName != "FLATFILE")"

Code Snippet

private void CreateOutputAndMetaDataColumns(IDTSOutput90 output)

{

if (_filename != null || _filename.Length > 0)

{

TextReader tr = File.OpenText(_filename);

string columns = tr.ReadLine();

tr.Close();

_columnNames = columns.Split(",".ToCharArray());

foreach (string columnName in _columnNames)

{

IDTSOutputColumn90 outName = output.OutputColumnCollection.New();

outName.Name = columnName.Trim();

outName.Description = columnName.Trim();

outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);

//Create an external metadata column to go alongside with it

CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outName);

}

}

}

Just to get the sample working all columns are strings for the moment, for my needs this is all I needed anyways.

Code Snippet

private bool DoesEachOutputColumnHaveAMetaDataColumnAndDoDatatypesMatch(int outputID)

{

IDTSOutput90 output = ComponentMetaData.OutputCollection.GetObjectByID(outputID);

IDTSExternalMetadataColumn90 mdc;

bool rtnVal = true;

int cCount = 0;

foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)

{

if (col.ExternalMetadataColumnID == 0)

{

rtnVal = false;

}

else

{

//mdc = output.ExternalMetadataColumnCollection[col.ExternalMetadataColumnID];

mdc = output.ExternalMetadataColumnCollection[cCount];

if (mdc.DataType != col.DataType || mdc.Length != col.Length || mdc.Precision != col.Precision

|| mdc.Scale != col.Scale || mdc.CodePage != col.CodePage)

{

rtnVal = false;

}

cCount++;

}

}

return rtnVal;

}

This was the other change I needed to make to the example, the collection index doesn't match the column ID.

I'll try to post the full source code online if I get some time so that hopefully it saves someone else the trouble.