data migration

Question

data migration

vithasun

Mr or Mrs. 500

Points: 538
More actions
January 28, 2010 at 11:29 am

#144561

Hello Everybody,
I hope all are doing great.
I am new to sql server migrations.
I appreciate if someone can share their experiences in migration approach from flat file to sql server 2005 using SSIS tasks, transformations and error handling.(without wizards)
Thanks in Advance
veena.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Well, you would use BIDS (Business Intelligence Development Studio) and do a new SSIS project. You'd have at least one DataFlow task that would include a Flat File source that uses a connection to your flat file and a OLE DB Destination that uses connection to your SQL Server. Then you would use the transforms needed to complete your task(s). Without knowing what exactly you have to do I can't tell you what transforms you would use. I regularly use Lookups and Derived Column transforms.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

vithasun Mr or Mrs. 500 Points: 538 More actions · Answer 2

Hi Jack,

Thank you very much for the reply.

Could you please elaborate your experences using look up transformation.

Thanks alot,

Veena.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

There's not much to expand on. You define a query, select the column(s) to match on and the column(s) to pass through. You just need to be aware that it is Case and space sensitive.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

vithasun Mr or Mrs. 500 Points: 538 More actions · Answer 4

vithasun

Mr or Mrs. 500

Points: 538

January 30, 2010 at 11:33 am

#1111222

Hi Jack,

Oh, Ok. Thanks alot.

Veena

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

@jack-2 Corbett

Please correct me if I'm wrong...

When loading many data, Lookup Transformation might become a performance issue, doesn't it? At least if keys cannot be cached.

I'd prefer a staging table when loading many rows.

Greets

Flo

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Florian,

Yes, the lookup can be a performance killer if you can't cache the lookup rows because it may have to query the lookup source for each row. Lookups perform better when the lookup table is small.

They still aren't as bad as UNION ALL's or SORT's

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question