Flat File Connection with different row types

I have a file format that uses many rows to describe relationships between one-to-many entries. Basically in many tables I one file with the only relations between tables been the order of the rows.

From what I’ve found so far, the Flat file Connection is not the correct Connection type to use for this. Is there something better suited, or documentation of creating Connection types.

Cheers Simeon.

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamiesql

FKs

Does anyone have a script that will evaluate the foreign key relationships in a db and generate a script to drop and re-create them?you might be able to write the sql procedure by using the sysforeignkeys table|||What will this be used for ?|||declare @.cr nchar(2)
declare @.go nvarchar(8)

set @.cr = nchar(13)+nchar(10)
set @.go = @.cr + 'GO' + @.cr

declare @.tablename nvarchar(128)
declare @.column nvarchar(128)
declare @.schema nvarchar(128)
declare @.constraint nvarchar(128)
declare @.fktable nvarchar(128)
declare @.fkconstraint nvarchar(128)
declare @.onupdate varchar(9)
declare @.ondelete varchar(9)
declare @.comma char(1)

declare @.sql nvarchar(4000)

declare cstrts cursor local fast_forward read_only for
select
c.[TABLE_SCHEMA],
c.[TABLE_NAME],
u.CONSTRAINT_NAME
from [INFORMATION_SCHEMA].[COLUMNS] c
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on c.[TABLE_NAME] = u.[TABLE_NAME]
and c.[COLUMN_NAME] = u.[COLUMN_NAME]
inner join [INFORMATION_SCHEMA].[table_constraints] t
on u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
where t.[CONSTRAINT_TYPE] = 'FOREIGN KEY'

open cstrts

fetch next from cstrts
into @.schema, @.tablename, @.constraint

while @.@.fetch_status = 0
begin
select
@.fktable = u2.[TABLE_NAME],
@.fkconstraint = r.[UNIQUE_CONSTRAINT_NAME],
@.onupdate = r.[UPDATE_RULE],
@.ondelete = r.[DELETE_RULE],
@.column = u.[COLUMN_NAME]
from [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u2
on r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
where r.[CONSTRAINT_NAME] = @.constraint

set @.sql =
'ALTER TABLE ['
+ @.schema
+ '].['
+ @.tablename
+ '] ADD CONSTRAINT ['
+ @.constraint
+ '] '
+ @.cr
+ 'FOREIGN KEY (['
+ @.column
+ ']) REFERENCES ['
+ @.fktable
+ '] ('

-- for each ordinal in the foreign key index...
declare idx cursor local fast_forward read_only for
select c.[COLUMN_NAME]
from [INFORMATION_SCHEMA].[COLUMNS] c
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on c.[TABLE_NAME] = u.[TABLE_NAME]
and c.[COLUMN_NAME] = u.[COLUMN_NAME]
where u.[CONSTRAINT_NAME] = @.fkconstraint
order by u.[ORDINAL_POSITION]

open idx

set @.comma = ''

fetch next from idx
into @.column

while @.@.fetch_status = 0
begin
set @.sql = @.sql + @.comma + '[' + @.column + ']'
set @.comma = ','

fetch next from idx
into @.column

end

close idx
deallocate idx

set @.sql = @.sql + ') ON DELETE '
+ @.ondelete
+ ' ON UPDATE '
+ @.onupdate
+ @.go

print @.sql

fetch next from cstrts
into @.schema, @.tablename, @.constraint


end

close cstrts
deallocate cstrts
-------

See also my attachment to 'Restoring Databases from Win2000 to Win2003/Collation' which uses a drop and recreate to all forms of indexes to recollate a databse.

Hope This helps

HH|||Thanx, this is going to be used in conjunction with a proc that truncates all of the user tables in the db. I'm building a new system and need to frequently purge the data in the db to test data migration scripts and delete statements take too long to execute.

Fixing a messy database after the fact...?

2 questions, actually:

I am new to database design and a lot of things never made any sense to me regarding relationships and such. I have been working on a very large design that started out well enough, but as tables were added a lot of organization fell by the wayside. Now that I am getting closer to the end, I am finding a lot of places where there should be Foreign keys, maybe some triggers, etc (I have the same data item in 5 different places, when it is deleted in one place it must go from all). Assuming that the datatypes and sizes are identical for the duplicated bits of data, can I go about making FK-PK relationships and such now that there is a lot of stuff in the database, or do I have to start from scratch and rebuild the whole thing.

The other question is much more simple:

How do I make multiple rows "unique". I have a primary key, and an identity column, but I can't add a secong primary key, and Enterprise Manager only lets me make 'int' datatypes identity columns. I have tried the "add constraints" but it asks for an expression and I have no idea what the syntax might be.

Any help is appreciated.Try downloading AdventureWorks for SQL Server 2000 from the first link, copy the installation file into Query Analyzer and execute it. It is an 87 table Database using the Peter Chen ERD model. The second is PPT slides with the book used to create it, only 143 pages but it has a lot of sample Catalogs that will make things a little easier for you. The book is dry and abstract. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?familyid=487c9c23-2356-436e-94a8-2bfb66f0abdc&languageid=f49e8428-7071-4979-8a67-3cffcb0c2524&displaylang=en

http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html

Kind regards,
Gift Peddie