SQL Server Performance

Steps to create a Package

Discussion in 'SQL Server DTS-Related Questions' started by ziicraja, Aug 7, 2005.

  1. ziicraja New Member

    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
  2. ranjitjain New Member

    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.
  3. satya Moderator

  4. ziicraja New Member

    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.
  5. ziicraja New Member

    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
  6. satya Moderator

    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.
  7. Madhivanan Moderator

  8. ziicraja New Member

    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
  9. Madhivanan Moderator

    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
  10. ziicraja New Member

    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
  11. Madhivanan Moderator

    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
  12. ziicraja New Member

    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
  13. Madhivanan Moderator

    >>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
  14. ziicraja New Member

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

    SHUKRAN.. (Arabic way of saying Thankx)
  15. Madhivanan Moderator

    Did you get the solution?


    Madhivanan

    Failing to plan is Planning to fail
  16. ziicraja New Member

    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
  17. Madhivanan Moderator

    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
  18. ziicraja New Member

    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
  19. ziicraja New Member

    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
  20. Madhivanan Moderator

    I dont know
    Probalbly you need to import them and use them from your local server

    Madhivanan

    Failing to plan is Planning to fail
  21. ziicraja New Member

    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.
  22. Madhivanan Moderator

    What is the error you get?

    Madhivanan

    Failing to plan is Planning to fail
  23. satya Moderator

Share This Page