Skip to content
November 22, 2013 / Rodrigo Chiolas

SSIS – Dynamic location for Execute Package Task

Problem

On a recent project, with a SSIS component, i needed to alternate between the  visual studio solution (filesystem) and the deployed solution (DB). The problem was the bazillion of child packages, Execute Package Tasks,  that the project had. For those not familiar with SSIS, “Execute Package Tasks ” component allows to call other packages, either from filesystem or DB.

Naturally, i don’t want to manually change from DB to filesystem, and vice versa, every time i switch enviroments. So i needed to find a solution to dynamically execute those child packages.

Solution

To provide a way to quickly, and easily, change enviroments we gonna use expressions of the “Execute Package Tasks ” component.

Steps:

  1. Create a variable, called DBorFilesystem, that will be used to alternate between the enviroments. The variable will have the values DB or Filesystem.
    create_variable
  2. Open the “Execute Package Task Editor” (double click the component), go to the “Expressions” tab and click on the “…” icon.
    open_expressions
  3. Create 2 properties expression:
    1. Connection – to alternate between the db connection and the package connection
    2. PackageName – to specify which package from db should be executed
      create_expressions
  4. For the “Connection” property, edit the expression (click “…”) and insert:
    @[User::DBorFilesystem] == "DB"  ? "<db_connection_name>": @[System::TaskName] +".dtsx"

    where <db_connection_name> is the db connection name where the packages are. If the component name it is different of the name of the package connection, replace @[System::TaskName] +”.dtsx” with the name of the package connection.

  5. For the “PackageName” property, insert:
    @[User::DBorFilesystem] == "DB"  ? "\\"+ @[System::TaskName] :""

    If the component name it is different of the name of the package , replace @[System::TaskName]  with the name of the package (without the extension “.dtsx”).

And thats it.

If you have multiple levels of child packages (package calling package that call package and so on), configure the variable “DBorFilesystem” on a package configuration, either a xml file or a sql server. Try not to use parent variable, because parent variable configuration are executed after the package validation, which means that the package will be validated with the default value of the package variable DBorFilesystem, not with the parent variable value.

Regards,

Advertisements
October 28, 2013 / João Lobato Dias

SSIS: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property

Problem:

Last Tuesday I stumbled on a odd error message in SSIS:

“Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.”

At the moment, I was trying to use a File System Task with a Delete file operation to delete a temporary file. The oddest thing about it was that the error was being thrown after the file had been successfully deleted.

Technical Explanation:

This (seemingly not very enlightening) error message is related to two properties common to all SSIS tasks: ExecutionValue and ExecValueVariable. The former can be used by a task to return an interesting value; the latter is the variable where that value will be written, becoming accessible to the following tasks.

In hindsight, the error message is pretty accurate: the failing task is trying to return a value, but it is unable to fit it in the selected variable.

Solution:

Turns out that it was a data type mismatch.

When configuring the task, I chose a variable for the Source propertyBy default, the same variable was chosen for the ExecValueVariable property. Since the source was a string path and the return value was an integer (i.e., the number of deleted files), the task was throwing an error after performing the operation.

ssis_properties_executionvaluevariableIn order to solve the issue, either point the ExecValueVariable to an Int32 variable or choose the <none> parameter (thus ignoring the return value). Note that a File System Task with a Delete file operation will succeed even if the file weren’t there in the first place. You’ll need to check the ExecutionValue if you want to use that piece of information.

Helpful links:

http://www.sqlis.com/sqlis/post/Have-you-used-the-ExecutionValue-and-ExecValueVariable-properties.aspx

September 26, 2013 / Tiago Epifânio

BI Publisher – Best Practices for Modeling RTF Templates – Part 2/2

This post is the follow-up to the original post BI Publisher – Best Practices for Modeling RTF Templates – Part 1/2.

Never use Internet Explorer to edit your Data Model

Not only because you have better browsers than that (I won’t try to explain why in this blog) but because you will most certainly have bad surprises. You will get errors when trying to run your data model (or report) and they will only get fixed when you redo all Links/Group Links in your data model. Which kinda sucks, by the way.

