Install it, Configure-it & Run-it…

Do you have a 32 bits Windows environment or 64 bits Windows environment?

For 32 bits environment, you have to copy the dll from the SSISReportGeneratorTask110-x86.zip archive to “C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks\

For 64bits environment, you have to copy the dll from the SSISReportGeneratorTask110-x64.zip archive to “C:\Program Files\Microsoft SQL Server\110\DTS\Tasks\

I made for you a little PowerShell script that helps you to add-it to GAC (GACInstall.ps1 in attachment – Download Page)

 

Set-ExecutionPolicy Unrestricted; #Confirm from Popup
[Reflection.Assembly]::LoadWithPartialName("System.EnterpriseServices")
[System.EnterpriseServices.Internal.Publish] $publish = new-object System.EnterpriseServices.Internal.Publish;

#32 bits - comment or uncomment in function of your environment
# $publish.GacInstall("C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks\SSISSFTPTask110.dll");
#64 bits - comment or uncomment in function of your environment
# $publish.GacInstall("C:\Program Files\Microsoft SQL Server\110\DTS\Tasks\SSISSFTPTask110.dll");

You return to your Business Intelligence Development Studio aka Visual Studio 2012, you click-right to SSIS Toolbox and the refresh submenu.

 

image

 

and voilà the component.

image

 

.. so, drag it into the design surface

And now create some file connections… that will point to some file formats (PDF, Word, Excel, HTML 4.0, MHTML, CSV, XML)

image

PS: the SMTP connection is optionally and is used only if you want to send the generated file by email.

Add some variables, as you see in the image bellow (we will talk about this a little bit later):

image

Double-click on the component and you will see this:

image

So… you choose @[User::ReportServer] and you click the “Go” button:

In the TreeView you will see the Hierarchy (the Tree Structure) of your selected SQL Server Reporting Services Server, you choose your Report (for example Invoice Report)

image

… and as you see you obtained some details about your report.

 

Let’s detail a little bit the content of the window and I will point into the “Action Panel”:

    • Export as: you have a list of supported formats

image

    • At this path:
      • if you chosed “Path from File connector” – you will obtain all File Connections you already created previously
        • image

       

      • if you choose “Path from Variable / Expression” you will get a list with all STRING variables (User&System)
        • image

 

Regarding the name of the report used you can configure it as well:

image

         In fact, when you click in the treeview on a node, the name (for example Invoice) will be automatically specified as the ‘Selected Report’ but you can specify a variable or an expression as the name of the report.

 

Let’s continue with the other tabs description:

  • Parameters
    • image
    • image
      • We have the name of the parameter
      • Parameter’s type
      • It is nullable?
      • The State of the parameter
      • The Default Value
      • Variables: contains a pick list with the variables (System/User) of the Parameter Type
      • f(x) column/button permits to build an expression for your parameter
  • Data Sources
    • image
    • The “Name” column contains the name(s) of the used DataSource(s)
    • The “reference” column contains the path(s) of the used DataSource(s)
  • Security
    • image
    • That’s the list of the users that can “attack” the report
  • Dependent Items
    • image
    • A list of dependent items –> Sub reports, etc…

Finally, if you want to receive the report by email you have a checkbox “Send the file by Email”. Checking the checkbox you will enable the button “E-mail details” and you will got this:

image

A popup window where you have to fill the classical email elements to send an email: Sender, Recipient(s), Subject and Body. As you see, you can use the variables (for From and To items) and optionally an Expression for the Subject and The Body of the email.

Finally, that’s our configured component:

image

Let’s execute it:

 

image

The confirmation email

image

And the log:

image

 

Good luck!

Last edited Jun 3, 2014 at 3:53 PM by cosminfr, version 13

Comments

No comments yet.