February 19, 2006 at 10:51 pm
Hi.
I desgined a DTS which will load .XLS file which static file name. For instance, mydata.xls. I have a ASP.NET (vb.net) application which will allow user to "upload" the file from client machine to webserver.
In the applicaiton, the uploaded file will be saved in the server as "mydata.xls". Upon saving the file, application will call a SP which is basically to execute DTS package which will upload data from "myData.xls" into a table called "dataMaster". Below is the SP codes:
exec master..xp_cmdshell 'dtsrun /S "myServer" /U "user" /P "pwd" /N "fileUPLOAD"'
IF @@ERROR <> 0 -- Error reported DTS failed
Begin
print 'Error: DTS has been failed !'
RETURN 99
End
Else
Begin
print 'DTS is success !'
RETURN 0
End
Now, I have more than one users and they all have to upload .XLS file (format is same) from their machine. In that case, I can not use "static" file name as datasource file name. Currently I use "myData.XLS".
I need to use a Dyanmic file name as my datasource file (.XLS file and format remain same) in my DTS to upload data into SQL table.
How can I do this in DTS ? I'm new to DTS so, pls advise bit more details.
Regards,
February 20, 2006 at 5:56 am
I assume they are all the same structure. However do you have to make the data available immediately? Or can it be periodicall? Also, which version of SQL 7/2000/2005?
Just dicussed this Friday with a friend needing the exact same thing but his was a once a day thing. Basically the way we with 7 and 2000 he creates two packages.
Package 1
ActiveX Script
Using FileSystemObjects open the path they are in and loop thru the list of filenames.
Step 1 Get File Name
Step 2 pass path and name into second package global
Step 3 if success delete the file and move to next (he only need until complete you could move the file or whatever else you need to).
Package 2
Package to do work, 1 string global variable
Step 1 ActiveX Script take global variabl value and set Excel Connection object value to path and filename
Step 2 On Success Run Transformation.
Then you just schedule package 1 to run every so often.
Have example code of each if you need bu out today so not readily available.
February 20, 2006 at 4:09 pm
use dynamic properties task and pass the name as global variable...
-Krishnan
February 20, 2006 at 9:51 pm
<<use dynamic properties task and pass the name as global variable...-Krishnan >
Could you please explain me bit more in details enabling me to modify my existing DTS. In the meantime, i will try myself to implement it.
appreciate your help..
February 21, 2006 at 6:57 am
What Krishnan is saying is this instead of
Package 2
Package to do work, 1 string global variable
Step 1 ActiveX Script take global variabl value and set Excel Connection object value to path and filename
Step 2 On Success Run Transformation.
Do
Package 2
Package to do work, 1 string global variable
Step 1 Dynamic Properties Task take global variabl value and set Excel Connection (datasource) object value to path and filename
Step 2 On Success Run Transformation.
This is simpler than using the ActiveX Script I just never had used in the past so was overlooking it.
Thanks Krishnan.
March 7, 2006 at 3:35 am
In Package you will see that There are Connections exists (DTS.Connection), In Package you can know that Which connection is for your Read your Excel File. Pickup that Conection and In DataSource you pass Excel File With Complete Path
For e.g. In My Package I am porting Data From Excel to SQL Table. Then it is like as bellow
Dim oPkg As New DTS.Package
Try
oPkg.LoadFromSQLServer("ServerName", "sa", "password", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "Test1")
Dim objFile As New
oPkg.Connections.Item(1).DataSource = "D:\Anil\MyExcelFile.xls"
oPkg.Execute()
Catch ex As Exception
MsgBox(ex.Message)
Finally
oPkg =
Nothing
End Try
Thats all
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply