Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Unanswered: Pulling data from an excel sheet

    I need to pull data from an excel sheet and load the data into a SQL table. This job should be done as a part of a SP. Because there is a lot of SQL code that needs to execute right after the file is loaded. We get like 20 of these files everymonth. I created a job and executed it using xp_cmdshell and DTSRun from my SP. But then the user needs be in sysadmin role or the account under which MSSQLServer runs should be a part of local administrators group. Both are not a very good from a security standpoint. So what are my options? What if I use opendatasource ???? I would doing some dyanmic SQL, but looks like there is no other option. Looking for ideas.

    Your time and thoughts are greatly appreciated.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Why not use dts and execute the sp within dts after the file has been loaded ?

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Or within the sp you can use sp_start_job.

  4. #4
    Join Date
    May 2002
    Posts
    73

    excel file

    How he could load the information to table. Sorry Iīm not familiar with dts or sp, but are those kind of ready-made scripts?

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    jeremas - When creating a dts package, you can either use a wizard or create one from scratch. When using the wizard, basically you select a source, destination and any transformations. The source is the file (in this case) - the destination is the database where you want the source loaded - the transformations allow you to map the columns in your source file to columns in your table ... also allowing you to manipulate the data as well before it is stored in the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •