SSIS and SSRS upgradation process

  • Article

With the introduction of new MSBI versions, we get excited with new features and love to be upgraded with latest. But it’s not that easy to upgrade our ecosystem of MSBI applications. Below are few options to do the tech refresh (either version up-gradation or migrating to new environment with different version of application).

  1. In place – In this option, we keep the same physical system (hardware) and upgrade the software versions and upgrade the applications. During this time (software up-gradation in main system), the MSBI applications go offline.
  2. Side-by-side – In this option, we keep the existing system as is and get a new system with desired version and do the applications up-gradation and deploy to the new system. Once verification of new system completes, we make the new as primary system and decommission the old system.

SSIS 2014 supports both package and project deployment mode. Below are the brief features of both deployment mode.

Project deployment mode

  1. The packages are treated as part of a project and they are linked to the project and properties.
  2. This mode needs a special database SSISDB, to be used for deployment and execution. We cannot deploy packages as part of file system.
  3. The packages are deployed as a project instead of individual packages. All or none package deployment happen in this option. We cannot deploy selective packages (in SSIS 2016, even projects can deploy individual packages i.e. added feature of selectivity).
  4. The packages can share project properties, parameters, connections etc.

Package deployment mode

  1. Even packages are part of a project they are maintained individually.
  2. The packages can be deployed either in database (msdb) or file system.
  3. The advantage of file system deployment is, we can have separate maintenance strategy of packages than database.

Please refer online material for more details and comparison of above modes.

We are discussing the scenario of version up-gradation of MSBI version 2008r2 to 2014 (package deployment mode).We are using the option of side-by-side up-gradation. The prerequisite is SSDT for VS 2015 to be installed.

Step 1 – Get the final source codes of BI applications.
Step 2 – Prepare new solution/project for respective old application. We can use the old solution also, but if we need to keep a separate version of solution, this step is needed.
Step 3 – Verify the SSIS packages and SSRS reports.
Step 4 – Prepare for build and release plan.

Detailed process mentioned as below

  1. We should use proper version of BI editor which matches with our target version, otherwise, we will face run time issues while deploying the code.
  2. Since we are using the TFS with VS 2015, the team used VS 2015 to open the SSIS and SSRS packages to add them in TFS. When we open packages and reports in VS 2015 (it will prompt for auto-upgrade to new version), they by default refer the latest version of SQL 2016. But our target is 2014. This creates a problem when we run the SSIS packages or SSRS reports.
  3. We should create a new project in VS 2015 and change the project target version property to use proper version i.e. in our case SQL 2014 for SSIS packages.
  5. Now we need to add the required SSIS packages which need to be up-graded to the new project. If we have many packages, it will be difficult to add them one by one. We can edit the project file and add the package name. We can use command prompt to get the package names and then use MS Excel to add the required other values needed to complete the project file with all the package names.
  6. Now we need to open the packages and check for any error.
  7. If the target version is SQL 2016, the VSTA version would be 14. But in our case the target is SQL 2014, so we need to downgrade the target to SQL 2014 and it has VSTA version 3.
  8. We may not be in risk if the package does not have any script task or script component.
  9. We can open the package file in notepad and search for tag VSTA and verify the version.
  11. When we change the target version from 2016 to 2014, we get error for script task and script component.
  12. When we open the script task , we observe the readonly and readwrite variables are blank. The script component, is not editable. So need to rewrite them, by taking the code from original file.
  13. For SSRS reports, we need to create a new project and change the target to appropriate version.
  15. Now we can add the data source (rds) and report (rdl) files.
  16. Verify the reports.
  17. If we open the reports in VS 2015, we can find a tag in the report with 2016 version. xmlns="https://schemas.microsoft.com/sqlserver/reporting/2016/01/
  18. We need to build the project and go to the bin folder and get the rds and rdl files which would be referring to 2010.
  19. These are the ones (rds and rdl files) to be deployed in the target system.

 

Executing parent child SSIS packages

 

  1. Creating Parent Child relationship
    1. We can create a parent package and call other package as child (by referring execute package task in control file).
    2. But in this way we cannot use the child’s own external configuration (xml) file. We need to add all the child’s configured values to the parent’s and from child package, we need to use parent package variable configuration option.
    3. This way we are tightly coupling the packages and the child cannot run independently.
  2. Dynamically loading the package and its config file.
    1. We can have a script task and pass the package name and config names as variables.
    2. In this way, we can write code for loading package with configuration file. Refer the links for details. Link1, Link2, Link3.
    3. We could not get the proper log for the child package.
  3. Using a batch file
    1. Batch file inside parent package.
      1. We need to use execute process instead of execute package.
      2. The batch file will have the package name and the config file.
    2. Batch file as a wrapper (powershell script can be used for better handling).
      1. We need to create a single batch file which calls the packages in order.
      2. In this way there is no relationship maintained.
      3. We can use if else condition to make sure one package need to be run only when the other succeeds.

Hope it helps you to upgrade applications with desired MSBI versions.

Note : If the target version is SQL 2016 for us, we may not need to do all the above conversions. But still need to verify the VSTA scripts in SSIS package to verify the c# / VB codes. It might happen certain framework related code may be unsupported with new versions.