Flat File to SQL table

I am looking to evaluate a text field in the row and change it to an integer in the sql table based on the text.

What is the best data flow tranformation for me to use inbetween the flat file source and the ole db destination?

it depends on what logic you are using for your evaluation but Derived Column will probably do it. If not, the script component.

-Jamie

|||Can you help with an example If then expression?|||

With the information you have provided, no. What evaluation do you want to do?

-Jamie

|||

Something like:

If [Treatment] = "No Deposit Required" then 1 else 0

I'm not sure how to write this in an expression.

|||

OK

[Treatment] == "No Deposit Required" ? (DT_I4)1 : (DT_I4)0

-Jamie

|||Thanks. your great..

Flat File Connection with quote encapsulated fields

I’m trying to evaluate SSIS for managing the import of files that have quote encapsulated fields, but not all fields are encapsulated. The fields are encapsulated to allow for embedded commas.

For example:

AAA,"BBB",CCC,"DDD","EEE","FFF",GGG,"HHHH",IIII

AAA,"BBB",CCC,"DDD","EE,E","FFF",,"HH,H",

When importing this file I have to alter the delimiter from column AAA to deal with the quote from column BBB like this:

{,}"

And BBB would look like

“{,}

Now I can go through each column for our files doing this by hand, but I wonder if there is some way to note that some fields are encapsulated. Thus it would be more wizard like. Or the ability to alter the Advanced fields while on the Preview or Columns to reduce the jumping across pages.

Also is the gramma for delimiters described anywhere?

Simeon

You should use the "Text Qualifier" property of the Flat File Connection Manager. In this case, you would specify " to be the Text Qualifier. Best part is if some columns are qualified, and some are not qualified, it will still handle them both.

Let me know if you have any problems using the Flat File Connection Manager.

thanks
Ranjeeta

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.