Data in excel file to SQL Server Database table

[mike55]

Hi,

 

I have managed to take the data stored in a SQL Server database table and have written it to and excel file. I am now trying to figure out what is the best approach for doing the reverse to this, i.e. excel to SQL Server.

 

Any suggestions? Am going to look at excel -> xml -> sql server.

 

Mike55

[PlausiblyDamp]

You may want to look at SQL's Data Transformation Services - you can find then in Enterprise Manager, they should be able to allow you to set this up and even automate it.

[mike55]

You may want to look at SQL's Data Transformation Services - you can find then in Enterprise Manager' date=' they should be able to allow you to set this up and even automate it.[/quote']

 

 

SQL's DTS is not an option in this case as this function must be available to the users of the application. Effectively, they will provide the location of a file on their local machine, they then click the process button, I then go to their machine, upload the file to the server, transfer the data to the SQL database, and hey presto we're done. One of the biggest problems will be security and authentication, i.e. prevent duplicate data being submitted.

 

Is it possible to call the SQL's DTS from code?? Would'nt think so, but it may be a long shot option if it can be done.

 

Mike55

[Afraits]

You can call a DTS package from code as I've maintained code that does it, unfortunately I don't have access to that code anymore. :o But it can be done.

[Anthony_n]

if the Xp_cmdshell is not disabled, create a stored proc and exec DTSrun

 

if not then there is a com object

 

Public Sub Call_DTSPKG(ByVal Name As String, ByVal ServerName As String, ByVal UserName As String, ByVal Password As String)
       '*****************************************************************************************
       'Function: Call_DTSPKG
       'PURPOSE: Runs a DTS
       'PARAMETERS: Name As String, ServerName As String, Username As String, Password As String
       'CALLED By: Mail_Sort
       'SIDE EFFECTS: NONE
       '
       '
       'You have to add the ref Microsoft DTS Package from the com objects menu or you will
       'get a error on the line Dim DTSItem As New DTS.Package
       '
       '*****************************************************************************************

       On Error GoTo MyErrHndl
       Dim DTSItem As New DTS.Package
       Dim Msg As String


       DTSItem.LoadFromSQLServer(ServerName, UserName, Password, 0, , , , Name)
       DTSItem.WriteCompletionStatusToNTEventLog = True
       DTSItem.FailOnError = True
       DTSItem.Execute()


       DTSItem = Nothing

       Exit Sub
MyErrHndl:
       MsgBox("DTS Failed" & vbCrLf & vbCrLf & Err.Description)
   End Sub