SQL server import and Export Wizard

In this article I will discuss about import and export wizard tool. Import and export wizard is the easiest tool to import data or export data from sources to destinations. Sources and destinations can be either SQL server, DB2, Oracle, Excel, flat files or access database.

Import and export wizard uses SSIS framework internally and provides a wizard to import or export data. The complete process done by the wizard can also be saved as an SSIS package.

Import and export wizard can be opened in the below mentioned ways.

1. Click on start button select import and export wizard under SQL server installation program
2. In SQL server management studio, Right click on database and select import data or export  data under tasks options based up on the type which you are doing
3. Another option is to open wizard is right click on SSIS folder in BIDS/SSDT environment and selecting import and export wizard.
4. You can also open the same wizard by providing command dtswizard.exe on the command prompt or run prompt.

You can choose any of the options to open import and export wizard. Wizard opens with welcome screen as pasted in Figure 1. Irrespective of the operation(import or export) you are performing, the welcome screen is same which explains what can be done using this tool.

Figure 1

Click on next button which opens Source wizard screen where you need to provide source information such as source type and required data base or files. Source connection is the one from which data will be exported.

Options on the data source wizard will be changed based up on the type of data source selected.

Figure 2 shows the options displayed when SQL Native client 10.0 selected as Data source. When SQL native client data source selected, you need to provide server name, authentication type and data base to fetch data

Figure 2

Figure 3 shows the options displayed when Microsoft Excel selected as Data source. Excel file location should provided to export data.

Figure 3

Figure 4 shows options displayed when Flat file source selected as Data Source. You need to provide file location and delimiters when Flat file selected as Data source.

Figure 4

In the same way, you need to provide all required fields based up on the data source selected.

Example: For the purpose of example (copying data from SQL server data base table into flat file), SQL server native client 10.0 selected and all other options provided as shown Figure 5.

Figure 5

Click next button after providing source and required options. Destination wizard will be displayed as shown in Figure 6.

In Destination wizard, you need to provide destination data source and required options. All the data sources which are available in source wizard are available in destination wizard as well.

For the purpose of the example, Flat file destination is selected and required options such as file name and delimiters should be selected as shown in Figure 6.

Figure 6

Click next button This option opens another screen where you need to select source tables or views/write a query that you want to transfer to the destination as shown in figure 7. For this example Copy data from one or more table or views options selected and click next button.

Figure 7

When next button clicked, which opens Destination configuration wizard as shown in figure 8 where table or view and delimiters should be specified. If you want you can click on edit mapping options and change default options. Click on Preview to view how data will be stored in flat file. Click next button after completion Destination configuration.

Figure 8

If no errors found, It will take you to save and run package screen. Here you can specify whether you want to execute package immediately or save package either on SQL server or File system for later use.You can also specify how you wish to protect sensitive data in package.

Figure 9

For the purpose of this example i have selected Run immediately and Save SSIS package option(on SQL server) and click next button. You are then taken to save SSIS package screen as shown in Figure 10.

Figure 10

Here you can specify name, description, server name and authentication type to save package. All SSIS Packages will be saved in MSDB database.

Click on next button which takes us to complete the wizard screen as shown in Figure 11 which displays complete details.

Figure 11

After verifying details on complete wizard screen click Finish button this takes you to last screen of the wizard which displays execution of the package as shown in Figure 12

Figure 12

After Executing, data from the table is saved into flat file and Complete process saved as an SSIS package under MSDB database as shown in Figure 13.

Figure 13

Read more ...

SSIS Wizards and SSIS terminology

In this article I will explain briefly about SSIS wizards (import and export wizard and BIDS Environment) and SSIS terminology

ETL: ETL stands for Extract,Transform and Load. SSIS is an ETL tool and used to extract data from multiple sources transform into required format and then load into destination.

Import and Export Wizard: Import and Export wizard is a tool provided with SQL server and used to import data from any OLE-DB source complaint data source to destination. This wizard is a quick way to transfer data from source to destination with minimal transformations.

Business Intelligence Development studio (BIDS)/SQL server Data Tools (SSDT): BIDS/SSDT is central environment where you will spend most of your time to create SSIS projects. This environment provides all the features to create complex SSIS projects.

Solution Explorer: A solution explorer is a container that holds many projects. For example you have solution that may contain many projects such as ASP.NET project, SSIS project, SSRS project and SSAS project. By default Solution explorer will not be shown if only single project exists. To enable this feature Go to Tools->Options->expand Projects and solutions select Always show solution under general tab.

