April 19, 2005 at 12:29 pm
Hi,
I have created a DTS package which loads the data from XLS file to DB. i am now trying to create a Program(or User Interface) which will allow the user to specify the filename and then execute the package. Can anyone give me an idea to start with this task?
Regards,
Adi
p.s: would be great if someone can give me a link or recommend some reference book for this task.
April 19, 2005 at 1:19 pm
Depends what you mean by Program (or) User Interface.
Is it a Web Application (or) a Windows Application (or) built into SQL Server itself.
WebApplication: You need to create a form with the name of the filename. This form should submit to an action 'asp page' which should call the DTS object. Try to create a function in the DTS package with a global variable to which you can feed the input of the filename. This function will then call your existing ActiveXScript to do the current task.
Windows Application: Same as above.
SQL Server: Write a function say 'getFileName()'. Use a prompt function to which you can send the filename. This filename should be checked for proper format etc., before calling the existing ActiveXScript you are currently using.
April 20, 2005 at 1:53 am
one way of doing this in DTS is though DTSrun utility
1. create a ini file with following tags
[source]
FileName=c:\abc2005_04_10.txt
2. In Dts package create a Activex task with following code.
Function Main()
Dim gsConfigINIPath
gsConfigINIPath = DTSGlobalVariables("gsConfigINIPath").Value
Dim oPKG
Set oPKG = DTSGlobalVariables.Parent
Dim oAssignments
Dim oAssignment
'DTSTask_ DTSDynamicPropertiesTask_1 is the task described as the 'Properties From INI File task
Set oAssignments = oPKG.Tasks"DTSTask_DTSDynamicPropertiesTask_1").CustomTask.Assignments
For Each oAssignment In oAssignments
oAssignment.SourceIniFileFileName =gsConfigINIPath
Next
Main = DTSTaskExecResult_Success
End Function
3. create a dynamic properties task where you can set properties for source
through .ini file name
and have a usual data transformation task.
So each day you just have to change the filename in .ini file.
Then run following command in cmd promt
DTSRun /N "PackageName" /G "{637535C5-8B40-4E39-8B01-18BC7827DE9A}" /F ".Dts file path" /A "gsConfigINIPath":"8"=".ini file path" /W "0"
Note :/G {Package GUID}
HTH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply