TECH SUPPORT

Data-Driven Package Schedule Wizard

Follow

 

Data-driven Package schedules allow you to use dynamic report data that is retrieved from an external database table at run time to populate the values for a set of reports. You can also use static text and default values should you wish. This allows you to allows you to write a single schedule consisting of multiple reports for each destination and saves you the trouble of having to write and maintain multiple data-driven schedules for each report.

 

This schedule is perfect if you have several reports that share common parameter values that need to be delivered to a common group of recipients.

 

For a full explanation of Data-Driven Schedules, review Data-Driven Schedule.

 

To start the Wizard, Go to the Home Tab, and select Data Driven Package.

 

image1274.jpg

image1277.jpg

 

Continue to General - Data-Driven Package Schedule Properties

General - Data-Driven Package Schedule

image1278.jpg

 

Unlike a Data Driven Schedule, a Data Driven Package will determine the data driver further into the schedule. In this Section we will define the general properties of the package.

 

Package Name:  The name of the package.

 

Create in: The SQL-RD folder where the package is stored.

 

Description:  A short description to help other users identify exactly what this schedule is and what it is expected to do

 

Keyword:  Enter some keywords which can be used later by Smart Folders to identify this schedule.

 

Continue to Data-Driven Package - Schedule

Schedule - Data-Driven Package Schedule

image1279.jpg

 

Determine your scheduling frequencies on this screen.

 

In this section, you will decide when the report will execute. There are a variety of options:

 

image865.jpg

 

Daily: Run a report every day or at a frequency of days.

Sub options:

Repeat every X Days-  EX. Run the schedule every 3 days.

 

image866.jpg

 

 

Weekly: Run a report on a weekly time frame.

Sub options:

Repeat every X weeks-  EX. Run the schedule every 2 Weeks.

On- Select the specific days of the week the schedule will run. If only once a week, select only the day of the week it will run.  EX. Run every Monday, Wednesday, and Friday.

 

 

Weekdays: Run the schedule Monday through Friday.

 

image868.jpg

 

 

Working Day: Run the schedule starting on a specific day of the month. Indicate which day of the month the schedule will run. E.G. run the schedule on the 4th working day of the month.

 

image1526.jpg

 

 

Monthly: Run the schedule on a monthly time frame.

Sub options:

Use the following options- Checking this box will enable you to select frequency options such as the “last Thursday of the month.” Also you can include or exclude specific months from the schedule.

 

image869.jpg

 

 

Annually: run the schedule every year at a specified time.

 

image870.jpg

 

Custom Calendar: Select the custom Calendar you wish to use. You can create a new custom calendar from the menu as well. Please see Custom Calendars for a tutorial.

 

image871.jpg

 

Other: Other scheduling options.

Sub options:

Run Schedule every X Minutes, hours, days, weeks, months, years

 

None: No scheduling is required for this item.

 

image872.jpg

 

Next, you will specify the time that the schedule will run as well as repetition options.

 

 

Schedule time:  Specify the exact time you want your schedule to run. *Tip: keeping in mind how long it takes for your report, time your schedules accordingly!

 

Exception Calendar: choose a calendar that will instruct the schedule to NOT run on those specified days. Please see Custom Calendars for a tutorial.

 

image873.jpg

 

Next to run on: displays the next run date of your schedule.

 

image83.gif

 

Repeat ever X hours or minutes:  run your reports on a reoccurring basis during a specific time frame. EX Repeat every .25 hours until 5PM.

 

image85.gif

 

Enable this schedule: to enable the schedule leave the box checked. To create this schedule disabled, deselect this box.

Once you have finished creating the timing for your schedule, click finish to proceed to the next stage.

 

Continue to Data-Driven Package - Data-Driver

 

Data-Driver - Data-Driven Package Schedule Properties

image1281.jpg

 

We will now build the data driver. The data driver determines the source for the values that you are going to use in the schedule.

 

This is where you specify the source and Data selection criteria for the Data Driver.

 

Click on Build to begin creating your criteria.

 

image1604.jpg

 

DSN Name:  Drop down and select a DSN.  

 

We strongly recommend System DSNs so that the DSN is visible to SQL-RD even when the user associated with the DSN is logged off.  

 

All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

 

When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in SQL-RD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.

 

When using Windows Authentication, make sure that the SQL-RD NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

 

When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID SQL-RD should use to log on to the database

Password:  Enter the password associated with the above user.

 

Click “Connect” to connect to the data source.

 

Parse out your selection criteria using the Simple or Advanced Query Tool.

 

Click Parse to reveal the results of your query.

 

image1283.jpg

 

Based on your selection criteria, your reports will run against the data in this table.

 

Click OK to return.

 

image1285.jpg

 

 

Key Column: Select the key column for the data that is returned for the reports. The information that drives the schedule is required to have a key column so that each row in the table is uniquely identified by the value in this column. The key column is there as an identifier which is used to troubleshoot in cases where there is a problem with one of the records.

 

Group reports by email address/directory: You can instruct SQL-RD to group reports that are sent to the same email address. Please note that enabling this option disables the ability to embed the reports in the email body for email destinations. You can also instruct SQL-RD to group the reports that are sent to the same directory (for disk destinations).

 

Merging Multiple Reports

 

Where the reports are grouped you can also instruct SQL-RD to optionally merge the output of the grouped reports into a single file (PDF and MS Excel formats only). Selecting each option will bring up the options for the output format that you can further customize.

 

Continue to Data-Driven Package - Output

 

Output - Data-Driven Package Schedule Properties

image1286.jpg

 

Reports in a Data Driven Package have a common destination. Based on your criteria you can specify which recipient will receive the reports in email, disk, SharePoint or any other schedule type.

 

Add:  Click to add another destination.  For more information on adding destinations please review Destinations.

Edit:  Select a destination and click to edit it's properties.  Or simply double-click on the destination.

Delete:  select a destination and click this button to delete it.

 

Click  "import"  to import from the list of default destinations.

 

Customizing the Destination

 

image1605.jpg

Similar to the Data-Driven Schedule, the package version can drive destination information based on values from your table. Using the inserts window, drag your data driven data to the desired fields in your output.

 

Continue to Data-Driven Packaged Schedule - Report

 

Data-Driven Packaged Schedule - Report

 

 

image1607.jpg

 

In this section you will add reports to your package. The Data Driven Package drives multiple reports against a single table. As reports do not need to share a common parameter, it is possible to manage numerous reports from one schedule.

 

Edit: Edit an already existing report in the package.

Remove: Remove a report from the package.

Add: add a report to the package.

 

Adding a Report

 

image1608.jpg

 

Report Service URL: Enter your SSRS report server location

Report Location: Browse to your desired report.

Report Name: Name the report, or use its default name.

 

*Hint: SQL Azure Reporting Services- when writing your report, make sure you have enabled Forms Authentication. You should use the 2010 .asmx address for your reporting service URL.

 

Format: Select the format for the report. For more information see Output Formats

 

image1609.jpg

 

Similar to the Single Data Driven Schedule, use Data Driven Data inserts to populate your various values into your parameter fields.

 

image1610.jpg

 

You can edit your database login credentials by double clicking on the appropriate database.

 

Refresh: Pull the latest version of the SSRS report. This ensures the schedule runs with the most up to date report and data.

 

Preview: View the report prior to runtime.

 

image1611.jpg

 

The output file name can be either the report default, or a customized file name and/or date stamp. Use Data Driven data inserts to populate database values into the file name.

 

image1612.jpg

 

Set up blank report handling for this specific report in the package.

 

Continue to Exception Handling - Data Driven Package

Exception Handling - Data Driven Package

image1613.jpg

 

This Section determines what SQL-RD will if it encounters errors or blank reports This is exception handling for the entire package not the individual reports. To set exception handling for specific reports, you can do so in the Data-Driven Packaged Schedule - Report tab.

 

 

Treat as “error” if not completed in X minutes: if a report takes longer than the specified amount of time to run, this option will treat the schedule as an error and follow the appropriate action. The “Auto-calculate” option instructs SQL-RD automatically determine how long a schedule should take to run the report. If it takes longer than the calculated amount of time, then it is an error.

 

*Note: if manually determining the error timing, please double check the runtime of the report in order to get the correct time estimate.

 

On error, retry executing schedule every X minutes up to Y times: If an error has occurred with the schedule, you can set the interval and number of times you wish the schedule to attempt a retry. By default SQL-RD will make 3 attempts.

 

image890.jpg

 

SQL-RD and Blank Reports:

 

SQL-RD has the ability determine if a report is blank. Meaning that if a report genuinely has no data behind it, then SQL-RD can do a number of things with the report and the schedule.

 

Check if a report is blank: With this option you can check if a report is blank.

 

Ignore the report: if the report is blank, do not send the report. The report will not be delivered to the destination.

 

Method Tab: Select the Method that will determine whether a report is blank.

 

Native: SQL-RD will check to see if the report returns any data. If not, the report is considered blank.

 

 

image1525.jpg

 

SQL Query: Select this option to use a user made query that will determine if the report is blank. If the query returns no results, the report is blank.

File Size Check: If the file fails to be a certain file size then the report will be considered blank.

 

image892.jpg

 

Actions Tab: Select an action from the task list. This task will be executed in the event that a schedule is blank.

 

image893.jpg

 

*Tip: You can send a notification if a report is considered blank instead of sending the report. Simply select “check if a report is blank” then select “Ignore the report.” In the actions tab, select “Send Email” from the list. Compose your email and save.

 

Continue to Data-Driven Schedule - Tasks

Data-Driven Schedule - Tasks

image1614.jpg

In the section you have the option of setting up custom tasks. Custom tasks are business process automation tools that can be auto triggered before or after a report runs. For more details on Custom Tasks, please see Automation Schedules for a full description of each task.

 

*Hint:Use data driven inserts to populate different tasks with values from your table.

 

If you have no desire to add a Custom Task, you can click the finish button to complete the schedule.

 

History - Data-Driven Package Schedule Properties

image1603.jpg

 

This screen shows you a history of the selected schedule.  To see a history of all schedules, please use the System Monitor.

 

Clear: Use this option if you want to clear all the history for a schedule.

 

Refresh: Use this option to refresh all the schedule history.

Data-Driven Package Context Menu

image1441.jpg

 

Right-Click on a schedule to see the following actions.

 

Add Report:  Use this to add one or more reports to an existing package.

 

Copy:  Use this to copy an existing schedule.  You can then paste it into another folder or the same folder.  Simply open the folder you wish to paste to, right-click in the "white" space and select "Paste".  Follow the on-screen instructions to complete the process.

 

Paste:  Paste a previously copied schedule

 

Rename:  Rename a schedule

 

Enabled:  Schedules are Enabled when there is a check beside this option.  To stop a schedule from running, or to "pause" it for a while, select this option to remove the check.  Disabled schedules will not run till they are enabled again.

 

Refresh Package: When a schedule is first created, SQL-RD caches (saves) a copy of the report.  All executions of the scheduled are performed using this copy.  If you make changes to your master report, you must select this option in order to pull the changes into SQL-RD. This refreshes all the reports included in the package.

 

Open:  Click this option to open the package in a new window in SQL-RD.

 

Execute Schedule:  This will execute the schedule immediately.  Note that the next run date and time is not moved on as a result of a manual execution.  This only moves on if the schedule is run automatically by one of the schedulers.  

 

Delete Schedule:  Selecting this option will delete the schedule.

 

Properties:  Selecting this option will bring up the "Properties" module containing configuration information for that schedule.  For more information on Properties, click here.

 

Tools:

 

Split into Single Schedules: This allows you to split the package into Single Report schedules.

 

Create Shortcut: Use this option to create a shortcut you can save in any location on your PC. Execute the shortcut to execute the schedule in SQL-RD.

 

 

 

Comments