|
SSIS Reporting Services Task (SSRS 2005, 2008, 2012)
|
 
|
|
SSIS Reporting Services Task (SSRS 2005, 2008, 2012)
|
|
 |
SSIS Report Generator Task can be used to export or email SSRS reports inside SSIS Package. This task also supports dynamic data driven export which can export several reports to disk and email to specified users based on settings stored in sql table.
|
| Property Name | Description |
|---|---|
| Action | Specifies action you want to perform
|
| TimeoutSeconds | Report generation timeout in seconds |
| ReportPath | If ReportType=Server then this is report path on the server [i.e. /{ReportFolder}/{ReportName}]. Example: /AdventureWorks Sample Reports/Product Catalog
If ReportType=Local then this is report definition file path. Example: C:\Reports\ProductCatalog.rdl |
| ReportParameters | Parameter values for this report.
Syntax: {PARAMETER_NAME};{MULTI_VALUE_FLAG};{VALUE_STRING}
Example: InvoiceNumber;False;INV1234|SubjectLine;False;This is your invoice|Amount;False;5678.55Example (Multi Value Parameter): ProductCategory;False;1|Country;False;USA|States;True;GA,TN,AL,NYExample (Binding value from SSIS Variable - When UseDirectValueForParameter=false): ProductCategory;False;User::varCategory|Country;False;User::varCountry|States;True;User::varStates |
| UseDirectValueForParameter | By default parameters value is set from SSIS Variable but when you set this property to True then you can type any value directly. |
| DatasetNames | Dataset name(s) for local report dataset binding. Use vertical bar if you have multiple datasets (e.g. Dataset1|Dataset2...). This option is valid only if you set Action=ExportLocalReport. Dataset name must exists in the report and Dataset name is case-sensitive |
| RecordsetVariable | Variable name which will hold dataset/datatable or ado recordset to bind with local report datasets defined by DatasetName property. If you have dataset with multiple datatables inside it then make sure they all appear in the same order as listed in DatasetName (e.g. Dataset1|Dataset2...). This option is only applicable if you have selected action = ExportLocalReport. |
| DatasetConnectionStrings | Dataset name(s) for local report dataset binding. Use vertical bar if you have multiple datasets (e.g. Dataset1|Dataset2...). This option is valid only if you set Action=ExportLocalReport |
| DatasetQueries | Dataset queries(s) separated by vertical bar if you have multiple datasets in report. for local report dataset binding. Use vertical bar if you have multiple datasets (e.g. Dataset1|Dataset2...). This option is valid only if you set Action=ExportLocalReport |
| ReportServiceUrl | This is report web service Url (Example: http://myserver/reportserver ). Click here to learn how to find Reporting Service URL |
| UserName | Username to connect report server |
| Password | Password to connect report server |
| WindowsAuthentication | Check this if you connecting to ReportServer using current credentials under which SSIS package is running |
| ConfigDataConnection | SSIS Connection manager for Config Database. This connection is used to connect database where Config Table is stored for DataDrivenExport |
| ConfigTableName | Configuration Table name where records for DataDrivenExport is stored. Click here to read more about DataDrivenExport option |
| ConfigFilter | SQL Filter which can be used to limit which Reports needs to be included or excluded in DataDrivenExport. (e.g. ReportType='Local' AND GroupName='WEEKLY') |
| ExportFilePath | Indicates local file path where exported report should be saved. (e.g C:\Reports\MyReport.xls).
For ReportType=Server valid extensions for file is PDF, DOC, XLS, TIFF, CSV, XML, MHT or HTM. For ReportType=Local valid file extensions are PDF, DOC and XLS. This option is only valid when you ExportAsFile to True. |
| GenerateSnapshot | Generate snapshot after report is executed |
| AppendSuffix | Indicates whether custom date/time suffix should be added to exported file name. If this option is checked then specify ExportFileSuffix |
| SuffixFormat | Suffix for exported file. You can use any valid Date/Time format specifiers of Microsoft .net (Example: _yyyy-MM-dd will format file name as MySalesReport_2015-12-31.pdf ) (Click here to read more about date time format specifiers) |
| ExportAsFile | If set then report is exported to disk |
| ExportAsEmail | If set then report is emailed to specified recipient(s) |
| EmailSMTPConnection | SMTP Connection Manager name which should be used to send emails (This is also used by DataDrivenExport) |
| EmailFrom | From address for email |
| EmailTo | To list for email. You can separate multiple recipients using semicolon (e.g. user1@abc.com;user2@abc.com) |
| EmailCc | Cc list for email. You can separate multiple recipients using semicolon (e.g. user1@abc.com;user2@abc.com) |
| EmailBcc | Bcc list for email. You can separate multiple recipients using semicolon (e.g. user1@abc.com;user2@abc.com) |
| EmailSubject | Email subject line (You may use following format specifiers)
|
| EmailBody | Email body (text or HTML). You can use following format specifiers.
|
| EmailAsHtml | Indicates whether email should be in HTML format or plain text |
| LoggingMode | LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log. |
CREATE TABLE [dbo].[SsrsExportConfig]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[GroupName] [varchar](100) NULL,
[ReportType] [varchar](10) NOT NULL,
[ReportServiceUrl] [varchar](500) NULL,
[ReportPath] [varchar](500) NOT NULL,
[IsEnabled] [bit] NOT NULL DEFAULT ((1)),
[ExportAsFile] [bit] NOT NULL DEFAULT ((1)),
[ExportAsEmail] [bit] NOT NULL DEFAULT ((0)),
[Parameters] [varchar](max) NULL,
[ExportPath] [varchar](500) NULL,
[AddFileSuffix] [bit] NULL,
[ExportFileSuffix] [varchar](500) NULL,
[Description] [varchar](500) NULL,
[EmailFrom] [varchar](255) NULL,
[EmailTo] [varchar](1000) NULL,
[EmailCc] [varchar](1000) NULL,
[EmailBcc] [varchar](1000) NULL,
[EmailSubject] [varchar](500) NULL,
[EmailIsHtml] [bit] NULL DEFAULT ((1)),
[EmailBodyText] [varchar](max) NULL,
[EmailAttachmentFile] [varchar](500) NULL,
[LastExportStartDate] [datetime] NULL,
[LastExportEndDate] [datetime] NULL,
[LastStatus] [varchar](20) NULL,
[LastMessage] [varchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Here is the list of columns with description for DataDrivenExport Configuration Table.
| Column Name | Datatype | Allow NULL | Description |
|---|---|---|---|
| Id | int | NO | Unique record number (auto generated) |
| GroupName | varchar(100) | YES | Group name for this report. Informational purpose only. e.g DAILY, WEEKLY, SALESREPORTS. This helps to filter report when DataDrivenExport is executed. |
| ReportType | varchar(10) | NO | This value can be any of the following values
|
| ReportServiceUrl | varchar(500) | YES | This is report web service Url (Example: http://myserver/reportserver ). Click here to learn how to find Reporting Service URL |
| ReportPath | varchar(500) | NO | If ReportType=Server then this is report path on the server [i.e. /{ReportFolder}/{ReportName}]. Example: /AdventureWorks Sample Reports/Product Catalog
If ReportType=Local then this is report definition file path. Example: C:\Reports\ProductCatalog.rdl |
| IsEnabled | bit | NO | If not set then DataDriven export skips this entry |
| ExportAsFile | bit | NO | If set then report is exported to disk |
| ExportAsEmail | bit | NO | If set then report is emailed to specified recipient(s) |
| Parameters | varchar(MAX) | YES | Parameter values for this report.
Syntax: {PARAMETER_NAME};{MULTI_VALUE_FLAG};{VALUE_STRING}
Example: InvoiceNumber;False;INV1234|SubjectLine;False;This is your invoice|Amount;False;5678.55Example (Multi Value Parameter): ProductCategory;False;1|Country;False;USA|States;True;GA,TN,AL,NY |
| ExportPath | varchar(500) | YES | Indicates local file path where exported report should be saved. (e.g C:\Reports\MyReport.xls).
For ReportType=Server valid extensions for file is PDF, DOC, XLS, TIFF, CSV, XML, MHT or HTM. For ReportType=Local valid file extensions are PDF, DOC and XLS. This option is only valid when you ExportAsFile to True. |
| AddFileSuffix | bit | YES | Indicates whether custom date/time suffix should be added to exported file name. If this option is checked then specify ExportFileSuffix |
| ExportFileSuffix | varchar(500) | YES | Suffix for exported file. You can use any valid Date/Time format specifiers of Microsoft .net (Example: _yyyy-MM-dd will format file name as MySalesReport_2015-12-31.pdf ) (Click here to read more about date time format specifiers) |
| Description | varchar(500) | YES | Description of this record |
| EmailFrom | varchar(255) | YES | From address for email |
| EmailTo | varchar(1000) | YES | To list for email. You can separate multiple recipients using semicolon (e.g. user1@abc.com;user2@abc.com) |
| EmailCc | varchar(1000) | YES | Cc list for email. You can separate multiple recipients using semicolon (e.g. user1@abc.com;user2@abc.com) |
| EmailBcc | varchar(1000) | YES | Bcc list for email. You can separate multiple recipients using semicolon (e.g. user1@abc.com;user2@abc.com) |
| EmailSubject | varchar(500) | YES | Email subject line (You may use following format specifiers)
|
| EmailIsHtml | bit | YES | Indicates whether email should be in HTML format or plain text |
| EmailBodyText | varchar(MAX) | YES | Email body (text or HTML). You can use following format specifiers.
|
| EmailAttachmentFile | varchar(500) | YES | NOT USED - For future use only |
| Datasets | varchar(MAX) | YES | If ReportType=Local then use this field to indicates how many datasets you have in report file (Use vertical bar to list multiple datasets). Example: MyDataset1|MyDataset2 |
| DatasetConnectionStrings | varchar(MAX) | YES | If ReportType=Local then use this field to indicate connection strings for each dataset listed in Datasets. If you have different connection strings for each dataset then list connection string in the same order as your dataset and seperate them using vertical bar. Right now only ADO.net for SQL Server and OLEDB Connection Strings are supported.
Example (Use of ADO.net Driver for SQL Server): Data Source=localhost;Initial Catalog=MyDB1;Integrated Security=True;|Data Source=(local);Initial Catalog=MyDB2;Integrated Security=True; Example (Use of OLEDB Driver): Data Source= localhost;InitialCatalog=MyDB1;Provider=SQLNCLI.1;Integrated Security=SSPI;|Data Source= localhost;InitialCatalog=MyDB2;Provider=SQLNCLI.1;Integrated Security=SSPI;Example (Use of ADO.net and OLEDB Driver): Data Source=localhost;Initial Catalog=MyDB1;Integrated Security=True;|Data Source= localhost;InitialCatalog=MyDB2;Provider=SQLNCLI.1;Integrated Security=SSPI;Example (Use of ODBC Driver): Data Source=localhost;Initial Catalog=MyDB1;Integrated Security=True;|Data Source= localhost;InitialCatalog=MyDB2;Provider=SQLNCLI.1;Integrated Security=SSPI; |
| LastExportStartDate | datetime | YES | Indicates when report export process started |
| LastExportEndDate | datetime | YES | Indicates when report export process ended |
| LastStatus | varchar(20) | YES | Indicates status of last export (i.e. STARTED, FAILED, SUCCESS) |
| LastMessage | varchar(MAX) | YES | Last export message. if LastStatus was FAILED then this field contains error message |
