Urgent..Transfering Excel to Sql server tables...

Question

Urgent..Transfering Excel to Sql server tables...

elegantkvc

SSCommitted

Points: 1910
More actions
May 21, 2008 at 1:30 am

#122340

hi,
I had an excel contains two sheets which have the data of timesheet of a company.
I had successfully transferred the excel into sql tables by creating SSIS Package using SSIS Import or Export wizard.
The problem is daily we are getting Excel sheets i want to get the
data to be added to those existing tables.But now i have to delete the existing tables and append the daily sheet to the existing excel sheet and export....How to get this done...Help me out friends.....
Thanks & Regards
vijay

Viewing 11 posts - 1 through 10 (of 10 total)

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

Micha van der Ende Mr or Mrs. 500 Points: 507 More actions · Answer 1

Hi vijay ,

Did you just run the Import and Export Wizard without any modifications to the package, because it will add an Execute SQL Task before the Data Flow Task that will first create a table from a copy of the Excel worksheet in the SQL DB.

Did you modify the package after the wizard??

regards

Micha

elegantkvc SSCommitted Points: 1910 More actions · Answer 2

hi Micha,

I havent modified the Package once i have defined it using the SSIS Import Export wizard.I just executed the package and got the results.

But the thing is how i can update the SQL tables on the basis of excel sheets generated daily.Thats the thing i want to know Micha...

Micha van der Ende Mr or Mrs. 500 Points: 507 More actions · Answer 3

Hi vijay,

You have to modify the package you created with the wizard (are you familair with SSIS...??). This Execute SQL Task have to be removed as soon as you've created the table in the database.

Do you append the data to the existing Excel sheet or do you get an new Excel sheet on a daily basis. That is important to know before you start modifying your package.

For sure it's possible to update the SQL table with the data from your Excel on a daily basis, even with a delta load.

Rgrds

Micha

elegantkvc SSCommitted Points: 1910 More actions · Answer 4

hi,

Am a newbie in SSIS .....I had removed the Preparation SQL Task in the control flow...Now the creation of new table got avoided and datas get appended to the existing tabel....but even though i have to genrate two packages for a single task....Any other way mate

The Excel sheets am getting is on the daily basis, the column names will be the same..The first time only i got to create the sql table...afterwards i want to update the tabel with the excel data on each day...

vijay

Micha van der Ende Mr or Mrs. 500 Points: 507 More actions · Answer 5

Hi vijay,

What you can do is to add a line in the Preparation SQL task to check if the table already exists in the database:

IF OBJECT_ID('dbo.TableName') IS NULL

CREATE TABLE dbo.TableName(

col1 int,

....

col10 int)

This will check if the table exists. If it doesn't then the table will be created else the sql will be omitted.

You still have to do some work in the dataflow to prevent re-importing data already in the database.

Tip: Also a good way to work with are variables, which are very powerfull in SSIS.

rgrds

Micha

elegantkvc SSCommitted Points: 1910 More actions · Answer 6

hii miche,

Thanks a lot dude...It works now.:)...My first job in SSIS works...awesome....Can u send me any materials regarding learning about the SSIS and creation of cubes using Analysis services...Because when i surf through the net i couldnot able to understand about the terminologies like factet table,dimension fully...If you give me any material regarding those topics it will be really helpful for me my friend...Because my next job is to create a cube based on the table ....

Thanks & Regards

vijay

Micha van der Ende Mr or Mrs. 500 Points: 507 More actions · Answer 7

Well, then I can recommend for sure the following books:

Professional SQL Server 2005 Integration Services: http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html

Expert SQL Server 2005 Integration Services:

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

The Microsoft Data Warehouse Toolkit: http://www.amazon.com/gp/product/0471267155/qid=1128605285/sr=2-1/ref=pd_bbs_b_2_1/002-4814565-4213606?s=books&v=glance&n=283155

And of course this website.....;)

I all have these books and you even can buy them used at amazon.

rgrds

Micha

elegantkvc SSCommitted Points: 1910 More actions · Answer 8

hi,

Thanks a lot for those links mate:)...Now am going in for devloping cube ..Any particular book for developing cubes using SQL Server Analysis server 2005.

Regards

vijay

Micha van der Ende Mr or Mrs. 500 Points: 507 More actions · Answer 9

Hi,

I'm not that familiar with AS, except for testing and playing around with it. I more into Cognos.

But you can also search http://www.wrox.com

I find the books from this publisher very pleasant to read.

micha

elegantkvc SSCommitted Points: 1910 More actions · Answer 10

hi mate,

Thanks a lot yaar for those links and for sorted out my query.

Thanks & regards

Vijay:)