Posts

Import Data into SQL from Excel

, , , ,

In our last lesson, we showed you how to use SSIS to export data to Excel. In fact, all of our previous SSIS lessons have focused on exporting data from SQL into some other format. In this lesson, however, we will show you how to import data from Excel into SQL.

This is very helpful as you start to develop your business intelligence program in your bank or credit union. Think of how much data exists at your financial institution sitting in the form of Excel spreadsheets. By importing this data into SQL, it can be cross-referenced and/or integrated with existing SQL data. This is a key first step in any business intelligence program, and in this lesson we will help you get started!

Setting up our Data Source

The Excel spreadsheet that I will be importing into SQL is based on data compiled by the NCUA. The spreadsheet contains a list of all credit unions and their associated member totals and total assets (as 6/30/2014). The image below gives you a quick snapshot of what the data looks like:

Spreadsheet Header

With our source Excel spreadsheet identified and described, open up your SSIS solution named “Credit Union SSIS Solution” if you have been following along with previous examples. Otherwise, create a new SSIS solution and name it “Import to SQL.dtsx”. Just like we have in past examples, drag a “Data Flow Task” from the “SSIS Toolbox” onto the “Control Flow” designer. Right click on the task, click “Rename”, and then rename the task “Import NCUA File into SQL”.

Renamed Data Flow Task

Now, double-click on the newly renamed task to be brought to the “Data Flow” editor. From the “SSIS Toolbox”, drag an “Excel Source” item onto the data flow designer. Rename this item “NCUA Excel File”.

NCUA Excel File Task Item

Double-click on the renamed item; the “Excel Source Editor” window will appear on your screen. Click the “New” button to the right of the “Excel connection manager” dropdown. Click “Browse”, then locate the file you will be importing (in my case, the NCUA file), press “Open” and then press “OK” when you are brought back to the “Excel Connection Manager” window.

In the “Name of the Excel sheet” dropdown, choose the specific spreadsheet within the Excel workbook that you wish to choose as your data source.

Excel Source Editor

Press “OK” once this has been completed.

Defining the Destination to Import Data into SQL

With the Excel source defined, drag an “OLE DB Destination” item from the “SSIS Toolbox” onto the data flow designer. Rename this item “SQL Destination”. Click the “NCUA Excel File” item so that it is selected then drag the blue arrow from the source item to the “SQL Destination” item.

Connected Items

Next, double-click on the “SQL Destination” item. Here, we defined where the NCUA data will reside in SQL. Click the “New” button to the right of the “OLE DB connection manager” dropdown. Select the connection from the left panel on the “Configure OLE DB Connection Manager” window that appears or define a new one by clicking the “New” button at the bottom of the window. Press “OK”.

For this example, we will need to create a new table in the database for this Excel data to reside. To do this, click the “New” button to the right of the “Name of the table or the view” dropdown. The “Create Table” window will appear on screen containing the SQL code that will be used to create a new table. I want to make one subtle change and alter the name of this table. After “CREATE TABLE” replace “[SQL Destination]” with “NCUA_Data”.

Create Table Window

Press “OK” once you have done this. If you open SQL Server Management Studio, you will see that this new table has been created in the database. This is important; the table is created once you press “OK” from the “Create Table” window – NOT when you execute the package for the first time.

To finish setting up the destination, press the “Mappings” option from the left side of the “OLE DB Destination Editor” window. Ensure that the column mappings are correct, and then press “OK”.

Execute the package (by pressing F5 or the green play button on the toolbar) to see if everything works. Assuming you successfully execute the package in SSDT, open up SQL Server Management Studio. We want to view the contents of the table we just sent the Excel data to in order to validate the process properly imported all data.

SQL Validation

It’s that easy! The process required to import data into SQL from Excel is nearly identical to the previous export examples we have completed throughout the series. We simply define the source, define the destination, and then the mappings between the two. Learning this relatively simple task in SSIS can open up tons of new opportunities for you to be able to use your data more effectively. Eliminate silos of Excel data by bringing that data into SQL. Doing this will allow you to integrate and cross-reference it with your other SQL data sources ultimately bringing you closer to business intelligence success!

Download the Excel File from this lesson by click this link: NCUA_Data.xlsx

/1 Comment/by

Using SSIS to Export Data to Excel

, ,

In our previous post, we learned how to append the date to flat file exports. This represents an intermediate level skill in SSIS but can be incredibly helpful in production business environments. Unfortunately, everything we have looked at in SSIS so far has related to flat files. More often than not our business users would prefer to receive data extracts in Excel files. In this lesson, we explore how to use SSIS to export data to Excel.

Defining the Source Data

