SSRS Special Date Stamp Format Name Changing Scheduled report: DTS package do your thing…

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.

filesystemtask

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?

variables1

click on the tab Package Explorer

variables2

this will dropdown and give lots of choices..

variables3

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.

connectionmanagervariables

Next we will add these variables and connections to the file system task.

filesystemtaskproperties

add event handler that will send an email on the execution of the file system task

eventhandle1

eventhandle2

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.

attachementrenamingexpressionbuilder

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)

sendmailundereventhandlers2

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

manifesto2

select deployment utility and then under create deployment utility change the value to true.

manifesto3

then build the package.

manifesto4

navigate the deployment output folder and double-click the file with the “manifesto” extension.

this will open the installation wizard

manifestowizard

click Next

filevsql

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.

savefile

then click finish and now you will have a .dts package that you can schedule in sql.

About dorothyjarry

Super Dots
This entry was posted in SSRS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s