I’ve been using Firefox and Chrome and these bug never occur.

Turn off the auto run option

Let’s face it. If your database isn’t crowded yet, it will be in the future. Chances are that you will have bazillions of records in just a few days/weeks/months.

What happens when you try to open a report that will load bazillions of records? My guess is it will take a long long time to load with a probability for out of memory/disk space error.

The problem is that when you click on the “Open” link of a report, it will run automatically before you can even fill in some filters. What you need to know is that there is an option in the report properties which tells Publisher to auto-run the report when you click open. This option defaults to True and you should change it to False:

  1. Find your report in the Catalog
  2. Click Edit:

    Edit report

  3. Click Properties
  4. Deselect the “Auto Run” option and click OK:

Auto run

Create a maximum records parameter

This is related to the previous tip. Even if you have the auto run option turned off, after you have opened the report there are some actions that you can do that will automatically run the report. For instance:

  1. If you switch to another template:
    Switch template
  2. If you switch the output format for your report:Switch output format

To avoid this, go to your Data Model and create a parameter that will set a maximum number of rows your query will return.

For instance, create a parameter called max_rows, set its default value to 10 or 100 or whatever you like, and then use it in your main query like this:

WHERE :max_rows is null or :max_rows >= rownum

The “:max_rows is null” part makes the query return all rows if the parameter is not filled with any value.

Validate Template

Always click on “Validate Template” (BI Publisher plug-in for MSWord) before uploading your template to your site. It won’t check every single detail in your template but it will warn about some basic issues like forgetting to close an xml tag. At least you will not waste time checking your log files for this simple errors.

RTFM

You always should read the er… friendly manual. But chances are that you won’t. So, keep track of the useful resources you find online.

I always keep this page open in one tab in my browser:

Creating RTF Templates Using the Template Builder for Word

 

Cheers.

 

Link to the first part of this article: BI Publisher – Best Practices for Modeling RTF Templates – Part 1/2.

August 5, 2013 / Rodrigo Chiolas

SSRS – Calendar Date Picker Parameter with MDX

Problem

Almost all reports i build have a date parameter. When creating a dataset with a date parameter, using Reporting Services with Analysis Services, a list of values is generated automatically.

Although it works and gets the job done, it is not pratical, more than a month and it starts to be annoying scrolling all those values.

Fortunately, it is possible to implement a calendar date picker as a parameter of a cube report.

Solution

Steps to implement a calendar date picker in SSRS 2008.

  1. Create a report
  2. Create the date parameter or edit the parameter generated automatically when you create a main dataset

    SSRS_datepickerpost_create parameter

  3. Go to Report Parameter Properties and change the data type to Date.SSRS_datepickerpost_change datatype
  4. Set Available Values to None

    SSRS_datepickerpost_available values

  5. If you want a default value, set Default Values to Specify Values and use, for example,  the following expression  =CDate(format(Globals!ExecutionTime,”yyyy-MM-dd”))

    SSRS_datepickerpost_default_values

    SSRS_datepickerpost_default_value expression

  6. Now, for each dataset where the date parameter is used, go to Dataset Properties -> Parameters and edit the Parameter Value

    SSRS_datepickerpost_edit dataset parameter

  7. The expression for the Date parameter in this example is  

=”[TP DIM CALENDAR].[Date].&[” + Format(CDate(Parameters!FromParameter.Value),”yyyy-MM-dd”) + “T00:00:00]”

Note: the expression will vary depending on the format in which your cube stores the date field

Note2: If generated automatically, you can delete the dataset associated with the parameter since it is not needed anymore.

References:

Here is the post i used as guide: Original Link

July 25, 2013 / João Lobato Dias

ODI: reverse operation on XML model fails with exception from Jython

Problem:

While attempting to perform the reverse engineering of an XML data model from an XSD file, using the module “RQM SQL (JYTHON)”, the process may fail with the following error message:

    ODI-1217: Session testXMLv2 (6347003) fails with return code 7000.
    ODI-1226: Step testXMLv2 fails after 1 attempt(s).
    ODI-1229: An error occurred while performing a Reverse operation on model code TESTXMLV2.
    Caused By: org.apache.bsf.BSFException: exception from Jython:
    Traceback (most recent call last):
      File "", line 19, in 
      File "", line 68, in __init__
    AttributeError: 'NoneType' object has no attribute 'getMetaData'