If you haven’t done so already, open up the SSIS solution in SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS) named “Credit Union SSIS Solution” or create a new SSIS solution.

Right clicking on the folder named “SSIS Packages” in the “Solution Explorer”, create a new package and rename it “Export to Excel.dtsx”.

As we have done in previous SSIS examples, drag a “Data Flow Task” from the “SSIS Toolbox” onto the blank Control Flow editor of the SSIS package.

Data Flow Image

To proceed to the “Data Flow” editor, either select the “Data Flow” tab to the right of the “Control Flow” tab just above the main editor panel or double-click on the “Data Flow Task” item in the control flow designer.

Like with any “Data Flow Task”, we need to define the source of our data. Since our data source will be the result of a SQL query, drag an “OLE DB Source” item from the “SSIS Toolbox” to the designer.

OLE DB

Next, we need to define the data source. To do this, double-click on the “OLE DB Source” item. A blank “OLE DB Source Editor” window will appear on screen.

Blank OLE DB Source Editor

Set the “OLE DB connection manager” dropdown to the proper database in which your data resides. We walked through this in our post that talked about how to use SSIS to export data to flat files. Refer to that post if you need some assistance walking through that process.




Once you have defined the “OLE DB connection manager” dropdown, change the “Data access mode” dropdown to “SQL Command”. This allows us to return the results of a specific query as opposed to an entire table. I am going to complete a query that returns the names of all current employees and to what branch they are assigned. To this, I will use the query seen in the image below:

OLE DB Source 2

Once you have the query defined, press the “Columns” option in the window’s left panel. Verify that the columns that appear on the screen are the columns that you wish to export to Excel.

OLE DB Columns

Press “OK” once you have done this. We now have our data source defined!

Defining the Excel Destination

Exporting to an Excel destination has some differences compared to exporting to flat files. SSIS essentially treats the Excel destination like a SQL table in some ways which we will see upon completion of this walkthrough.

To define our Excel destination, drag the “Excel Destination” item from the “SSIS Toolbox” out onto the designer.

Dragged Excel Destination

Click on the “OLE DB Source” item and drag the blue arrow to the “Excel Destination” item and then release. If done correctly, the two items should be connected by the blue arrow:

Connected Items

Double-click on the “Excel Destination” item. The “Excel Destination Editor” window will appear. This is nearly the same exact window that appears when you define an OLE DB source. The only difference here is that we are defining the destination instead of the source of the data.

Excel Destination Editor Blank

Click the “New” button to the right of the “Excel connection manager” dropdown. When the “Excel Connection Manager” window appears, press “Browse”. A file explorer window will appear.

Create Excel Connection

Navigate to wherever you would like to create your Excel destination. I will be leaving mine on my Desktop. In the “File name:” text box, type “Active Employees” and then press “Open”. This creates the Excel destination. When you press “Open” you will be returned to the “Excel Connection Manager” window. Leave the checkbox next to “First row has column names” checked and then press “OK”.

Learn SQL Course

Looking to learn SQL? Click here!

You will be brought back to the “Excel Destination Editor” window. We have, at this point, defined what Excel file our data will be sent to. However, we now need to define the actual sheet that the data will reside in. Think of the Excel file as the database and the spreadsheet as the actual table.




To define this, click the “New” button to the right of the “Name of the Excel sheet” dropdown. You will notice that a script is automatically generated in the “Create Table” window that appears. It recognizes the columns that are contained in the OLE DB Source and properly defines the data types for each column. Press “OK”.

When you are returned to the “Excel Destination Editor” window, select “Excel_Destination” from the “Name of the Excel sheet” dropdown. Once you have done this, click the “Mappings” option from the left panel of the window.

Mappings

Press “OK” once this has been completed.

Export Data to Excel

Now that all of the steps have been completed, we can test to see how our package works. Press the green play button or F5 to start debug mode and execute the package. If you get an error that mentions something about running the package in 32-bit mode, head to the main menu, hover over “Project” and choose “Credit Union SSIS Solution Properties” – the last item in the menu. When there, click the “Debugging” option under “Configuration Properties”. Set the “Run64BitRuntime” property to “False”. Apply and press “OK”.

If the package successfully executed, your screen should like the image below:

Successful Execution

Navigate to the Excel file that you sent the data to and open it up. Notice the number of rows in your Excel spreadsheet (in my case, 755 including the header). Close the Excel file and then go back to SSIS and execute the package again. Once executed, re-open the Excel file the data was exported to. Notice the number of rows now.

SSIS will not overwrite the rows and will simply add another 754 data rows (in my case) to the spreadsheet. Since SSIS treats the Excel destination like a SQL table in many regards, the data will be appended instead of being overwritten. This is a subtle yet critical difference between working with Excel exports and flat file exports.

