SSIS - Import Excel applying transformation on Row

Question

Post reply

SSIS - Import Excel applying transformation on Row

rossetto_ax

Say Hey Kid

Points: 666
More actions
October 22, 2007 at 4:13 am

#126296

Hi !
I'm very new in SSIS 2005, how could I do? to import an Excel file onto SQLServer 2005 table, applying transformation on columns and row read?
I have a file composed by 5 columns (i've also to skip the first two rows)
Column 1 | Column 2 | Column 3 | Column 4 | Column 5
I have to test the Column 2 and applying a new codification by taking the value from a Lookup of another table end copy all the other values in the relative destination.
This in DTS 2000 was too easy, here for me too difficult... 🙁
Thank's for your HELP !
Alex

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

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

okhere SSC-Addicted Points: 443 More actions · Answer 1

Hi ALex,

May I suggest that you separate the tasks into 2 phases:

1. Use SSIS to import the Excel data into a table as it is exactly.

2. Use a following step in the SSIS package, upon the successful completion of 1 above, to do whatever you want using Transact-SQL. For example, you can skip or lookup etc.

Thanks,

Osama

rossetto_ax Say Hey Kid Points: 666 More actions · Answer 2

Osama Kandil (10/22/2007)
Hi ALex,
May I suggest that you separate the tasks into 2 phases:
1. Use SSIS to import the Excel data into a table as it is exactly.
2. Use a following step in the SSIS package, upon the successful completion of 1 above, to do whatever you want using Transact-SQL. For example, you can skip or lookup etc.
Thanks,
Osama

Thanks Osama,

is exactly what I would like to do... But what kind of object in "Data Flow Transformation" I've to take for using Transact-SQL?

Alex

okhere SSC-Addicted Points: 443 More actions · Answer 3

Hi Alex,

It's called: Execute SQL Task.

Here is a link that may be of help.

http://www.sqlis.com/58.aspx

Good Luck!

Osama

rossetto_ax Say Hey Kid Points: 666 More actions · Answer 4

Thanks Osama,

your solution is useful, but do you know if there's a way to made transformation while charging data without a Temp table?

I mean in Data Flows.... with any kind of "Data Flows Transformation".

Thanks

Alex

xanthos SSCrazy Points: 2510 More actions · Answer 5

Looks like a typical scenario to use the lookup transformation task.

See BOL