Issues When Deploying SSIS Projects From Visual Studio 2015 To SQL Server 2014

I stumbled across an error message that didn’t seem like was getting much exposure online so I wanted to write it up here just case anyone else had this experience. The situation that created this issue was a Visual Studio 2015 development environment creating packages that would get deployed to a SQL Server 2014 instance. When running the package through a SQL Agent Job I would get the following error in the reporting log.

“SCR Build ErrorMessage text 1:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task “”ST_c9f9ef4bd6a84f25a6cca6ce603450c5″” uses version 14.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services.
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)”

Initial online research suggested that the scripts that were in use for these packages were corrupt and or outdated and that they needed to be reapplied if these objects originated from earlier Visual Studio versions. That was not the case in my scenario as I just need some updates and a quick configuration.

When the early versions of SSDT for 2015 were released they were only supporting SQL Server 2016 deployments. Later releases would add the functionality to deploy to a SQL Server 2014 instance. These later releases of SSDT are extremely helpful as the recourse would be to develop your packages under Visual Studio 2013. 

After doing some searching online it was found that as of this writing the Microsoft recommendation is to apply the latest Visual Studio Updates. Instruction can be found here.

Applying the latest Visual Studio Updates does not automatically correct the 2014/2016 compatibility issue. There is some configuration to apply. Right click on your project and select Properties. Under Configuration Properties select General. Set your desired TargetServerVersion and that will allow you to apply that property to all of your packages. Now redeploy and you should have successful package executions on SQL Server 2014 instances.

vs2015projectproperties

 

Correct CRAN mirror error in RStudio when installed after SQL Server 2016’s Revolution Analytics RRO for RRE 7.5.0

I finally got around to installing SQL Server 2016 CTP 3.0 and was excited to see how R could integrate with the product. If you haven’t done so yet and would like to explore the new R features yourself, you can find very detailed instructions in the following links.

  1. Introducing Microsoft R Server (SQL Server 2016 R Services): //henkvandervalk.com/introducing-microsoft-sql-server-2016-r-services
  2. Installing SQL Server R Services: //msdn.microsoft.com/en-us/library/mt604883.aspx
  3. Post-Installation Server Configuration (SQL Server R Services): //msdn.microsoft.com/en-us/library/mt590536.aspx

One thing I did want to do outside of these instructions is to install RStudio Desktop as the IDE just for comfort-ability, look, and feel. However once I opened RStudio, I noticed an error indicating the CRAN mirror was set to an insecure URL. The URL in question was from the Revolution Analytics installation.RStudioCranMirrorError

WARNING: Your CRAN mirror is set to “//cran.revolutionanalytics.com/” which has an insecure (non-HTTPS) URL. The repository was likely specified in .Rprofile or Rprofile.site so if you wish to change it you may need to edit one of those files. You should either switch to a repository that supports HTTPS or change your RStudio options to not require HTTPS downloads.

To learn more and/or disable this warning message see the “Use secure download method for HTTP” option in Tools -> Global Options -> Packages.

 

A quick update to the RProfile.site file will correct this.

1: Locate the RProfile.site file located in “C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\etc”.

2: Locate the block of code for “# set a CRAN mirror”.

 # set a CRAN mirror
 r <- getOption("repos")
 if (!identical(system.file(package="RevoScaleR"),"")) {
 if (.Platform$OS.type == "windows"){
 r["CRAN"] <- RevoUtils::getRevoRepos(CRANmirror=TRUE)
 } else {
 r <- c(REVO=RevoUtils::getRevoRepos())
 }
 } else {
 r["CRAN"] <- RevoUtils::getRevoRepos(MRANmirror=TRUE)
 }
 options(repos=r)	

3: Update with the following additional and commented lines.

  
 # set a CRAN mirror
 r <- getOption("repos")
 r["CRAN"] <- "//cran.revolutionanalytics.com/"
 # if (!identical(system.file(package="RevoScaleR"),"")) {
 # if (.Platform$OS.type == "windows"){
 # r["CRAN"] <- RevoUtils::getRevoRepos(CRANmirror=TRUE)
 # } else {
 # r <- c(REVO=RevoUtils::getRevoRepos())
 # }
 #} else {
 # r["CRAN"] <- RevoUtils::getRevoRepos(MRANmirror=TRUE)
 #}
 options(repos=r)

This will correct the RStudio error.

Notable SQL Server / Business Intelligence Books Released In 2014

Last year witnessed a lot of great releases in the Microsoft data space regarding SQL Server, Business Intelligence, and even Big Data. The following is a list of highly regarded titles that would make a good addition to your learning library.

 

 

Microsoft Big Data Solutions

March 10, 2014

 

 

 

 

Professional Microsoft SQL Server 2014 Integration Services

April 21, 2014

 

 

 

 

Microsoft SQL Server 2014 Business Intelligence Development Beginners Guide

May 26, 2014

 

 

 

High Impact Data Visualization with Power View,

Power Map, and Power BI

June 10, 2014

 

 

 

Power Query for Power BI and Excel
June 24, 2014

 

 

 

 

Beginning Power BI with Excel 2013: Self-Service Business Intelligence Using Power Pivot, Power View, Power Query, and Power Map

September 24, 2014

 

 

 

Microsoft SQL Server 2014 Query Tuning & Optimization

October 7, 2014

 

 

 

 

 SQL Server Integration Services Design Patterns 2nd Edition

December 18, 2014

Five TED Talks with Terrific Data Visualizations

TED

 

Data visualization can come in many forms. Whether you are presenting your data via report or an interactive chart, it is important to tell an accurate story with the data. Here are five great TED talks that take data and tell remarkable stories.

 

1. David McCandless – The beauty of data visualization

 

2. Hans Rosling – New insights on poverty

 

3. Aaron Koblin: Visualizing ourselves … with crowd-sourced data

 

4. Jer Thorpe – The weight of data

 

5. Jamie Heywood – The big idea my brother inspire