Project Explorer: New project will be added to existing solution explorer by right click on solution explorer window Add -> New Project. By default SSIS Related project contains single package under SSIS packages folder and 3 other folder Data sources, Data source views and Miscellaneous when the project created.

Data sources: A data source allows you to create data connection that can be used to perform ETL operations by whole package. Data source connections can be OLEDB, ODBC, ADO.NET, and DB2. Data sources can be either by single package or all packages in the Project.

Data sources Views: Allows you to create logical views of your business data. These views consist of few tables or views that can shared either by one package or all packages in SSIS Project.

SSIS Packages Folder: This contains all packages

Miscellaneous folder contains any other files added to our project other than Data source, Data source views or SSIS packages.

.dtsx: An SSIS package extension

.ds: A shared Data source extension

.dsv: A shared Data source view extension

The Toolbox: The tool box contains all items that can be used in designer pane. Tool box in Control flow designer pane displays all tasks and Tool box in Data flow designer pane displays sources, Transformation and destination.

Properties Window: Any task or selected item can be customized using properties window. Select any particular task and press F4 to display properties window.

SSIS Terminology:

Package: A package is core component in SSIS and an executable program that contains work flow and business logic. A package is an unit of execution much like .NET program. Package in SSIS created with .DTSX extension which is XML structured file.

Package is a collection of tasks which are connected by precedence constraints.

Control Flow: The main important component in SSIS package is control flow and control flow is collection of tasks which are connected by Precedence constraint. Control flow contains work flow of package which consists of containers, tasks and precedence constraints.

Tasks can be dragged from tool box on to control flow design pan, yellow warning or red error will be shown on the task until you configure the task as shown in the below figure.

Task: A task is discrete unit of work which provides functionality to SSIS package. SSIS provides set of tasks. Read tasks article to know more about SSIS tasks

Precedence constraint: Precedence constraint is used to execute the tasks in specific order and which defines work flow of the package. A constraint controls the execution of two linked tasks by executing the destination task based up on final state (Success, Failure, and Completion) of prior task and business rules that are defined by expressions. You need to specify constraint or Expression in Precedence constraint editor as shown below

Evaluation operation specifies either constraint or expression or both.

There are 3 constraints values.

1. Success: A task that’s linked with this constraint executed only prior task is successful. This task is colored in Green

2. Failure: A task that’s linked with this constraint executed only prior task is failed. This task is colored in Red

3. Completion: A task that’s linked with this constraint executed only prior task execution is completed whether or not if prior task is succeeds or fails. This task is colored in Blue

Expressions can also be specified along with constraints.

Annotation: An annotation is comment in SSIS package. To add annotation Right click on package design pane and select Add annotation. Annotations are used to explain others about your package.

Connection Mangers: Connections manager tab is placed at the bottom of the package which contains all connections required for control flow and data flow in package. Connections can be FTP connection or HTTP connection or OLEDB connection or flat file connection.

You can create connection by right clicking in the connection manager tab and choosing appropriate connection.

Variable: Variable allows you to create dynamic SSIS packages and which are much like variables in other programming languages. Variable in SSIS are classified into two categories.

System Variable system variables are pre-defined variables in SSIS such as machinename, Containerstarttime, PackageName, PackageID and all system variables are prefixed with System as System::<VariableName>

User Variable user variables are created in package and all user variables are prefixed with User as User::<VariableName>. The scope of user variable can be task level, container level, event handler or entire package.

To access variable window, Right click on deign pane and select variables. Grayed colored variables are system variable and blue colored variables are user variables.

NOTE: Scope cannot be changed once defined while creating user variables variable.

Data flow: Data flow is another import component in SSIS after control flow. In data flow tab, you will perform core functionality of SSIS i.e. extract data from different sources, transform it and then write into destination.

Data flow is one task in control flow. When data flow task is created, subsequent data flow task will be created

Example: in the below example 3 data flow tasks are created. Each data flow task has its own data flow designer pane separately as shown below.

Event Handler: Event handler tab is used to handle errors or warnings or completion in task or container.

Package Explorer:

Package explorer consolidates all information including variable, precedence constraints,Event handler, Connection managers, Log providers and executables.

Read more ...

BCP utility

In this article I will discuss about BCP utility tool

BCP stands for Bulk copy Program which is used to copy bulk data between Microsoft SQL server data table and data file in specified format. This utility mainly used to import/export large number rows between table and file.

The syntax of BCP utility is as follows.

Syntax: BCP {dbtable | query} {in | out | queryout | format} datafile

