Renaming a SQL Reporting Service File

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.