Despite some subtle differences, the majority of the steps to use SSIS to export data to Excel are identical to exporting data to flat files. As you continue to use SSIS, you will become more familiar with these processes and be able to quickly make simple packages with ease!

/by

Append the Date to Flat File Exports with SSIS

, , , ,

In one of our previous posts, we provided instructions on how to use SSIS to export data to flat files. Often times, our business users would like to have a running history of exported data. For example, if we had an export that output all orders for the prior day our users wouldn’t want to see a file named “Orders Placed Yesterday”. Data consumers would prefer to see a file named “Orders Placed 6-30-2015”. To append the date to flat file exports in SSIS is a relatively common and simple task.

Setting Up the File Name Variable

To start, open up the SSIS solution named “Credit Union SSIS Solution”. Right click on the “Export to Flat File” package in the “Solution Explorer” and select “Copy”. Then right-click on the “SSIS Packages” folder and click “Paste” to create a copy of the package. Rename this package “Export with Date Append”.

Make sure that you have a “Variables” panel on the left side of your screen. If you cannot find this panel, click on the “View” option from the main menu, hover over “Other Windows” and choose “Variables”.

Our plan is to use variables to store the fully qualified file name with the appropriate date value. Then, we will use this variable value as the connection string for our flat file connection.

To start, click the “Add Variable” button in the top left corner of the “Variables” panel.

New Variable Button

A new row will appear in this “Variables” panel. Name the variable “FilePath” and then set the “Data type” column value to “String”.

First Variable

With the variable’s row highlighted, navigate to the “Properties” panel which should be on the right side of your screen. Find the property named “EvaluateAsExpression”; set this value to “True”. Directly beneath this property is another property named “Expression”. If you click in the text box to the right of the “Expression” property, an ellipsis button will appear to the right; click this button.

Properties

The “Expression Builder” window will appear on your screen. Here is where we will define how this variable will be evaluated. I won’t dive too deep into how to create different SSIS expressions, but if you click here you will be redirected to SSIS expression documentation from the Microsoft Developer Network that provides a wealth of knowledge on the subject.




In the “Expression” text box, type in the following expression (including all quotation marks as they appear and of course modifying for your specific location and folder names):

“\\\\localhost\\c$\\Users\\Brewster\\Desktop\\AccountTypes as of ” + (DT_WSTR, 2)MONTH(GETDATE()) + “-” + (DT_WSTR, 2)DAY(GETDATE()) + “-“+ (DT_WSTR, 4)YEAR(GETDATE()) + “.csv”

Once you have typed or copied the above expression, click the “Evaluate Expression” button below the text box. The “Evaluated value” read-only text box will display how the expression evaluates. In this case, it contains the fully qualified file path containing the current date appended to the file name and the appropriate “.csv” extension.

FilePath Expression

Press “OK” once this has been completed.

Adjusting the Connecting String

Next, we need to adjust the connection setting for the flat file export. To do this, highlight the “AccountTypes.csv” connection from the “Connection Managers” panel towards the bottom of your screen:

Flat File Connection




Navigate to the “Properties” panel, and find the “Expressions” property. Click the ellipsis button to the right of the property. The “Property Expressions Editor” window will appear. In the first row, choose “ConnectionString” from the “Property” column dropdown. In the “Expression” column either type “@[User::FilePath]” or click the ellipsis and choose the variable name from the “Variables” folder in the resulting window that appears. This specifies that we want to send our data to the location specified by the “FilePath” variable.

Property Expressions

Press “OK” once this has been completed.

Guess what? That’s it! That is all it takes to append the date to the file name for a flat file export using SSIS. To test the package, click the green triangle (play button) on the toolbar underneath the main menu. Your package should like the screen below if everything executed successfully:

Debug Mode

With the package successfully completed, navigate to the location of the export and check to be sure that the file was exported properly. You can see in the image below that I have a file with the date appended to the location I expected to see the file:

Desktop Image

What might some like an otherwise challenging task can be completed rather simply using SSIS. Understanding what types of expressions can be created and how they can be employed throughout your SSIS packages is a valuable skill. This simple example hopefully provides you with some insight on other ways you can utilize SSIS in your organization!

/by

Using the SQL Agent to Automate SSIS Packages

, , , , ,

In our last post, we learned how to use SSIS to export data to a flat file. While that is certainly a step in the right direction towards removing all manual effort from our reporting processes, we still had to manually kick off that SSIS package. In this post, we are going to learn how we can use the SQL Agent to schedule this package to automatically execute.

What is the SQL Agent