[-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-V file format version] [-q quoted identifier] [-C code page specifier] [-t field terminator] [-r row terminator] [-i inputfile] [-o outfile] [-a packetsize][-S server name] [-U username][-P password][-T trusted connection] [-v version] [-R regional enable] [-k keep null values] [-E keep identity values] [-h "load hints"][-x generate xml format file]

Dbtable: the name of the destination table when importing data or source table when exporting data. Dbtable should specify in fully qualified path i.e. (DatabaseName.Schema.tableName)

In: copies data from file to table

Out: copies data from table to file. If you specify existing file, file will be overwritten.

Queryout: copies data into file using query and it should specified only when bulk copying from file

Format: creates format file for the table. The same will be used when bulk copying data using format option. The BCP utility refers this format while inserting data into table which makes sure you need not specify any format information. You need to specify –f option when you are creating format files and also can specify –x if you are creating XML based format file.

Datafile: the name of file. This data file can be source if you are importing data into table or destination when exporting data from table

Remaining all are options mentioned based up on the BCP command you are executing.

Examples:

Let us create below mentioned table

create table FormatExample(id int,

name varchar(10),phonenumber int)

insert into FormatExample values(1,'name1',123456)

insert into FormatExample values(2,'name2',234567)

insert into FormatExample values(3,'name3',345678)

insert into FormatExample values(4,'name4',385678)

insert into FormatExample values(5,'name5',123456)

insert into FormatExample values(6,'name6',345677)

insert into FormatExample values(7,'name7',456788)

insert into FormatExample values(8,'name8',856789)

Importing data from table into file (out will be used)

bcp AdventureWorks2008.dbo.FormatExample out Format.txt -U sa -P <ur_sa password> -c

bcp AdventureWorks2008.dbo.FormatExample out Format.txt -T –c

-c is Char type all columns data will be loaded into text file as character. –T for trusted connection.

Importing data into table from file (in will be used)

bcp AdventureWorks2008.dbo.FormatExample in Format.txt -U sa -P <ur_sa password> -c

bcp AdventureWorks2008.dbo.FormatExample in Format.txt -T –c

Importing data from table into file using query out option

bcp "select id,name,phonenumber from AdventureWorks2008.dbo.FormatExample" queryout Format1.txt -U sa –P<ur_sapassword> -c

This query out option only used when bulk copying data into file.

All above mentioned queries executed in local server as SQL server installed locally. You can even execute same queries on other servers by passing –S option to bcp utility

Creating format file

bcp AdventureWorks2008.dbo.FormatExample format nul -U sa -P <ur_sa password> -n -f Format.fmt(Creates format file in xml format)

bcp AdventureWorks2008.dbo.FormatExample format nul -U sa -P <ur_sa password> -n -x -f Format.xml(Creates format file in XML format).

You should specify nul option after format to create format file.

-c creates all values as character type format file but –n creative native data types in format file.

Importing data with format file:

bcp AdventureWorks2008.dbo.FormatExample in Format.txt -f Format.fmt -U sa -P <ur_sa password> (Import using non-xml format file)

bcp AdventureWorks2008.dbo.FormatExample in Format.txt -f Format.xml -U sa -P <ur_sa password> (Import using xml format file)

NOTE: Please leave comment:

Read more ...

File System Task in SSIS

In this article I will discuss File system task in SSIS. File system task in SSIS is used to perform file or Directory related operations which are creating, moving, and deleting files or directories.

Drag and Drop File system task from tool box on to control flow tab as shown in Figure 1

Figure 1

Double click the File system task which display configuration editor as shown in Figure 2. Task editor has only two tabs.

Figure 2

In General tab, you can specify the Operation type and Connections which are required to perform the operation. Here connections can be specified through variable or file connection.

The File system task options are

Copy Directory: Copies all files from one directory to another directory which requires both source and destination connections.

Copy File: Copies a specific file. Which requires both source and destination connections.

Create Directory: creates directory which requires only source connection.

Delete Directory: deletes directory which requires only source connection.

Delete Directory content: deletes all files from directory which requires only source connection.

Delete File: deletes file which requires only source connection.

Move Directory: Moves directory from one location to another location which requires both source and destination connections.

Move File: Moves a specified File from source to destination which requires both source and destination connections.

Moving File or Directory option requires OverWriteDestination need to be set. If OverWriteDestination option set to false the task fails if File or Directory exists in destination.

Rename File: Renames a file which requires both source and destination connections.

Set Attributes: Which sets attributes (Hidden, Ready only, Archive, System) for a file which requires only source connection.

The best thing in SSIS is if you want create Directory C:\SSIS\FileSystemTask\Directory1\File\ all you need to do is specify full path in task which creates all directories at a time. You need not to create each directory individually through task.

