How to use Import and Export wizard (DTS) to transfer data to or from a SQL Azure database?

A lot of people are starting with experiments to look to the possibilities of SQL Azure. One of the things to start is a database with content. As described in one of my previous blogs, you can use the SQL Azure migration wizard. This is a nice tool, but most people are already aware of the Import and Export Wizard in SQL Server Management Studio (SSMS), also known in the past as Data Transformation Services DTS.

In this blog I will describe how you can use the Import and Export Wizard to transfer data from an on-premise SQL Server database to an SQL Azure database. What do you need to have to start using the Import and Export wizard.

1. Windows Azure subscription
2. Create a SQL Azure server and database
3. Configure the SQL Azure firewall
4. On premise SQL Server with SQL 2008 R2 with SSMS.
5. Create the tables you need in the SQL Azure database.
6. Create a clustered index on each table to which you want transfer data. Without a clustered index you CAN'T transfer data to a SQL Azure database.

Now you have done all preperations we can start the Import and Export Wizard. Click Start, All Programs, SQL Server 2008 R2, Import and Export Data. The Import and Export wizard is now started.

1. Choose a datasource. This is your on-premise SQL Server.
2. Choose a destination. This will be your SQL Azure database.
1. Destination: .NET Framework Data Provider for SqlServer
2. Encrypt: True
3. Integrated Security: False
4. Password: SQL Azure administrator password
5. TrustedServer: Certificate: True
6. User ID: SQL Azure administrator name. [username]@[sqlAzureservername]
7. Data Source: FQDN of your SQL Azure Server
8. Inintial Catalog: The SQL Azure database you have created to insert data.

