Importing, exporting

Importing data from JSON files and Power BI Rest APIs into Power BI

August 27, 2020 by Ben Richardson

In this article, you will see how to import data from JSON files and Power BI Rest API into the Power BI environment.

Export data from MySQL tables to Azure SQL database

August 12, 2020 by Nisarg Upadhyay

In this article, I am going to explain how we can export the data of the on-premise MySQL database to Azure SQL database. This task can be performed in two phases.

Importing and Exporting data using SSIS Hadoop components

May 11, 2020 by Hadi Fadlallah

In the previously published article, we talked briefly about Hadoop, and we gave an overview of the SSIS Hadoop components added in the SQL Server 2016 release, and we focused on the Hadoop connection manager and the Hadoop file system task.

How to export data from Excel into multiple tables in Azure SQL Database using SSIS

March 3, 2020 by Nisarg Upadhyay

In this article, I am going to explain how we can split the data within the excel file and upload it to the tables created on the Azure SQL database.

Methods to script SQL Server database objects

February 14, 2020 by Rajendra Gupta

In this article, we will explore various ways for scripting SQL Server database objects.

Dynamic column mapping in SSIS: SqlBulkCopy class vs Data Flow

February 14, 2020 by Sifiso W. Ndlovu

The Data Flow Task is an essential component in SQL Server Integration Services (SSIS) as it provides SSIS ETL developers with an ability to conveniently extract data from various data sources; perform basic, fuzzy to advance data transformations; and migrate data into all kinds of data repository systems. Yet, with all its popularity and convenience, there are instances whereby the Data Flow Task is simply not good enough and recently, I got to experience such inefficiencies. To demonstrate some of the limitations of SSIS’s Data Flow Task, I have put together a random list of Premier League’s leading goal scorers for the 2019-2020 season.

Importing a BACPAC file for a SQL database using SSMS

February 6, 2020 by Rajendra Gupta

This article gives you an overview of BACPAC package and its usage in SQL Database data refresh (data import and export) using SQL Server management studio.

Import JSON data into SQL Server

January 17, 2020 by Rajendra Gupta

This article explores the process of JSON data import in the SQL Server table using T-SQL and SSIS.

Importing Data into SQL Server from MySQL using SSMS

November 29, 2019 by Jignesh Raiyani

This article shows the best way to import a copy of your MySQL database table to SQL Server using the SQL Server import data feature. We’ll demonstrate how to perform data import and export using a query with the assistance of SQL Server Management Studio.

Query Amazon Athena external tables using SQL Server

November 15, 2019 by Rajendra Gupta

This article covers the following topics:

Overview of the Amazon Athena
Query CSV file stored in Amazon S3 bucket using SQL query
Create SQL Server linked server for accessing external tables

Data Import from Amazon S3 SSIS bucket using an integration service (SSIS) package

October 28, 2019 by Rajendra Gupta

This article explores data import in SQL Server from a CSV file stored in the Amazon S3 SSIS bucket.

Working with XML Data in SQL Server

October 11, 2019 by Ben Richardson

XML (eXtensible Markup Language) is one of the most common formats used to share information between different platforms. Owing to its simplicity and readability, it has become the de-facto standard for data sharing. In addition, XML is easily extendable.

SSIS Multicast Transformation overview

August 22, 2019 by Rajendra Gupta

This article explores the SSIS Multicast Transformation for creating different logical copies of source data.

SSIS Conditional Split Transformation overview

August 16, 2019 by Rajendra Gupta

This article explores the SSIS Conditional Split Transform task to split data into multiple destinations based on the specified conditions.

Importing Data into SQL Server from Compressed Files

March 5, 2019 by Rajendra Gupta

Introduction

I have seen many organizations receive data from various sources and import into SQL Server. You might receive data in various formats and want to import into SQL Server. We can prepare a ETL (Extract-Transform-Load) process to import data into the SQL Server. In doing so, might receive data in a compressed file, which helps to send data over the network using a ZIP file format because it reduces the file size significantly. If we are receiving a ZIP file to import into SQL Server, we need to unzip it and then only we can import data. We might need to create a ZIP file as well from the existing files.

Using Python SQL scripts for Importing Data from Compressed files

March 4, 2019 by Rajendra Gupta

Using Python SQL scripts is a powerful technical combination to help developers and database administrators to do data analytics activities. Python provides many useful modules to perform data computation and processing of data efficiently. We can run Python scripts starting from SQL Server 2017. We can create the ETL solutions to extract data from various sources and insert into SQL Server.

Importing data from a PDF file in Power BI Desktop

March 1, 2019 by Rajendra Gupta

Power BI Desktop is a useful reporting and analytical tool to represent data in various formats. These presentations help us to quickly understand information and circulate it to stakeholders in a visual fashion.

SSIS Script component vs derived column

March 1, 2019 by Daniel Calbimonte

Introduction

In this article, we will show how to convert dates from dd/mm/yyyy to mm/dd/yyyy using the Script component and also derived columns in SSIS. We will also explain when to use a derived column (DC) and when to use the Script Component (SC).

Export SQL Server FILESTREAM Objects with PowerShell and SSIS

February 14, 2019 by Rajendra Gupta

In this series of articles on SQL Server FILESTREAM (see TOC at bottom), we explored various ways to store unstructured data in the file system with the metadata in SQL Server tables. If we have a large number of objects in the file system, it is advisable to use the fast disk for storage purpose. It is faster and provides better IO in comparison with the traditional file system.

Importing SQL Server FILESTREAM data with SSIS packages

February 1, 2019 by Rajendra Gupta

Initial configuration

We have been exploring the SQL Server FILESTREAM feature in this ongoing series of articles. In this previous article, Managing data with SQL Server FILESTREAM tables, we wrote about inserting FILESTREAM data into a FILESTREAM table and performing DML activities on it. Suppose we have created the FILESTREAM database in our instance and now we want to insert a large number of files into a FILESTREAM container. It is easy to write out the insert queries for a small number of files, but if the numbers of files were in huge quantity, it would be difficult to write out the code and insert data into it. It is difficult to manage such kind of requests regularly in the environment.

Importing and Working with CSV Files in SQL Server

January 28, 2019 by Ben Richardson

Introduction

CSV (comma separated values) is one of the most popular formats for datasets used in machine learning and data science. MS Excel can be used for basic manipulation of data in CSV format. We often need to execute complex SQL queries on CSV files, which is not possible with MS Excel. See this article for what is possible with Power BI.

However, before we can execute complex SQL queries on CSV files, we need to convert CSV files to data tables.

Using SQL Bulk Insert with Strict Business Rules

January 23, 2019 by Timothy Smith

This article will cover SQL bulk insert operations deterministic outcomes and responses covering not allowing any bad data to allowing all data to be inserted, regardless of errors.

Disabling triggers in SQL Server for a specific session

January 18, 2019 by Ranga Babu

This article will focus on the various ways to disable triggers in SQL Server so they won’t interfere with certain operations like bulk inserts.

SQL Bulk Insert Concurrency and Performance Considerations

January 18, 2019 by Timothy Smith

One of the challenges we face when using SQL bulk insert from files flat can be concurrency and performance challenges, especially if the load involves a multi-step data flow, where we can’t execute a latter step until we finish with an early step. We also see these optimization challenges with constraints as well, as fewer steps to complete a data flow results in saved time, but possibly less accurate data.

Considering Security with SQL Bulk Insert

January 17, 2019 by Timothy Smith

In this article, we’ll discuss security implications of using SQL Bulk Insert and how to mitigate those risks.