Hi Guys
I had the requirement to export the excel spreadsheet data into the remote SQL Server database , the user expects to choose the file from the local machine then the VB program should export the data into the remote sql server table.
There are so many possibilities to do that
1) Data Transformation services
2) Data Tansformation Service with the Arguments
2) Distributed Queries
3) Add Linked Servers
I tried with all the above , but i failed.Then i choosed the Data Transformation services with the argument and its working fine with me.The VB code is below:
Function ExecutePackage()
'Purpose : To Execute a package that has been created in SQL Server DTS with the argument.
Dim oPKG As DTS.Package, oStep As DTS.Step
Set oPKG = New DTS.Package
Dim sServer As String, sUsername As String, sPassword As String
Dim sPackageName As String, sMessage As String
Dim lErr As Long, sSource As String, sDesc As String
' Set Parameter Values
sServer = <Server Name>
sUsername = <User Id>
sPassword = <Password>
sPackageName = <DTS Package name that has been created in the remote DB>
' Load Package
oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
DTSSQLStgFlag_Default, , , , sPackageName
oPKG.Connections.Item("Connection 1").DataSource = sFileName ' To Change the FileName as argument to the DTS package
'For i = 1 To oPKG.Connections.Item("Connection 1").Properties.Count
' Debug.Print oPKG.Connections.Item("Connection 1").Properties.Item(i).Name & " --- " & oPKG.Connections.Item("Connection 1").Properties.Item(i).Value
'Next
' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next
oPKG.Execute
' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next
oPKG.UnInitialize
Set oPKG = Nothing
End Function