We have a requirement for the naming of a report to have a special date stamp format. From everything I have found this seems an impossible task in SQL 2008 SSRS with out the help of a dts package.
I am really writing this post, because I rarely work dts packages. Often they are the kind of thing that once they are set up they just run.
Created report in MS2008 SQL Reporting services
Scheduled report to write a file to a system file every day at 5:30. This was a simple task done right in subscriptions.
Then I have a scheduled dts package in SQL Business intelligence Development or BIDS that will update the name of the file and then email it to the list of individuals who have been receiving these reports.
First I added a file system Task to my control flow tab. I am actually doing this on two different files, so I have two tasks, one for each file.
Now we need to create some variables to store some information in them.
The not so obvious. Where are the variables and how can you add them?
click on the tab Package Explorer
this will dropdown and give lots of choices..
when you select variables it will give you this handy little area, click the +icon to add a new one. so intuitive ;D
I added a variable “src_file” for the name of the file that needs to be opened and renamed.
Add a new connection this will be where the files will be saved. This is where the renaming of file happens. Navigate to the place where the file is and select the file. Use the expression button to add the code to change the name of the file. Now the name of my connection becomes the name of my file.
Next we will add these variables and connections to the file system task.
add event handler that will send an email on the execution of the file system task
when you select edit then expressions then drop down the expressions and you should see attachments. Add the same code you used to rename the file to go get the attachment.
now when you look it will always have the name of the last file renamed in the attachments when you open to edit the mail in the future. (that’s ok, it gets over written)
To get this into Sql to start scheduling te DTS package.
create a manifesto
right-click on the name of your package and select properties
select deployment utility and then under create deployment utility change the value to true.
then build the package.
navigate the deployment output folder and double-click the file with the “manifesto” extension.
this will open the installation wizard
I am saving mine to a file system. this way you could potentially add it to any sql instance. if you save it to the sql it is basically stored in the sql server that it is deployed to and you would need to do this again to move it to another server.
then click finish and now you will have a .dts package that you can schedule in sql.