SSIS Exception - The Script Task uses version 15.0 script that is not supported in this release of Integration Services

I recently encountered SSIS exception - The Script Task uses version 15.0 script that is not supported in this release of Integration Services , when I included the script task in SSIS package to zip the generated text file and tried to run the SSIS package through command prompt.

   Source: Script Task Script Task

   Description: There was an exception while loading Script Task from XML: System.Exception: The Script Task "ST_08f0882909d74636bd9612a8d0e85790" uses version 15.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)

End Error

SSIS Package:

Here is command script to run ETL SSIS package

C:\Users\rtiwari>DTEXEC /FILE C:\Users\rtiwari\source\repos\PDFSharp_Merge\ETL_Extract.dtsx

After researching all possible cause, I found there could be two reason, why this SSIS exception is getting encountered.

1.   Deployed SSIS package ‘s target framework is not matching on deployed server :

Here is steps to fix the target framework for deployed SSIS package

Ø  Right click the SSIS project in the Project Explorer -> Click on Property

Ø  Click on Configuration Properties and Change TargetServerVersion è SQL Server 2017 

2.   Second reason really surprised me it was version of DTEXEC.exe. If the version of DTEXEC.exe command is not latest version, then also it throw exception.

Might be there are different version of SQL server Data Tool installed on your machine, Please look the below folders for different version of DTEXEC.exe

               

·         Open a Command Prompt window, change to the directory that contains the latest version of utility command, and then run the utility from that location

C:\Program Files(x86)\Microsoft SQL Server\140\DTS\Binn> DTEXEC /FILE C:\Users\rtiwari\source\repos\PDFSharp_Merge\ETL_Extract.dtsx

·         At the command prompt, run the utility DTEXEC.exe by entering the full path (:\Program Files\Microsoft SQL Server\140\DTS\Binn).

C:\Program Files(x86)\Microsoft SQL Server\140\DTS\Binn\DTEXEC.exe /FILE C:\Users\rtiwari\source\repos\PDFSharp_Merge\ETL_Extract.dtsx

SSIS: Different way to execute SSIS Package

In this blog, we will learn about the different ways in which we can execute or run the SSIS package.

1. Execute the SSIS Package from the command prompt by using DTExec.exe command 
2. Execute the SSIS package in SQL Server Business Intelligence Development studio – BIDS

1. DTExec.exe command:

SQL Server includes the command line tool DTEXEC.EXE which can be used to execute an SSIS package.  DTEXEC can be run from a Command Prompt or from a batch (.BAT) file

C:\Users\rtiwari>DTEXEC /FILE {Location of SSIS package}

If you have configuration file for this ssis package

C:\Users\rtiwari>DTEXEC /FILE  {Location of SSIS Package } /CONFIGFILE {Location of configuration file}

Here is an example to run ETL SSIS package with configuration file

C:\Users\rtiwari>DTEXEC /FILE  C:\\Sample\\ETL_Extract.dtsx  /CONFIGFILE C:\\Sample\\ETLConnectionStrings.dtsConfig 

2. Execute the SSIS package in SQL Server Business Intelligence Development studio:

In visual studio to execute the SSIS package, right click the package within Solution Explorer and select Execute Package option from the drop down menu as shown in the below.

SSIS - Set variable in SQL task with Single Row and Full result set

In my recent blog SSIS - How To Pass input parameter in Execute SQL Task explained how to pass input parameter in SQL task and this blog we will discuss about to set variable value in SQL task so that in later flow, we can use this variable value and we will also discuss how to map the list of returns value to variables

Here is a SQL Task – Get Project Code based on input project ID parameter.

As we see, this SQL task takes ProjectID as input parameter and it always returns single project record.

If result set = “Single Row”, we can maps the result set value to variables like ‘ProjectCode and ProjectName’

These variable we can use in further data flow (load project history data based on project code)

Here is OLD DB Source , we can see its pulling history records based project code

And ProjectCode value is being set by input parameter variable

SSIS - How To Pass input parameter in Execute SQL Task

This blog will explain how to create a variable and how to pass it as input parameter in Execute SQL Task

Create Variable in SSIS Package.

We have a SQL task and this task will update project schedule date based on input date

 

SQL Query:

Update Project SET ScheduleDate = ?

or

sp_updateScheduleDate ?

In above SQL query, we need to pass input parameter for schedule date, for this first we need to create a variable

1.    Open the Execute SQL task Editor -

Right Click on SQL Task à Select Edit à Open the Execute SQL task Editor

2.    Select Parameter Mappings

3.    Click on Add button and Add Variable popup window will be opened and enter below fields

·         Container   -  Specify Scope of variable, by default SSIS package name – eg Update

·         Name - Enter variable Name eg – ScheduleDate.

·         Namespace – Enter Namespace

·         Value Type – Select Data Type for variable (eg DateTime) 

·         Value (by default value) – Today Date – 12/12/2019

After entered above fields and click on OK button

Maps variable as input parameter in SQL Task:

Maps the created variable ScheduleDate (User::ScheduleDate) as input parameter for SQL task and in SQL script, we have only one input parameter symbol ‘?’  So give parameter name as 0, and define parameter size 10 for Date Field

 

If you have more than one input parameter, then give parameter name as 0, 1, and 2.

SQL Query:

Update Project SET ScheduleDate = ?, ProjectName = ?

SSIS: Dynamic File Name for Flat File Destination

This blog will demonstrates how to give the dynamic file name for flat or csv  file in SSIS package  like DataFeed_{yyyyMMdd} .txt eg. DataFeed_20181212.txt

Data Flow

• Configure the Flat File Destination with dummy file name.

Flat File Connection Manager

• Go to Connection Manager and right click on Flat File Connection Manager

1. Click on Property
2. Set DelayValidation = True, by default it is False

Flat File Connection Manager Property

• Click on Expression Property and Set Expression Value for ConnectionString 

Expression Builder

Expression:

"C:\\temp\\dataFeed_"+(DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() )) +(DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() )) +(DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))+".txt"

·   (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() )) – returns Month

·   (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() )) – returns day

·   (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) – returns Year

  

The above expression generates file name dataFeed_MMDDYYYY.txt in C:\temp location.

Thanks for visiting and please leave your comment if it helps you

Visual Studio SSIS Project Incompatible

Unsupported

This version of Visual Studio is unable to open the following projects. The project types may not be installed or this version of Visual Studio may not support them.

For more information on enabling these project types or otherwise migrating your assets, please see the details in the "Migration Report" displayed after clicking OK.

I recently faced this issue, when I tried to open existing SSIS project and it was not opening in Visual Studio 2017 and throwing ‘Unsupported Message’.

When I migrated current project from visual 2015 to visual 2017 and I found that SSIS /SSRS component is not part of Visual studio 2017 installation package so I installed separate package for SSIS/SSRS and after sometimes visual studio (in background ) runs some performance improvement update checks and it asks to disabled the 3^rd party component and after that SSIS package was not loading in visual studio solution  and throwing above unsupported error message.

 I followed below steps to resolve this problem. 

Click on Tools à Extensions and Updates … Option 

Extensions and Updates

Extensions and Updates Screen:

  

Click on “Enable” button to include package in project, after that close visual studio solution and reopen the SSIS project.

Thanks for visiting and please leave your comments, if it helps you

SSIS - Error Code and Error Column

SSIS automatically creates two columns on the Error output from data flow components

1.       ErrorCoulmn

2.       ErrorCode 

Error Code – it contains the error number that caused the row to be rejected. You can find the list of error code at http://msdn.microsoft.com/en-us/library/ms345164%28v=sql.100%29.aspx

Error Column – it contain the column ID of the flawed column and that Column ID is unique to a dataflow within package.

You can easily find actual associated Column Name for Column ID.

·         Open the Advance Editor of error associated component

Advance Editor

·         Click on “Input and Output Properties” tab

Input and Output Properties

·         View all Input Columns, you can find the matching column ID.

Thanks for visiting!!

SSIS Derived Column Cannot Convert Between Unicode and Non-Unicode String Data Types

In this blog, we will discuss about Data type Conversion Issue in SSIS Package for derived Column. recently while designing of SSIS package for migrating  data from legacy system to new system I did a lot of data transformation regarding data type conversion, and concatenate of multiple columns.

I used derived column component for data type conversion or concatenate of two columns value and while concatenating of two column I got data type conversion error.  

SSIS : Column ‘XXX’ Cannot convert between Unicode and non-unicode string data types

I  performed below steps to resolve this data type conversion issue without explicitly data type conversion :

1. Open the ‘Show Advanced Editor’ of derived column component
2. Click on “Input and Output Properties”  à  “Derived Column Output” à Output Column  à
3. Set Data Type property of derived Column à DT_STR

After above changes, SSIS package successfully loaded data. Thanks for visiting !!