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!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|||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.

Extended Stored Procedures developed in .NET?

Is it possible to create an extended sproc for SQL2K using .NET (ie C#,
VB.NET, etc)? All of the examples I've found focus on C++.
Cheers, JonNo, not in managed code. There's a KB describing that it is not supported fo
r SQL Server to host
managed code. Below is my "canned" answer:
It is not supported for extended stored procedures or sp_OA procedures to ca
ll .NET code in CLR;
hosted within SQL Server's address space..
See:
http://support.microsoft.com/defaul...kb;en-us;322884
Also, below is with permission from David Browne, explaining how you can hav
e SQL Server execute CLR
code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/defaul...kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last w, and I just got around to testing.
It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
news:u3nFUEQGFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create an extended sproc for SQL2K using .NET (ie C#, VB
.NET, etc)? All of the
> examples I've found focus on C++.
> Cheers, Jon
>|||You rock!
Cheers, Jon
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23yum2SQGFHA.1140@.TK2MSFTNGP10.phx.gbl...
> No, not in managed code. There's a KB describing that it is not supported
> for SQL Server to host managed code. Below is my "canned" answer:
> It is not supported for extended stored procedures or sp_OA procedures to
> call .NET code in CLR; hosted within SQL Server's address space..
> See:
> http://support.microsoft.com/defaul...kb;en-us;322884
> Also, below is with permission from David Browne, explaining how you can
> have SQL Server execute CLR code executing in its own process:
> "
> Short answer: Don't do it.
> Calling managed code inside a stored procedure is not supported.
> http://support.microsoft.com/defaul...kb;en-us;322884
> At least not directly. You need some sort of unmanaged proxy to
> communicate
> with your component running in another process.
> For instance, http, or, drum roll, a COM+ Server Application.
> This will cause COM+ to load an unmanaged proxy object in the SqlServer
> process and will load the CLR into a COM+ surrogate process (dllhost.exe).
> Which somebody here mentioned last w, and I just got around to testing.
> It's all perfectly transparent to you, but you have to set up the COM+
> server application.
> Remember this is something different from .net remoting. With .NET
> remoting
> you have a _managed_ proxy object in the local process, and so you load
> the
> CLR in the local process as well as the remote process.
> Anyway here's what I did:
> I created this VB class
> comTest.vb listing:
> Imports System.Runtime.InteropServices
> <ClassInterface(ClassInterfaceType.AutoDual),
> ProgId("comTest.comTestClass")> _
> Public Class comTest
> Public Function Hello() As String
> Return "hello"
> End Function
> End Class
>
> build comTest.dll and registered it with
> regasm /codebase comTest.dll /tlb:comTest.tlb
> (complains that I haven't strong-named my assembly, which you should do.)
> created an empty COM+ server application, set to run under a local
> administrator account, and dragged comTest.dll into its components folder.
> created an unmanaged host (vbscript will do), and invoked the component
> using IDispach just like SQLServer.
> test.vbs listing
> Set d = CreateObject("comTest.comTestClass")
> MsgBox d.Hello
>
> Then I used the .net command line debugger cordbg.exe's 'pro' command to
> list the processes hosting the CLR. And procexp.exe from
> www.sysinternals.com to verify that the CLR's dll's were not loaded in my
> unmanaged process. My unmanaged host did not load the CLR, although it
> loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
> Then in sql I ran
> declare @.object int
> declare @.msg varchar(50)
> declare @.rc int
> declare @.hr int
> declare @.source varchar(1000)
> declare @.description varchar(1000)
> exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'create failed ' + @.description
> return
> end
> exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'method failed ' + @.description
> return
> end
> print 'return: ' + @.msg
> exec @.rc = sp_oadestroy @.object
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'destroy failed ' + @.description
> return
> end
>
> Ran fine, and still only one CLR loaded into dllhost.exe's process. So
> think we can safely conclude that COM+ server applications do not violate
> the prohibition against running managed code in SQLServer's process and
> provide a convenient mechanism for interoperating with managed code from
> TSQL.
>
> David
> "
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
> news:u3nFUEQGFHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||> You rock!
In this case, I'd say that it is David who rock. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
news:%23kLhBsQGFHA.3376@.TK2MSFTNGP12.phx.gbl...
> You rock!
> Cheers, Jon
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23yum2SQGFHA.1140@.TK2MSFTNGP10.phx.gbl...
>sql