But the same will not work when are you are moving directory from source to destination directory (C:\FileSystemTask to C:\FileSystemTask\SSIS\FileSystem) the task fails if destination does not exist.

NOTE: Please leave a comment

Technorati Tags: SSIS,MSBI,File System task,Task

Read more ...

Bulk insert task in SSIS.

In this article we will discuss about bulk insert task.

Bulk insert task in SSIS allows us to load data from text file or flat file into SQL server database table using BULK insert command or BCP.EXE (Bulk Copy program) command line utility tool. This functionality gives fastest way of loading data.

BULK insert is a SQL statement which is used import data from flat file but BCP is an utility used in command prompt to load data from flat file into Database table

Read online articles for more details to learn more about Bulk insert command and BCP.exe utility

Drag Bulk insert task from tool box onto control flow as shown in figure 1.

Figure 1

When you double click Bulk insert task it will open task editor to configure as shown in figure 2. If task is not configured properly one red symbol shown as shown in figure 1

Figure 2

In General tab, Suitable name and description for the task should be provided.

Next tab is Connection where source file connection and destination table should be provided as shown in Figure 3

Figure3

Select destination from drop-down list if there are any created. Next, Select destination table from next Destination Table drop down box.

Next, specify the file using source connection by File drop down box. Both source and Destination should be created in connection managers tab. if not create new connections by selecting <New connection….> tab in respective connection drop-down box.

After specifying source and destination connections, you need to specify the format which is having two options. Options 1 specify allows Row delimiter and Column delimiter which are having same set delimiters as shown in figure 4

Figure 4

{CR}{LF} = New Line

{CR} = Carriage return

{LF} = Line feed

{;},{,}, Tab, Vertical Bar {|}

When option 2 Use File selected, you need to specify the format (.fmt) file which is created using BCP utility as showing figure 5

Figure 5

In the Options tab, you need to specify options for inserting data from file into table.

Figure 6

In Advanced Options, You can specify code page of source and you can rarely change Codepage type from RAW which is default. The DataFileType option can specify what file type is. Options here are char, native, wide char, wide native. Generally files will be loaded using char type but some times if file is being loaded using format file (.fmt) which is created native option you then need to specify native in DataFileType.

Other Advanced options, Batch size specifies how many rows should be executed as single batch. By default batch size is 0 which means all rows will be executed as single batch and you can specify first row and last row to copy using FirstRow and LastRow options

The options drop down box contains five options as shown in figure 7.

Figure 7

Check constraints: This options checks constraints on columns before committing the record. This option is turned on by default.

Keep nulls: this option keep nulls if any column is left as blank.

Enable Identity insert: This option should be enabled if destination table has identity column. If not this task will be failed

Table lock: This option creates SQL lock on the destination table from insert and update options from other processes.

Fire Triggers: Enable if this option if insert triggers need to be fired while inserting data on the table. By default this option is turned off.

Other options are SortedData which specifies what column you wish to sort by while inserting data. By default sortedData is false. Type the column name in the SortedData field if you need to specify. The MaxErrors option specifies how many error rows can be ignored while inserting data before the task is stopped with an error. By default MaxErrors is 0, if single row has a problem, entire task fails.

NOTE: Please leave comment if you like the post

Technorati Tags: SSIS,MSBI,Bulk insert task,task,bulk insert

Read more ...

SSIS tasks

Today we will learn about various tasks which are available in SSIS. Before going to know about different tasks first will discuss about what is task.

Task: A task is a discrete unit of work that performs some action required in a package which may be moving a file or sending email or sending/Receiving file from FTP server. SSIS tasks are foundation of the control flow in SSIS. When you are on control flow tab SSIS displays set of controls which represents workflow of the package.

SSIS tasks are divided as following categories