The SQL Agent is a Windows service that executes scheduled tasks or jobs. Creating a SQL Agent job allows us to schedule when an SSIS package, for example, is executed automatically. SQL Agent jobs can be created to execute a number of tasks including: executing T-SQL code, SSIS packages, SSAS queries, PowerShell scripts, and a number of other types of tasks. For our purposes, we will be creating SQL Agent jobs to automatically execute our SSIS packages. ETL jobs are often automatically executed at a set time (usually nightly) via SQL Agent jobs, therefore understanding the basics of the SQL Agent are critical to becoming well-versed in the data warehouse/business intelligence toolkit contained within Microsoft SQL Server.

Creating the SQL Agent Job

Before we create the SQL Agent job, open up the “Credit Union SSIS Solution” in SSDT if it is not currently open. In the “Solution Explorer”, right click on the “Package.dtsx” package name and click “Rename”. Rename the package “Export to Flat File.dtsx”. Press the “Save All” icon on the toolbar and then exit SSDT (SQL Server Data Tools).

NOTE: the next steps require you have the proper authorization and permissions to create, schedule, and execute SQL Agent jobs. You may need to speak with your IT department for proper authentication depending on your SQL environment.

Open SQL Server Management Studio (SSMS) and login with your Windows credentials. At the bottom of the “Object Explorer” you should see an item named “SQL Server Agent”. Click the toggle button to the left of it to expand the folder. Once expanded, expand the “Jobs” folder.

Object Explorer

This where the SQL Agent jobs are stored. Notice that two jobs currently appear – these are generated by the system and can be ignored. To create a job, right click on the “Jobs” folder and click “New Job”.

New Job

Give this job the name “Exporting to Flat File”. Leave the other default options unmodified. In every SQL Agent Job you can have many steps. So, for example, we could have multiple SSIS packages executed in the same job. This is a nice feature if you have recurring processes that need to be executed on the same schedule. You could create a single job for all of your weekly recurring reports, so that you would only need to monitor and maintain a single job instead of one for each SSIS package.




Click on the “Steps” option on the left side of the “New Job” window.

Steps

Click the “New” button near the bottom of the window to create a new job step. Name this step “Export to Flat File SSIS Package”. In the “Type” dropdown, select “SQL Server Integration Services Package”. You will notice the bottom half of the window will change significantly once you make the selection.

In the “Package Source” dropdown, choose “File system”. This is telling the SQL Agent that we will be choosing a package located in a folder instead of being stored somewhere else (there are other package storage mechanisms within SQL Server that could be selected). With “File system” selected, click the ellipsis button to the right of the “Package:” text box near the bottom of the window. Locate the SSIS package “Export to Flat File.dtsx” that we created in the previous post and renamed earlier in this post. Press “Open” once you have found this item.

Job Step

After making the necessary changes, your window should look similar to the image above. Press “OK” once you have made the modifications. Once you press “OK”, you will be brought back to the “New Job” window which should now look like:

New Job Part 2

We have created the SQL Agent job with the appropriate step created to execute the SSIS package we created in the last post. Now we must schedule the job to execute at a specific time.

Click the “Schedules” option from the panel on the left. Once the window changes, press “New” towards the bottom of the screen.




When the “New Job Schedule” window appears, type “Sample Ad Hoc Execution” in the “Name” text box. Then, in the “Schedule type:” dropdown, choose “One time” from the menu.

Schedule the job to execute about ten minutes from when you are completing this step. So, I am completing this step at around 11:50 AM on 6/29/2015, so I will set the job to execute at 12:00 PM.

Job Schedule

Press “OK” once you have made the schedule updates. Press “OK” at the bottom of the “New Job” window to save the SQL Agent job we just created. If there were any errors or you do not have the necessary permissions to create the job, SQL will throw an error and a pop-up window will appear.

You will notice that the “Exporting to Flat File” job now appears under the “Jobs” folder in the “Object Explorer”.

Updated Jobs Folder

Once the scheduled execution time passes, right click on the job in the “Object Explorer” and click “View History”. You will see a successful execution logged with the appropriate details on the screen. If anything failed in the job, this window will tell you why the error occurred. Using this log viewer is incredibly helpful when debugging a failed SQL Agent job or step.

View History

You can navigate to wherever you saved the exported flat file and see that the file was modified and created right at the time we specified.




And just like that you created your own SQL Agent job and automated the execution of an SSIS package. This is a critical step towards leveraging the most valuable tools within Microsoft SQL Server and its business intelligence operations. If you need to automatically import data into a SQL table or automatically execute a set of SSIS packages, using a SQL Agent job provides you with the flexibility and automation you need to be efficient and successful!

/1 Comment/by