Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Exclamation Unanswered: Externally triggered DTS to import excel data to SQL server

    I need to extract data from an excel file to my SQL Server 2000 database. Users used to do this themselves through an ASP script I developed but some data in certain cells are invariably lost, NULL value instead is recorded (according to Microsoft this is the problem w/ using excel as a data source).

    To get around this problem I asked my users to send me their excel files so I could import the data manualy using SQL Server's Import Data facility. But, this is not acceptable. They should be able to do this themselves w/o my intervention.

    There is already an "upload file to server" facility that they can use. And after uploading I was thinking of using DTS to automatically import the data from excel. But the DTS package is normaly executed based on a set schedule. What I need is for users to upload the excel file to the server, then for them to trigger the DTS package w/o directly accessing the SQL server database.

    Is this possible? Can I create a stored procedure that will execute the DTS package? I'm not quite familiar w/ stored procedures although I'm trying to learn more about it right now.

    Here's a sample excel data source, info.xls:
    Name Age State
    John Smith 30 NY
    Anne Collins 25 CA
    Mike Peterson 22 TX

    Destination db and table: dbUser, tblInfo
    Fields: tName(nvarchar, 50), iAge(numeric, 3), tState(nvarchar, 2)

    Any assistance on this will be highly appreciated. Thanks!

  2. #2
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    You could get a stored procedure to start the scheduled job which is running the DTS package. A basic stored procedure to run the job would be:

    CREATE PROCEDURE sp_StartDTS

    AS

    BEGIN

    EXEC msdb..sp_start_job @job_name = 'The DTS job name'

    END

    You can also use the job id etc... do a search in the Books Online for sp_start_job and you'll get the syntax. There is also a success/fail return code which you could use in the ASP page

  3. #3
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Externally triggered DTS to import excel data to SQL server

    Originally posted by jasper627
    I need to extract data from an excel file to my SQL Server 2000 database. Users used to do this themselves through an ASP script I developed but some data in certain cells are invariably lost, NULL value instead is recorded (according to Microsoft this is the problem w/ using excel as a data source).

    To get around this problem I asked my users to send me their excel files so I could import the data manualy using SQL Server's Import Data facility. But, this is not acceptable. They should be able to do this themselves w/o my intervention.

    There is already an "upload file to server" facility that they can use. And after uploading I was thinking of using DTS to automatically import the data from excel. But the DTS package is normaly executed based on a set schedule. What I need is for users to upload the excel file to the server, then for them to trigger the DTS package w/o directly accessing the SQL server database.

    Is this possible? Can I create a stored procedure that will execute the DTS package? I'm not quite familiar w/ stored procedures although I'm trying to learn more about it right now.

    Here's a sample excel data source, info.xls:
    Name Age State
    John Smith 30 NY
    Anne Collins 25 CA
    Mike Peterson 22 TX

    Destination db and table: dbUser, tblInfo
    Fields: tName(nvarchar, 50), iAge(numeric, 3), tState(nvarchar, 2)

    Any assistance on this will be highly appreciated. Thanks!
    to overcome bad data in the Excel spreadsheet you could import the data into a holding table that will allow nulls or other bad data, then run some SQL over the table identitfying good records by updating a bit field in the table. If the types of data errors are known and can be fixed automatically eg NULL should be 0 then you could fix that either in the DTS package with a VB script or later with SQL.

Posting Permissions

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