DTS package in a cursor
-
I have a DTS package with global vaiables. This vairables decides the output of the query.
Now i run a cursor on the table and fetch theese global variables. Each time variables are fetched my DTS package runs and exports the data in the excel file, sends the mail and deletes the file...
But the problem, is that it attaches the file for the first record in the cursor and does not attaches the file for the consequest records. The code of the cursor looks like this....
**************************
DECLARE @Command varchar(100), @Job_Type varchar(10), @Dept_Name varchar(30),
@Dept_id varchar(10), @Email varchar(50)
SET @Job_Type = 0
DECLARE mail_cursor CURSOR GLOBAL READ_ONLY FORWARD_ONLY STATIC FOR
SELECT distinct jsd.Dept_id, jsd.Dept_Name, jsd.Email FROM job_status js inner join
job_status_dept jsd on js.dept_id = jsd.dept_id WHERE Job_Type = @Job_Type
OPEN mail_cursor
FETCH NEXT FROM mail_cursor INTO @Dept_id, @Dept_Name, @Email
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC master..xp_cmdshell 'del X:\dailyfiles\job_status.csv', no_output
SET @Command = 'dtsrun /S(local) /Usa /Pxxxxx /Nabc /ADept_id='+@Dept_id + ' /AJob_Type='+ @Job_Type
EXEC master..xp_cmdshell @Command, no_output
procedure for sending the mail....
FETCH NEXT FROM mail_cursor INTO @Dept_id, @Dept_Name, @Email
END
CLOSE mail_cursor
DEALLOCATE mail_cursor
***************************
The error log file of the DTS package look like this
does any1 have a solution to this..
******************************
Package Name: abc
Execution Started: 2/18/2003 9:17:05 PM
Execution Completed: 2/18/2003 9:17:05 PM
Total Execution Time: 0.079 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 2/18/2003 9:17:05 PM
Step Execution Completed: 2/18/2003 9:17:05 PM
Total Step Execution Time: 0.079 seconds
Progress count in Step: 2
*********************************************
The execution of the following DTS Package succeeded:
Package Name: abc
Execution Started: 2/18/2003 9:17:06 PM
Execution Completed: 2/18/2003 9:17:06 PM
Total Execution Time: 0.031 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: Access is denied.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0
Step Execution Started: 2/18/2003 9:17:06 PM
Step Execution Completed: 2/18/2003 9:17:06 PM
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: Access is denied.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0
Step Execution Started: 2/18/2003 9:17:07 PM
Step Execution Completed: 2/18/2003 9:17:07 PM
Total Step Execution Time: 0.031 seconds
Progress count in Step: 0
*******************************
-
Two things,
1) It looks like you're using the same filename for each record. Does the file get deleted successfully? Maybe your email process is stopping the file from being deleted which in turn is preventing it from being re-created.
2) Why not append the @Dept_id and @Job_type variables to the file name to have a unique name?
eg:
SET @Command = 'del X:\dailyfiles\job_status_' + @Dept_id + '_' + @Job_Type + '.csv'
EXEC master..xp_cmdshell @Command, no_output
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
-
quote:
Two things,1) It looks like you're using the same filename for each record. Does the file get deleted successfully? Maybe your email process is stopping the file from being deleted which in turn is preventing it from being re-created.
2) Why not append the @Dept_id and @Job_type variables to the file name to have a unique name?
eg:
SET @Command = 'del X:\dailyfiles\job_status_' + @Dept_id + '_' + @Job_Type + '.csv'
EXEC master..xp_cmdshell @Command, no_output
Thanks
Phill Carter
Hello Sir
I had thought that about it, i can achieve the same , but in the DTS Designner how do i give the destination file name as with such kind of parameters....
In the DTS desiner when i take the Excel file with destination, it ask for the file name, and that needs to be the valid file name... If u know how to give file name with
variables, please tell me...
Thnks for the comments
-
Check out
http://www.sqldts.com/default.aspx?6,101,200,0,1
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply