Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This tutorial will help you to get started with SSIS (SQL Server Integration Services)

Getting started with SSIS

Getting started with SSIS - Part 6: Import Export Wizard Part 1

Feb 21 2012 12:00AM by Sudeep Raj   

As I mentioned in the last chapter, we will look into our very first SSIS package. There are multiple ways to build an SSIS package. The simplest way to do so is Import export wizard. You can start the SQL Server Import & Export wizard by various ways. Below are the ways to start:

  • In Business Intelligence Development Studio, right-click the SSIS Packages folder, and then click SSIS Import and Export Wizard.
  • In Business Intelligence Development Studio, on the Project menu, click SSIS Import and Export Wizard.
  • In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a database, point to Tasks, and then click Import Data or Export Data.
  • In a command prompt window, run DTSWizard.exe, located in C:\Program Files\Microsoft SQL Server\90\DTS\Binn.

I will be using the 3rd way to start the Import & Export wizard. All the methods will show you the same wizard so you could try any of these.

Let us try to use the Import & Export Wizard to load the following data to a SQL Server Table.

Name,Age,Sex

Sam,28,M

Naom,23,M

Rita,26,F

Lets now start the with the Import & Export Wizard. I will be using screenshots along.

In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a database, point to Tasks, and then click Import Data. See the figure below:

start Import Export Data Wizard

On doing the above, the Import and Export Wizard will open. On the welcome screen you get a brief description about the wizard. You could choose to not see this message when you open it the next time. Just check the checkbox towards the bottom. Once done, click Next.

SQL Server Import Export Data Wizard – Welcome window

The next screen of the wizard is the place where you provide the details of the Data Source (Fig.3). There are various options for Data Source varying from Flat File Source (chosen for my example), Excel Source, Access Source, OLEDB Source, etc. You need to select the appropriate source as per you need. Since I have selected Flat File source as the data source I get the following fields. Had I chosen OLEDB Source it would have been asking me for the Server details and the database to fetch the data from etc.

On the General tab, you see the features as mentioned below:

  • In the File name click the browse button and select the input file you wish to import to the SQL Table. If you are working in a different locale, you could set that. Else, let it remain as the default.
  • Depending on the Format of your input data, you could select Delimited or Fixed Width File. I am using a comma delimited file hence selected Delimited here.
  • If you have a text qualifier, you could select it from the drop down list, which has the most commonly used text qualifiers. In our example, we do not have a text qualifier so we leave it blank.
  • The next point is Header row delimiter. In case your header row has a different delimiter compared to the detailed records, then this feature is useful. This wizard does all these settings by default after reading your file, so mostly you do not need to change them.
  • Now let’s say we need to ignore the first records in the data as that is not relevant. So here we set the Header rows to skip to 1.
  • If you have the column names in the first row you need to check the check box stating “Column names in the first data column”. What this will do is take the column names from this and not use this as a data record. If you do not do this in our example Name, Age, Sex will also be treated as data records (as I have not even set the Header rows to skip). This is a very useful feature because if the column names are there, the wizard reads them and creates the meta data accordingly. Else you will need to put them manually for easy maintenance and development.

SQL Server Import Export Data Wizard – Choose a Data Source: General

fig. 3

Once you have the General settings for the flat file, click the next tab Columns (Fig. 4). As the name suggests, here we provide the basic details of the columns and you get to see the preview of the data.

You do not need to make changes in the Data source, as it is set to Flat File Source in the last tab itself. Row delimiter is set to {CR}{LF} by default. CR is for Carriage return while LF is line feed terms derived from olden day’s typewriter. Windows uses both for line feed (new line) while UNIX uses just LF.

Column delimiter will be set automatically if it is among the once available in the drop down list. Else, you need to do the advanced settings, which I will explain later. Other column delimiters commonly used are semicolon { ; } , pipe or vertical bar{|} , colon {:}, tab {t}, etc.

On doing these basic setting, you can view the preview of the file.

Note: Had we not checked the box stating “Column name in the first data row” we would not get the column names as the you see now (Fig. 4). For column headers, you would have seen Column1, Column2 & Column3. And the current column headers would be treated as data record and you would have 4 records in you data now. You data would look like:

SQL Server Import Export Data Wizard – Choose a Data Source: Columns

Fig. 4

Let us move on to the next tab “Advanced” (Fig. 5).

As you see here, we will have the list of all columns. As you click each column, on the right pane you can see various properties for the column. Primarily you could set the data type of each column and the length, in case it is of string type. In its numeric, you need to set the precision and scale.

Note: By default, all the columns would be assigned datatype as string ([DT_STR]) with OutputColumnWidth (length) set to 50. You need to take care about this or else there might be truncation and your package would fail if the data in any column is greater than 50 characters. In case all the data in your particular column is of at max 10 characters, then you would be unnecessarily wasting memory and your package performance would be affected.

Note: Had we not checked the box stating “Column name in the first data row” we would not get the column names as you see now (Fig. 5). All you would see Column1, Column2, Column3 one below the other. It will not be an issue if the number of columns is 5 or less. But imagine if you have greater than 10 columns. It would get very difficult to track which column is representing what data. And this would lead to error, delay in development and MOST difficult to maintain the package later.

SQL Server Import Export Data Wizard – Choose a Data Source: Advanced

fig. 5

After the Advanced tab, click the Preview tab and you would see a similar preview as you saw in (Fig. 4). In case you do some changes in the advance tab which were not there in the Columns tab then you will see difference in the 2 previews. Otherwise they will be the same. Till this point we have set up the source of our ETL.

Once you are satisfied that all the columns are well mapped click the Next button. You will be in the next window (Fig. 6)

Now we need to configure the destination. As mentioned at the beginning, we need to get the data from a flat file to a table in a SQL Server database. Let the Destination be set as default “SQL Server Native Client 10.0”.

In the Server Name provide the server name. I have provided a period {.} which represents localhost. In case you have a SQL server instance created you would need to put that as well .

Select the Authentication mode. You need to select the radio button accordingly.

Next, you need to select the database name from the drop down list (provided you have given the valid credentials above). You could also create a new database if you wish to send the data to a new database. All you need to do is click the New button and provide the database name. Once completed, click the Next Button.

SQL Server Import Export Data Wizard – Choose a Destination

fig. 6

The next window will have the option to select the source and the destinations configured earlier (In our case as we have just done for one. In case the source was SQL server, then we would have to select the tables we would like to export.) Here we just need to select the check boxes you see below (Fig. 7). In the Destination, just key in the name of the table you wish to load the data. You are now almost done. Click the Next button.

SQL Server Import Export Data Wizard – Select Source Tables and Views

fig. 7

The primary setting for you 1st SSIS package is done. The next window has the option to Run & Save the SSIS package (Fig. 8). You have the option to run the package immediately AND / OR save the SSIS package for later execution. I select the 2nd option. You could select any of the options or both. I would just be saving the package now and will execute that later.

Once you select the option Save SSIS Package, you need to select from 2 options.

  1. SQL Server: The package would be stored in SQL server and you could execute the package from SSMS and easily execute them from there
  2. File System: The SSIS package would be saved on you system. In addition, here you could open the package and edit using BIDS if you feel the need. Then execute it with SSIS Package Execution Utility.

We will talk in detail about the next property that is set on this screen, Package Protection level. You could leave it to the default value or set it to Do not save sensitive data as I have done.

Click Next after this is done.

SQL Server Import Export Data Wizard – Save and Run Package

On the next screen, you see very basic settings for the package like providing a name, description to the package. And finally providing the path where we need to save the package. Thats it!! Click Next to goto the next screen :)

SQL Server Import Export Data Wizard – Save SSIS Package

The next screen shows a brief summary about the package. Primarily what are the source and destination details along with the package name and location. It also provides whether the package will execute on clicking the next button or not. If you remember I had selected the option to not run the package immediately. Hence you see the last line on this screen as “The package will not run immediately”. One all looks fine click the Next button.

SQL Server Import Export Data Wizard – Complete the wizard

On clicking the Next button, you see that the package being validated for all the setting and finally it is saved. It would have run if we had chosen the option of Run immediately. See image below.

SQL Server Import Export Data Wizard – Execution status

You can go-to the path you provided and check the file with .dtsx extension. That is your SSIS Package.

That is it for this chapter. We will dissect the package in the next chapter. Take a break and come back soon :)


Sudeep Raj
12 · 13% · 4306
8



Submit

Your Comment


Sign Up or Login to post a comment.

"Getting started with SSIS - Part 6: Import Export Wizard Part 1" rated 5 out of 5 by 8 readers
Getting started with SSIS - Part 6: Import Export Wizard Part 1 , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]