Steps to create a Package

Hi friends

I think DTS is a solution for the problem I have and this is my first project using this technology, Hence I need help from you guys[:I]

Scenario

I have a client who uploads an excel file to a specific location and has given me a link for eg:http://00.00.00.00/excelfile/empattend.xls

Seems this excel sheet is being created dynamically from a source (legacy database) and scheduled for daily upload and hence the empattend.xls has a only a row appended to it. (it already has thousands of rows of data).

My problem:[V]
I need to create a table in SQL SERVER using the data from the Excel sheet, as am manipulating something with the table created, that is say if the Excel sheet has 13 columns but my destination table has 17 columns, I use this table to do some calculations and change some data except the DATE field which is crucial, now I need to schedule a package so that, my table is appended only newly created row(s) and already existing data is untouched.

I tried DTS using a local copy of an Excel sheet to create a table in the database and everything is fine, but the only problem is I need to append only the fresh data, whereas the old data remains intact (untouched), hope you can understand my problem, Someone please do help me out in this regards.

Cheers
Raja

Hi Raja,
As you need to append only fresh data then it depends on the rows which are there in Excel File.
If the file has only fresh valid rows of data then you can simply set append data to existing table while creating DTS.
Else if the excel file contains Old Data with newly inserted Data then you have to truncate the SQL table and then insert the data from excel into SQL.
So again in this case while creating DTS you can set the option of delete data from table before inserting.

Thankx for the replies pals,

Dear Ranjit Jain, as you said I have the excel sheet with the old data and the newly added data, I cannot delete the data from the SQLServer as I said I have some other columns which is being updated during some calculations are done,hence the records in the SQL Server should never be touched but to append the fresh data.

Satya, I'll check the blog you have recommended.

Thankx again.

Satya I checked the blog but it says how to "Generate an Excel XLS spreadsheet from T-Sql in Sql Server" I need to know the other way around, From EXCEL TO SQLSERVER

Thankx Satya and Madhivanan,

Madhivanan I tried ur way it works fine but my concern is about to update only fresh data, guess can give a try to what Satya has suggested dumping into a dummy table and then appending it to the existing but still how do u append only the newly added rows to the dummy table, guess am not niggling you guys and you people don't mind, am really new to this stuff.. Thankx

Thankx dudes
I made it work, used DTS to import data from excel to a dummy table and scheduled a job to dump the data to original table and used a query to update only the fresh(current data), it works great, Thankx again.

Madhivanan, there is a question for you,

I used one of your queries to export data to excel sheet, it works fine, but I need to transform date and time fields before I export the data, coz' I get some jumbled data in those columns, for example, I have one column which has only

date like 6/13/2005 but when it's exported to excel it is like this Jun 13 2005 12:00AM, I don't need the time here,

the other column has only time for example 7:35 AM but it shows like Dec 30 1899 7:35AM which is meaningless, can you pls let me know the transformation which has to be done to get my desired output. Thankx

That's great and a prompt reply Madhivanan, that did help me, Thankx a lot.

I have a another question if you think am not bugging you, is it possible for the user to create the Excel sheet dynamically and give the path of his own?

thankx

Thankx Madhivanan,letme try this out and get back to you.

SHUKRAN.. (Arabic way of saying Thankx)

Dear Madhivanan
Things are working great, Thankz for your help.

I have one question...

I am trying to create a DTS or may be I can also create a job to be scheduled to append a table from an Excel Sheet available somewhere in the internet, for instance the client has given me an URL likehttp://###.##.##.##/something/excel.xls and I need to directly call this Excel sheet to append a table in SQL SERVER

How do I do this....


Best Regards

Thankx for the reply Madhivanan
I gave the path as it is but it says cannot open the file, I need to schedule this import every morning around say 5AM, this import is most important for other schedules to run properly, we cannot keep any local systems on for this to happen, is there any other alternate solution for this?

Best Regards

Madhivanan, The database is online and I need to create a job to update a table from an excel file which is also available online as I said above http://###.##.##.##/something/excel.xls).

How can I schedule this to happen every day.

Best Regards

I tried that Madhivanan, only that particular Job fails.
when I do the DTS and run immediately it succeeds, but when I schedule it, it fails.