Technical Explanation:

Regrettably, this is a generic, low-level error in the Jython code. This is an uncaught exception pertaining to the incorrect instantiation of auxiliary objects in the Jython runtime.

This is, in fact, a Jython error instead of an ODI one. This difficults the diagnosis of the problem as well as the search for information related to the error.

Solution:

In a nutshell, the most likely cause for this error is the inability for the ODI Agent to access the XSD file.

So far, we have successfully solved this problem using the following strategy:

  1. Check if the file path for the XSD file in the XML Data Store is correct (parameter “d” in the XML Driver URL).
  2. Check the permissions for the XSD file. Remember that the user of the ODI Agent must have read permissions for the file.
  3. Check if you are using the correct ODI Agent. If you are not using a local ODI Agent for the Model reverse engineering, it is possible that your physical agent and the XSD file are actually in a different machine! This might seem unlikely, but remember that the logical agent may link to different physical agents depending on the selected context.
  4. Assuming your ODI agent and XSD are in different machines, check the connection between the machines.
July 22, 2013 / Rodrigo Chiolas

SSRS – Disable auto update of parameter datasets

Problem

In SSRS 2008 and SSRS 2008 R2, changing the main dataset wiht Report Designer overwrites the parameter datasets, which will erase any customization you’ve made.

Solution

Right click the report and choose View Code.

Find the parameter dataset  and modify it.

Add the line

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

Like this:

<Query>

<DataSourceName>DataSource1</DataSourceName>

<CommandText> …

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> 

<rd:Hidden>false</rd:Hidden>

</Query>

Finally, don´t forget to save.

References:

Here is the original post used to solve this issue: Original Link

July 19, 2013 / Francisco Castro

SQL Server Job Error: Error processing a SSAS database inside a SSIS Package

Usually when you deploy your dtsx (SSIS package extension) package you have already tested it and it is just fine. But when you try to execute the same dtsx package on a SQL Server job, sometimes it doesn’t work. Why?!

One possible error description: Either the user, NT AUTHORITY\NETWORK SERVICE, does not have access to the ‘databasename’, or the database does not exist.

Problem: When you execute the dtsx package on development enviroment or by some other tool (dtexec or dtexecui) the user executing the package is your user (the one you are logged in). On the other hand, when executing the package on a job, the user executing the package is the agent user. This user don’t have permissions for some actions like processing the cube ou acessing the cube datasource.

Solution: Create a proxy account that executes the job under your user privileges.

The solution  is very easy and everyone can do it in less than 5 minutes, you just need to do these simple steps:

  1. Create a credential in sql server – A credential is a record containing the authentication information needed to connect to a resource outside of SQL Server. Most credentials consist of a Windows login name and password.
    • Under Security folder on your instance, right click on folder CredentialsPost2Img1
    • Select option “New Credential..” and a new window will appear
    • Give a name to your credential (example: MyCredential), a user (mydomain\myuser) and a password. The user shoud be your own user or a user with all necessary privileges.
    • Select ok, and your Credential is created.
  2. Create a proxy Account – Microsoft SQL Server Agent proxy accounts define a security context in which a job step can run.
    • Under SQL Server Agent, open “Proxies” folder, right click on SSIS Package ExecutionPost2Img2
    • Select option “New Proxy…”
    • Give a name to your proxy, select your credential (created on step 1) and select the appropriate subsystems, in my particular case i have selected SSIS and SSAS related (see image below)Post2Img3
    • Press “Ok” button and your proxy is created.
  3. Use your proxy account to run the problematic job step
    • Open your job
    • Edit your step
    • On “Run as:” option select your proxy account.

    Post2Img4

More info:

Microsoft documentation to create a proxy –  http://msdn.microsoft.com/en-us/library/ms190698%28v=sql.105%29.aspx

%d bloggers like this: