November 29, 2004 at 5:29 pm
How can I get the LocalPackage to allow
parameters so I can do the following ?
1. Click green "Run" button
2. Enter the "StartNumber"
3. Wait for the job to finish
------------------------------------
Current Setup
1. Open DTS
2. add a "Transform Data Task"
3. click "Properties"
4. Add the following SQL statement
SELECT ITEMNO, DESCRIPTION, STATUS, CATEGORY, AVAILABLEINV
FROM ITEMMAST
WHERE (SUBSTRING(ITEMNO, 1, 5) <> '00000')
ORDER BY ITEMNO
5. Click green "Run" button
6. The job will work
7. Modify the SQL statement as follows
Declare @StartNumber
SELECT ITEMNO, DESCRIPTION, STATUS, CATEGORY, AVAILABLEINV
FROM ITEMMAST
WHERE (SUBSTRING(ITEMNO, 1, 5) <> @StartNumber )
ORDER BY ITEMNO
8. Receive a "invalid statement" error
November 29, 2004 at 8:28 pm
Add an activeX task that runs at the start of your package. In the task, use an inputbox to prompt for the start number.
I assume that you want to use this value instead of "0000" in the first select statement. You can then assign the value to the select statement from the same activex task.
e.g.
Function Main()
Dim SQL
Dim oPkg, oTask, oConnection
Set oPkg = DTSGlobalVariables.Parent
x = InputBox ("Enter the start number")
For Each oTask In oPkg.Tasks
If Instr(oTask.Name, "DataPump") > 0 And oTask.Description = "Some meaningful description" Then
oTask.Properties("SourceSQLStatement").Value = "SELECT ITEMNO, DESCRIPTION, STATUS, CATEGORY, AVAILABLEINV FROM ITEMMAST WHERE (SUBSTRING(ITEMNO, 1, 5) <> '" & _
cstr(x) & _
"') ORDER BY ITEMNO"
End If
Next
Main = DTSTaskExecResult_Success
End Function
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply