
In the Last Episodes…
Hi there! Welcome to the 6th instalment of a series of tutorials on SQL and SQL Server Studio. There’s a simple goal: To make you familiar and comfortable with the tool, and the language. "Why does it even matter?" I see you asking. Well, it turns out, curiosity, and side projects are often key in getting picked on new projects, or even in getting hired for a new job. The mere fact you’ve already used an important tool such as SQL Server Studio and wrote some SQL queries, can and will give you a clear head start.
If you missed Episode 1, on how to set up our environment and local server, head towards this article:
Learn SQL Server Management Studio – Part 1 Step by Step Setup
The 2nd Episode will teach you how to create your own databases, tables, and an important commentary on naming conventions:
Learn SQL Server Management Studio – Part 2 Databases, Tables & Naming Conventions
In the 3rd Episode, we introduced the CRUD operations and the Primary and Foreign Keys:
Learn SQL Server Management Studio – Part 3 CRUD Operations, Primary & Foreign Keys
In Episode 4 we discussed schemas & the main normalization steps:
Learn SQL Server Management Studio – Part 4 Schemas & Normalization
In Episode 5 we covered Stored Procedures & Scheduling, this is a real beast. I can’t stress enough how this will facilitate and automate your daily (data-)life.
Learn SQL Server Management Studio – Part 5 Stored Procedures & Scheduling
And don’t forget to come back 😉 .
What to Expect?
Today we’ll look at SSIS Packages. By the end of this tutorial, you’ll create your own packages, and by leveraging schedules, you’ll be able to automate tasks such as data import & exports. Let’s jump right in!
A Quick Definition
Sql Server Integration Services (SSIS) is a component of SQL Server Studio that can perform data migration tasks. It has a data warehousing tool for ETL steps (data extraction, transformation, and loading). An SSIS package in Microsoft’s words is:
"an organized collection of connections, control flow elements, data flow elements, event handlers, variables, parameters, and configurations, that you assemble using either the graphical design tools that SQL Server Integration Services provides, or build programmatically."
The graphical window makes it easy to perform those tasks, all one needs to do is to provide the required information or select options from dropdown lists. The feature we’ll focus on is the Import/Export.
Getting to Work
I will assume you already have your SQL Studio environment setup and are connected. The next step would be:
- Right-click on a database, and select Tasks, then Import Data

- You’re greeted by a typical Microsoft Wizard window, let’s move on

- We are asked to choose the source from which to copy the data. Among the available options, we find Microsoft Excel, and SQL Server Native Client 11.0

Import/Export between SQL databases
To import between SQL databases or tables we do as follows:
- Our source will be SQL Server Native Client 11.0

- We then select where to export our data, and decide to ‘Copy data from one or more tables or views

-
- Next, we can decide to copy data or write a query to specify what to transfer. We’ll pick the first option.

- The Wizard then offers us the choice to pick which tables to copy and to define the names or select an existing table. For the sake of this example, the table we copy our ‘Employees’ to will be named ‘Employees_copy’.

- Under Edit Mappings, we have a few options. In our case, as we’re creating a new table, Employees_copy does not exist yet, some options are greyed out. If we were to copy to an existing table, we could decide to ‘Delete rows in destination table’ or ‘Append rows to the destination table’.

- We can then decide to run the package immediately, and to save the package on SQL Server or locally (File system). We’ll save it locally.
- We can define the name, and destination path

- On the next page, SQL Studio offers a summary

- Then the process runs and provides a step by step feedback.

- One can open a .dtsx file using Microsoft Visual Studio, or NotePad++. The script details the source, the destination etc. If a step of the process were to change, instead of recreating a package, it could make sense to adjust the script.
Let’s cover Import with Microsoft Excel.
- Restart the process, Right-click on a database then Task>Import data
- Select Microsoft Excel, the Wizard windows is updated, and we’re asked to provide the path to the excel from which we’ll import data, as well as the Excel version. From experience, the Excel version already caused some unexpected results. If your import does not work properly, try changing the extension to .xls or .xlsx respectively, this sometimes does the trick. Click Next.

- We then have the choice to select where to copy this data. We select SQL Server Native Client 11.0. But do not click on the arrow by ‘Server name’. This Wizard has the peculiarity to freeze when one clicks there. It starts searching for instances and is slow in offering anything. Let’s see a quick trick instead.
- Within the main SQL Studio window, click on Connect > Database Engine

Then copy-paste the name of your server instance.

Back to our Wizard window, let’s paste this into the ‘Server name’ field. That’s it, it all took 2–3 seconds, while the dropdown list would freeze for… I have never found out, it is just too slow.
- The next step is to pick our database. For this, we can safely click on the dropdown, the available options will be coming from our server name above.

- The following steps will be the same as for DB to DB copies. SQL will treat the Excel source file as one database, where every sheet is a table. One can either define the name of the destination table – therefore creating a table, or choose an existing table.

Final Words and What’s Coming Next
That’s it for now! I hope you now feel more confident to operate around SQL Server, and:
- Create your own SSIS packages,
- Export or Import data from & to your database, using an Excel or another database,
What’s coming next? In the upcoming article, we will combine the power of SSIS Packages and Job Schedules, to automate some ETL tasks.
Thanks for reading, let me know what you think or if there’s a topic I should cover. Until next time!
Happy Coding!
Thanks for reading! Enjoyed this story? Join Medium for complete access to all my stories.
Continue The Journey With Episode 7!
Learn how to connect SQL Server to Power BI, a powerful visualization tool, a first intro to this business intelligence tool with the creation of your first visuals.
Learn SQL Server Management Studio – Part 7 Connect to PowerBI & First Visuals