SQL Server Performance Forum – Threads Archive

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.

May check this BLOGhttp://codebetter.com/blogs/raymond.lewallen/archive/2005/05/04/62781.aspx is any help. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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

Then you can take help of LINKED SERVERS in this case, may check in books online for more information. Using DTS also you can define the import of rows to a temp. table and then run a query to import those required rows to base table in SQL serve.r Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

>>From EXCEL TO SQLSERVER See if this helps you
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=8440
Madhivanan Failing to plan is Planning to fail

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

Insert all records to a newtable
Then Check these against your original table which records are new and insert them accordingly Insert into(columnlist) originaltable
Select columnlist from newtable where keycol not in (select keycol from originaltable) Madhivanan Failing to plan is Planning to fail

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

When Selecting Datefield in your table use convert(varchar,datefield,101) Simlarly when selecting time, use convert(varchar,timefield,114) Madhivanan Failing to plan is Planning to fail

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

>>is it possible for the user to create the Excel sheet dynamically and give the path of his own? Bcp will do this Exec master..xp_cmdshell ‘bcp "Select columns from DBname..tableName" queryout "C: est.xls" -c’ There is no need to create test.xls file
After you run the query it will be created automatically The problem you may have is this file will only have data and not headers Madhivanan Failing to plan is Planning to fail

Thankx Madhivanan,letme try this out and get back to you. SHUKRAN.. (Arabic way of saying Thankx)

Did you get the solution?
Madhivanan Failing to plan is Planning to fail

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

I think you need to import it to a local system and refer that path
Otherwise Give the file name as you specified and see what happens Madhivanan Failing to plan is Planning to fail

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 dont know
Probalbly you need to import them and use them from your local server Madhivanan Failing to plan is Planning to fail

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.

What is the error you get? Madhivanan Failing to plan is Planning to fail

May check KBAhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to resolve this DTS scheduled job issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>