There are 3 parameters coming to a Stored Procedure which kicks off a DTS Job.
@Filename For example-- 'Task Summary'
Kicksoff a DTS Task (DTS_Load_Task_Summary)
This Loads the file 'Task Summary' into a Temp table. (not #/##)
Job Step: (sp_createTmpTaskSummary)
Stored Procedure has been created that creates a permanent Temp table (and this works okay)
'+ 'tmp' +''+ @UserNm +''+ @SavedDateLast +''+ 'Task_Summary' +'
For the DTS Filepath, I want this to be dynamic
next time \\yyyyyy\yy$
1) I need a sample of how to get the (variable) Filepath in to the DTS Connection Property
2) I need a sample of how to get the @usernm into the SP (sp_createTmpTaskSummary)
The date is created with getdate() do don't need help with the Date.
3) Related question is where (connection properties?) in the DTS job should I establish
the UNC which says the network
drive where the file is located? I believe I have the permission issues settled.
I have not used parameters in DTS so I am confused. There are parameters behind the SQL
statement and there are parameters for DTS and I have not used these before. I don't know
how they work or how to pass something in. Help please.