Task Name	Task Description
Looping and Sequence tasks
Sequence Container	Used to group set of tasks as a single component.
For Loop Container	Used to loop through certain tasks in your package
Foreach Loop Container	Used to loop through collection of objects in your package.
Scripting tasks
Script task	This task allows us to perform .NET based scripting in Visual studio
Analysis Service Tasks
Analysis Services Execute DDL task	Used to execute DDL task in Analysis services(create, drop, delete cubes)
Analysis Services Processing task	Used to process Analysis service processes which are cubes, dimension or cube model
Data Mining Query task	Allows us to run predictive queries against your Analysis Services data - mining models
Data Preparation task
Data Flow task	This task is used to perform ETL (Extract, Transform and load) operations in SSIS package.
Data Profiling task	task allows for the examination of data to replace your ad - hoc data profiling techniques
File System Task	This task is used to perform Directory related or file related operations
FTP task	Used to Send/receives files from FTP server
XML Task	Parse or Processes XML file. It can merge, split or reformat XML file
Web Service task	Used to execute web methods on web service
RDBMS server tasks
Execute SQL task	executes SQL statement or SQL stored procedure
Bulk Insert task	Used to load data into table by using BCP utility in SSIS package
Backward Compatibility tasks
Execute DTS 2000 Package Task	Used to execute legacy 2000 DTS package
ActiveX Script task	Used to execute ActiveX script in your SSIS package. This task only used to conversion of legacy DTS package which uses ActiveX scripting method.
Work Flow tasks
Execute Process task	Used to execute external process in your package such as notepad.exe,command.exe
Execute Package task	Used to execute some other package with in the package
Message Queue task	Sends or receives messages from MSMQ(Microsoft Message Queue)
Send Mail task	Used to send mails using SMTP
WMI Data Reader task	This task executes WQL queries on Windows Management Instrumentation
WMI Event Watcher task	This task gives ability to SSIS to respond certain Windows event
SMO(SQL management object) administration task
Transfer Database task	Transfers  Database from one server to another server
Transfer Error Messages task	Transfers Error messages from one SQL server to another server
Transfer Jobs task	Transfers Jobs from one SQL server to another server
Transfer Logins task	Transfers SQL logins from one server to another server
Transfer Master Stored Procedures task	Transfers Stored procedures from one server to another server
Transfer SQL server objects task	Transfers SQL Objects(tables, views, triggers, procedures, User-defined functions..) from one server to another server

NOTE: Leave your comment if you like the post

Technorati Tags: SSIS,SSRS,task

Read more ...

Web service task in SSIS?

Today we will learn how to use web service task in SSIS. Before going into details about the task we will learn about web service to understand better

What is web service?

Web service is piece of information which can be shared between two devices through World Wide Web (WWW). This has an interface file WSDL (web service definition language) which is in machine readable format (XML) and the same is used to connect to consume the web methods provided by web service.

You can search more about web service in online books to know more about web service and WSDL

Web service task in SSIS is used to retrieve data in XML format by executing web methods in web service

Below mentioned steps will explain how to use web service task in SSIS

Step1: Drag and Drop Web service task from tool box on to control flow design editor as shown in figure 1

Figure 1

Step2: Web service task should be configured by double clicking the task which displays Web service task editor as shown in Figure 2.

Figure 2.

This task requires the establishment of an HTTP connection manager where web service is hosted or specific WSDL file is placed on website. If HTTP connection does not point to a WSDL file on site, Local version should be provided.

Click on HTTP connection tab which displays Http connection manager Editor where WSDL location should be placed. Credentials and Certificate should be provided if there are any attached to the web site. In this example I used web service which is hosted in localhost server as shown 3. Click on test connection link to verify your HTTP Connection.

You can configure the HTTP Connection Manager to access a Web services hosted on

www.webservicex.net, which is most commonly used for learning purpose.

www.webservicex.net/stockquote.asmx?WSDL is one such example to use stockquote web method which is hosted on web servicex.net

Figure 3.

Step3: Provide WSDL file for the web service. If WSDL does not exist you can download using Download WSDL option and OverWriteWSDLFile should be set either true or false based up on requirement as shown in Figure 2

Step4: The next step is to define input editor. Select the service which is used to execute web service and you need to specify the web method which you want to execute for the input of the task. Web service task will provide all the available methods by WSDL which is provided in general tab as shown in figure 4.

Once you select one of the web methods Add (Which takes two integer values and return addition of those two values as integer), the web service will display required Parameters for the task. You can provide hard-coded values or through variable as shown in figure 5.

Figure 4

Figure 5

Variable 1 = 10 and Variable2 = 20 values passed to Add method.

Step5: The remaining tab is output tab as shown in figure 6. The output of resulting web service method can be saved either variable, select a data type as string as output will be sent in XML format or File connection. In this example Output is saved in variable which is later printed using script task as shown in Figure 6.

Figure 6

Write below mentioned code in Script task which is to read data from Variable2 which is passed as ReadOnly variable in script task

System.Windows.Forms.MessageBox.Show(Dts.Variables["Variable2"].Value.ToString());

Running this web service task will result in calling the web method Add on the Service1 and retrieving the data in XML variable as shown in Figure 7

Figure 7

Technorati Tags: SSIS,MSBI,BI,Sql server,web service task,task

Read more ...