Dynamically import an Excel file in SQL using DTS

[Malchik]

Hi,

I created a DTS in SQL that gets an Excel file and imports it in a table. The file source path and name may change, since multiple users will upload it through an Intranet site. I searched a way to dynamically set the source and filename using a Gloabal variable in the DTS and sevral different coding in ASP.NET. Finally I went for the simple, I removed the global variable and use only .NET

Here is the code: (you have to add the Microsoft DTS Package 2 COM as reference)

Dim pkg As New DTS.Package2

Call pkg.LoadFromSQLServer(sServer, sUserName, sPassword, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , _
"ImportEmployeeStats")
pkg.Connections.Item("Connection 1").DataSource = sPath & sFileName

Call pkg.Execute()
pkg.UnInitialize()
pkg = Nothing

In this example I used an SQL Login and Password. If you do the same, make sure to set the DTS with the right credential.


Hope that will help someone else...

Mal'chik