3. Press next and execute the same steps as you normally do in the Import and Export wizard.
4. In case you forgot to create a clustered index you will get next message:
1. Messages
   
   Error 0xc020844b: Data Flow Task 1: An exception has occurred during data insertion, the message returned from the provider is: Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again. (SQL Server Import and Export Wizard.

Enjoy using the Import and Export wizard to transfer data to your SQL Azure database.
Tweet

Msg 567, Level 16, State 7, Line 1 File 'D:\TraceFiles\vrijdag8161700.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file. fn_trace_gettable

If you have a SQL Profiler trace file which you want to open in the query analyzer you can make use of the fn_trace_gettable function.  Assume you are running this script from a server named: ServerA and you have a SQL Server named: ServerSQL. On ServerA you have stored a trace file in the folder D:\TraceFiles.

On ServerA you executed next command to open the trace file in the SQL Server Management Studio.

SELECT eventclass,TextData,ApplicationName,NTUserName,
LoginName,CPU,Reads,Writes, Duration, ClientProcessID,
SPID,StartTime,EndTime,BinaryData
FROM :: fn_trace_gettable('D:\TraceFiles\vrijdag8161700.trc',default )

This will return:
Msg 567, Level 16, State 7, Line 1


File 'D:\TraceFiles\vrijdag8161700.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

The error is caused by the fact that the function fn_trace_gettable is executed on the SQL Server named: ServerSQL. This server has no acces to the file in 'D:\TraceFiles\vrijdag8161700.trc' on ServerA.

To solve this share the folder D:\Tracefiles for the user who need access to it.
SELECT eventclass,TextData,ApplicationName,NTUserName,
LoginName,CPU,Reads,Writes, Duration, ClientProcessID,
SPID,StartTime,EndTime,BinaryData
FROM :: fn_trace_gettable('\\ServerA\TraceFiles\vrijdag8161700.trc',default )

Now the ServerSQL has access to the file on ServerA.
Enjoy using fn_trace_gettable in your SSMS.

: Tweet

How to update your PowerPivot Field List after a change in the datamodel?

It can happen that you have made a nice PowerPivot dashboard with a lot of usefull pivots and charts. After a while some changes are made in the datamodel of the database on which you have build your PowerPivot dashboard. For instance one column is added to an existing table or view. In this blog I will describe how you can update your PowerPivot Field List with the new column, so you can make use of it.

• Open the Excel sheet with your PowerPivot dashboard.
• Select PowerPivot tab in the Ribbon.
• Press the button PowerPivot Window button to launch the PowerPivot window.
• Select the table or view on which a column is added

• Press the button Table Properties.

• Be sure to select Column names from Source.
• Scroll to the right. At the end you will see the new added column. Select this column. In this example Zipcode
• The added column ZIPCode is now added to the PowerPivot Window.
• Select the Excel sheet with you rPowerPivot dashboard.
• Show the Field List.
• The PowerPivot Field list has detected that a change is made. Press the refresh button.

• Select the Data tab in the Ribbon of your Excel sheet and press the refresh all button. This update your excel sheet with the information of the added column.
  

• Now look in the PowerPivot Field List and you will see the added column in the table or view.

Enjoy using your PowerPivot dashboard with the new column(s)

How to change the database for your PowerPivot sheet.

As of Office 2010, you can use PowerPivot in combination with Excel. The PowerPivot is a very powerfull tool to analyze the data in your database. After having build some nice pivots on one of your databases, it can happen that you want to re-use the same sheet on a different database.

In this blog I will explain how you can update your PowerPivot sheet with data from a different database.

• Open your Excell sheet.
• Select the PowerPivot ribbon tab.

• Select the PowerPivot Window button in the ribbon. The PowerPivot window is now started.
• Select the design tab.
• Select button: Existing Connections.

• Select your PowerPivot data connection.

• Press edit button.
• Change the server and or database you want to use.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

• Press the 'test connection' button to see if you can connect to the database.
• Press save.
• Press Close
• Select the Home tab in the PowerPivot ribbon.

• Select Refresh all button. Now the data refresh window is displayed.

 

• Press Close when data is updated successfully.

Now we need to update the excell sheet with the data in the power pivot sheet.
 

• Go back to your excell sheet.
• Select the Data tab in the ribbon.
• Select the refresh all button.

Now all data in your excell sheet is updated with the data of the selected database.
Enjoy analyzing your data with the PowerPivot.

Part 2: Analyze SQL Profile traces with SSRS dashboard. How does it work?

As described in my previous blogpost I started a series in which I will explain how you can make a dashboard in SQL Reporting Services to analyze your SQL Server Profiler trace files. In this blogpost I will explain what you need to install and configure to use this dashboard.

What do you need to install:

• SQL Server 2008 R2 database engine. This database server is used to import all trace files into a database.
• SQL Server Reporting Services. (2008 R2). This reporting server is used to host the dashboard.
• SQL Server Business Intelligence Development Studio. With BIDS you can modify the dashboard and deploy the reports to the reporting server.

What else do you need:

• Use the default trace file template of SQL Profiler to trace your application. Be aware to use the correct filter for the trace, so you got only these queries you want to analyze. For instance you can filter on hostname, spid or applicationname.
• Script to create database and table in which all trace files are imported.
• Import script to import the SQL Profiler trace files (.TRC)
• The SSRS reports of the dashboard to analyze the trace files.

First we will start to create a new PerformanceAnalyze database and import table: TraceFileImport

-- BEGIN Performance analyze script created by André van de Graaf
-- Blog site http://www.keepitsimpleandfast.com/

USE MASTER
GO

-- Create PerformanceAnalyze database
CREATE DATABASE [PerformanceAnalyze] ON PRIMARY
(NAME = N'PerformanceAnalyze',
FILENAME = N'D:\Data\PerformanceAnalyze.mdf',
SIZE = 102400KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'PerformanceAnalyze_log',
FILENAME = N'D:\Data\PerformanceAnalyze_log.ldf',
SIZE = 10240KB , FILEGROWTH = 10%)

GO

USE [PerformanceAnalyze]
GO

-- Create table in which all trace files will be uploaded.
CREATE TABLE [dbo].[TraceFileImport](
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[TextData] [ntext] NULL,
[ApplicationName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[LoginName] [nvarchar](128) NULL,
[CPU] [int] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[Duration] [bigint] NULL,
[ClientProcessID] [int] NULL,
[SPID] [int] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[BinaryData] [image] NULL,
[ImportID] [nvarchar](50) NULL,

PRIMARY KEY CLUSTERED
([RowNumber] ASC)
WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-- END Performance analyze script created by André van de Graaf


Now we have the database and import table, so we are ready to import the first trace files. The trace files will be imported by the stored procedure: PA_ImportTraceFile.

-- Create stored procedure to import the trace files
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PA_ImportTraceFile]
(@ImportID nvarchar(50), @TraceFile NVARCHAR(2000)) AS
INSERT INTO TraceFileImport
SELECT eventclass,TextData,ApplicationName,NTUserName,
LoginName,CPU,Reads,Writes, Duration, ClientProcessID,
SPID,StartTime,EndTime,BinaryData,@ImportID AS importid
FROM :: fn_trace_gettable(@TraceFile,default )
GO

This stored procedure uses 2 parameters: Importid,Tracefilename_and_location
The ImportID is used in the SSRS reports to select your trace file.

Example:
EXEC PA_ImportTraceFile 'CustomerX_1','D:\Traces\Cust_Trace1.trc'

In my next blogpost I will make the SSRS reports available and will explain what you need to configure at the reporting server to use these reports. So stay tuned for the SSRS reports.

Previous posts in this series:
Part 1 Analyze SQL Profile traces with SSRS dashboard.