Category: Data Design

All about Azure Databases, Azure single database, Azure managed instance, Azure Cosmos database, Azure VM based databases

Edit script in SSIS is not working

This started today. The edit script stopped working in VS 2017. Here is the work around;

The issue may occur because the Script Task is not installed properly.

VS 2017 solution

Here are two options that might help;

Option # 1

Open control panel and try to repair “Microsoft Visual Studio Tools for Applications 2017”.

This options has helped me after upgrading VS SSDT 2017 recently. The funny things is that you don’t need to repair “Microsoft SQL Server Data Tools for Visual Studio 2017 (SSDT). Anyway it worked.

Option # 2

Please check whether the Microsoft.SqlServer.ScriptTask.dll file exists in the following folders (supposing it is a 64-bit platform):

  1. C:\Program Files\Microsoft SQL Server\110\DTS\Tasks
  2. C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks
  3. C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\v4.0_11.0.0.0__89845dcd8080cc91

If the Microsoft.SqlServer.ScriptTask.dll file is missing in any of the folders, copy one (from the other folders or a different working SSIS server) to the folder. Alternatively, you can also use the command line tool gacutil.exe to install the task assembly into the global assembly cache (GAC)

If above work around does not work, then do this;

Uninstall SSDT from your machine

Reboot the machine so that no cache issue

Reinstall SSDT with the option Install new SQL Server Data Tools for Visual Studio 2017 instance (SSDT), like below. Do not install in your existing Visual Studio instance again.

VS 2019 Solution

Download and Install this patch from Microsoft;

https://www.microsoft.com/en-us/download/details.aspx?id=58317

Here is the reference to this download;

https://docs.microsoft.com/en-us/answers/questions/303968/visual-studio-2019-1690-ssis-script-task-vsta-wont.html

Hope this will help.

What does it mean that Azure Cosmos DB is multi-model?

Cosmos DB is a single NoSQL data engine, an evolution of Document DB. When you create a container (“database instance”) you choose the most relevant API for your use case which optimises the way you interact with the underling data store and how the data is persisted in to that store.

So, depending on the API chosen, it projects the desired model (graph, column, key value or document) on to the underlying store.

You can only use one API against a container, multiple are not possible due to the way the data is stored and retrieved. The API dictates the storage model – graph, key value, column etc, but they all map back on to the same technology under the hood.

Multi-model, multi-API support

Azure Cosmos DB natively supports multiple data models including documents, key-value, graph, and column-family. The core content-model of Cosmos DB’s database engine is based on atom-record-sequence (ARS). Atoms consist of a small set of primitive types like string, bool, and number. Records are structs composed of these types. Sequences are arrays consisting of atoms, records, or sequences. The database engine can efficiently translate and project different data models onto the ARS-based data model. The core data model of Cosmos DB is natively accessible from dynamically typed programming languages and can be exposed as-is as JSON.

The service also supports popular database APIs for data access and querying. Cosmos DB’s database engine currently supports DocumentDB SQL, MongoDB, Azure Tables (preview), and Gremlin (preview). You can continue to build applications using popular OSS APIs and get all the benefits of a battle-tested and fully managed, globally distributed database service.

This article is referenced here;

https://stackoverflow.com/questions/44304947/what-does-it-mean-that-azure-cosmos-db-is-multi-model

Difference between SSIS, Azure Data Factory and Azure Data Bricks

For Data Engineering workloads within Microsoft landscape, there are multiple options to carry out Data Engineering tasks to extract data from myriad of data sources. Currently three options are available:

  • SQL Server Integration Services (SSIS): It is part of Microsoft SQL Server Suite and SSIS is a very well-known popular ETL tool for Data Integration along with rich built in transformations. Introduced in 2005. Mainly for on-premises. Now you can run on-premise as well. Aggregations, splits and joins.
  • Azure Data Factory (ADF): Unlike SSIS, ADF is a ELT tool along with Data Orchestration tool to build pipelines to move data across different layers. From on-Premise to Cloud and within Cloud landscape. Movement and Orchestration but not Transformations.
    • Data movement & Orchestration
    • Extract, Load & Transform
    • Transformation activities.

People familiar with SSIS can use it and existing SSIS packages can also be migrated.

Azure Data Bricks: Azure Data Bricks is latest entry into this for Data engineering and Data Science workloads, unlike SSIS and ADF which are more of Extract Transform Load (ETL), Extract Load Transform (ELT) and data Orchestration tools, Azure data bricks can handle data Engineering and data science workloads.

In a nutshell, although you can compare and contrast these tools, they actually compliment each other. For example you can call existing SSIS packages using Azure Data Factory and trigger Azure data bricks notebooks using Azure Data Factory.

How to split string in SQL Server

There could be many method but here are two simple one;

DECLARE @ValueToSplit NVARCHAR(50) = N'12345VA [987]'

--This is classical method to split string
SELECT
	SUBSTRING(@ValueToSplit, 0, CHARINDEX('[', @ValueToSplit)) AS [FIRST],
	SUBSTRING(@ValueToSplit, CHARINDEX('[', @ValueToSplit)+1, LEN(@ValueToSplit)) AS [SECOND]

--This function can be used in SQL 2016 onward
SELECT split.*
FROM
(
	SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RowNumber, value AS TextValue FROM STRING_SPLIT(@ValueToSplit,'[')
) split
WHERE 1=1
AND split.RowNumber = 1

Here are the results;