Introduction
You need to rename a Report
file render to
\\Server\Public\Reports\Test directory after SQL Server Reporting
Services 2005
Subscription was
executed and created that file there. This must be done so that the consumer of
the report can have
an archived stock of reports
with dates. E.g 01_02_thru_03_20_2008Production_RGU_Report and so and so on.
Using the code
Solution: There are two ways
to solve this problem, but both do address the problem of how do you know
when the subscription services
have completed the creation of the report file. In this article we will
look at both options. Option 1 Use SSIS to create a file System Task
to perform the rename.
Option 2 Write an SQL Script using sp_ReplaceFileOrDirNames function to rename
the file.
Image left out
Both options will do the work
of renaming the file but which one will be able to synchronized with
Subscription Services??? We are about to find out.
Go to the screen on the snap
shot below by logging on to your server, expand the nodes, then right click on
Job Activity Monitor, and then click View Job Activity. Now find the Job Name
for the subscription Event,
which may be something like
6FD6B328-692B-4749-A6E1-E4E760CC0A5D. Double click on it.
Once you are there, you
will be able to do a new step, delete, edit or insert into this step.
Here is where you want to
click Insert, and follow the prompt to complete the new step.
ImageLeft out
You will be using master..xp_cmdshell
store procedure, to rename the production Production_RGU_Report. By having a
T-SQL stored procedure rename the file,
you avoid having to use
both a Subscription Event and a programmer having to come after wards to rename
the file on a daily basis.
Here is the T-SQL
SET @fn=REPLACE(convert(char(8),getdate(),1),'/','')
SET @cmd ='Ren \\Server\MyShared\New Folder\Report.xls \\chrd0dv02\MyShared\New
Folder\Production_RGU_Reports '+@fn+'.xls'
Select @fn,@cmd
EXEC master..xp_cmdshell @cmd
Image left out
You can type any name you want in the Step Name box. You can now select Transaction-SQL script (T-SQL)
for Type. Then Type in the SQL Script above of any appropriate script for renaming the files you want
to rename. Make sure that the paths
are correct for your system.
Image left out
Let us say that you had an SQL Server integrated service package store on this server or
any server on your network. Change Type to "SQL Server integrated service package" then
find the Package on the appropriate server. In the second
article I will discuss creating that Package to do the rename.
After adding the second step, make sure that you use the up down button to set
this new step that we have created as the second step after the report is run.
So now we have the first Step 6FD6B328-692B-4749-A6E1-E4E760CC0A5D, then the
second step Rename file. This job will run as a combined package.
Points of Interest
Editing an automate job created by Reporting
Service 2005 Subscription.
History
Later this week I will submitting an article on
creating a SSIS package to rename a file in sync with the Subscription Time
event.
