How to strip double quotes while importing data from CSV or TSV using SSMS Import Data wizard OR SSIS?

Standard

Long Title! Let me explain. This post will help you solve following problem if you run into it:

1) You are using SSMS Import data wizard to load data from a comma (or tab) separated value (CSV/TSV) file into SQL Server Table & you find that your source data values has double quotes and so you want to strip them before loading to destination table.

2) You are using SSIS to load data from a CSV/TSV file into SQL Server Table & you want to strip the double quotes in source table fields before you load the data to destination table.

Double Quotes CSV file SSMS SSIS LoadSolution:

1. After you’ve configured the Flat File connection. you’ll reach to a point where you’ll see “Flat File Connection Manager” in SSIS. Or in the SQL Server Import & Export data wizard, you’ll see a dialog box to configure flat file connections.

2. In the Text Qualifier, enter

Strip Double Quotes SSMS SSIS Import Wizard3. Make sure to Preview the data to verify that the double quotes around data fields have been trimmed.

4. That’s it! You’ve successfully configured the flat file connection manager to strip double quotes.

How do you reduce the network “latency” between application and SQL Azure?

Standard

I was at SQL Rally recently (10-11 may 2012) where I happened to have a nice talk about SQL Azure with a fellow attendee. They were considering porting their database (that supports one of their apps) to Microsoft’s cloud service. One of the concern they had was “How to reduce the network latency between SQL Azure and their App?” And Since I knew the solution, I shared it with that person. I am sharing it here so others can benefit too.

Now one of the first question that I asked the attendee was: Are you also porting your app along with the database to Azure?

Turns out, They were considering to host the app on Azure cloud too. So technically that’s called a “Code Near” scenario – And in this case, the application and the database both *should* reside in the same data-center. if you do so, the network latency between your app and the database is minimal.

Now, if you have your app on-premise and you are considering SQL Azure, then select the data-center location that has the minimal network latency between your app and SQL azure. Technically it’s called Code-Far scenario I have written about one of the ways you can do so, here’s the URL: Testing latency between client and SQL Azure via client statistics in SSMS

That’s about it for this post.

Official Resource: SQL Azure and Data access

SQL Azure Beginner’s TSQL Task: Creating a basic stored procedure

Standard

Being New at something is scary! In Tech field, One of the ways you could NOT be NEW at something is to kick the tire’s of that tech for a while. To that end, Here are the few tasks that you could perform if you are new to SQL Azure – Kick SQL Azure’s tires for a while – write few TSQL on SQL Azure – And NOT be NEW at it anymore. Go Learn!

In this Part 1 of N, I am going to create a stored procedure that accepts a parameter with SQL Azure.

So I have a table that lists firstname, lastname of the customer along with location details. Here’s what it looks like:

querying the sql azure view

Resource: A Tutorial on Installing a sample database on SQL Azure.

And just note that I running the queries on SQL Azure management portal and not on SSMS. You can do so in SSMS too. But for this blog-post – I used the SQL Azure management portal.

So now we want to write a Stored Procedure which gives this information when it is called; Not only that, we want that stored procedure to return information about customers from the country that’s specified by the person calling the procedure. And such a stored procedure would empower the person who is going to call the stored procedure to filter the customer list based on the country of their choice. So let’s go ahead and write the code:

[sourcecode language=”sql”]

create procedure saleslt.spcustomerlocation @countryname nvarchar(50)
as
select firstname,lastname,city,stateprovince,countryregion from customertable
where countryregion = @countryname

[/sourcecode]

Now once we have created the stored procedure, It can be called by running the following code:

[sourcecode language=”sql”]

exec saleslt.spcustomerlocation ‘United States’

[/sourcecode]

And it would give us list of customers in United States like shown below:

running the sql azure stored procedure

So that’s about it for this post.

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

paras doshi blog on facebookparas doshi twitterparas doshi google plus

Part 10/10 of “Getting started with SQL Azure” series is live!

Standard

Part 1: We defined SQL Azure and discussed advantages of SQL Azure

Part 2: We created an Azure account and created our very first SQL Azure database

Part 3: We discussed about the provisioning and the billing model of SQL Azure

Part 4: We discussed the SQL Azure architecture

Part 5: We discussed the SQL Azure security model

Part 6: We discussed how to migrate Databases to SQL Azure

Part 7: We discussed how to improve performance of SQL Azure DB and options for planning backup and restore strategies.

Part 8: We discussed administrative tasks related to SQL Azure.

Part 9: we discussed about Developing SQL Azure applications.

Part 10 A: We discussed about SQL Azure Data SYNC and SQL Azure reporting

Part 10 B: Conclusion

part 7 of “Getting started with SQL Azure” series is live!

Standard

Aim of “Getting started with SQL Azure” series is to offer you a set of brief articles that could act as a Launchpad for your to-be wonderful journey of exploring Microsoft’s cloud based database solution i.e. SQL Azure.

In part 7, I have discussed following administrative tasks:

1. How to improve performance

2. Backup and restore Strategies

 

URL: http://beyondrelational.com/blogs/parasdoshi/archive/2011/11/27/getting-started-with-sql-azure-part-7-sql-azure-administration-i-lt-lt-paras-doshi.aspx

 

Summary:

Part 1: We defined SQL Azure and discussed advantages of SQL Azure

Part 2: We created an Azure account and created our very first SQL Azure database

Part 3: We discussed about the provisioning and the billing model of SQL Azure

part4: We discussed the SQL Azure architecture

part 5: we discussed the SQL Azure security model

part 6: We discussed how to migrate databases to SQL Azure.

Part 7: We discussed how to improve performance and backup options in SQL Azure

part 6 of “Getting started with SQL Azure” is live!

Standard

Aim of “Getting started with SQL Azure” series is to offer you a set of brief articles that could act as a Launchpad for your to-be wonderful journey of exploring Microsoft’s cloud based database solution i.e. SQL Azure.

In part 6, I have discussed the SQL Azure security model:

http://beyondrelational.com/blogs/parasdoshi/archive/2011/11/21/getting-started-with-sql-azure-part-6-lt-lt-paras-doshi.aspx

Summary:

Part 1: We defined SQL Azure and discussed advantages of SQL Azure

Part 2: We created an Azure account and created our very first SQL Azure database

Part 3: We discussed about the provisioning and the billing model of SQL Azure

part4: We discussed the SQL Azure architecture

part 5: we discussed the SQL Azure security model

part 6: We discussed how to migrate databases to SQL Azure.

[Link] 2011 Testing latency between client and SQL Azure via client statistics in SSMS

Standard

As I write this blog post, There are six location options while provisioning a SQL Azure server. And so while provisioning a SQL Azure server, who may have to decide the optimal location of the SQL Azure server based on the criteria that the latency between your application and the SQL Azure server is the minimum. And as you may know, we get better performance – if we are able to minimize the latency between client and SQL Azure. So let’s get into action.

read more

Forum Q&A: Is SQL azure and SQL Server the same?

Standard

Question:

Is SQL azure and SQL Server the same?

Answer:

I would recommend you read:

1. General Guidelines and Limitations SQL Azure (BOL)

2. SQL Azure vs SQL server (Technet Wiki)

3. Diagrammatic representation of “SQL Azure VS SQL server”

Link:

http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/dc4ddeff-d92d-42b0-9feb-407092e58b05/

Part 4 of “Getting started with SQL Azure” is live!

Standard

Aim of “Getting started with SQL Azure” series is to offer you a set of brief articles that could act as a Launchpad for your to-be wonderful journey of exploring Microsoft’s cloud based database solution i.e. SQL Azure.

In this blog post, I have discussed the SQL Azure architecture. Link:

http://beyondrelational.com/blogs/parasdoshi/archive/2011/10/10/getting-started-with-sql-azure-part-4-lt-lt-paras-doshi.aspx

Just to recap:

In part 3, i have discussed:

1. Provisioning model of SQL Azure

2. Billing Model of SQL Azure

read more: Getting started with SQL Azure – Part 3 << Paras Doshi

In part 2, i have discussed:

1) How to sign up for a Free trial of windows azure (to play with SQL Azure)!

2) How to create your very first SQL Azure database (and a table too!)

3) How to connect to SQL Azure server Via SQL server Management studio.

Read more: Getting started with SQL Azure – Part 2 << Paras Doshi

And in part 1, i have discussed

1) Where SQL Azure fits in the windows azure platform?

2) Defined SQL Azure

3) Advantages of SQL Azure

Read More: Getting started with SQL Azure – Part 1 << Paras Doshi

Part 3 of “Getting started with SQL Azure” is live!

Standard

Aim of “Getting started with SQL Azure" series is to offer you a set of brief articles that could act as a Launchpad for your to-be wonderful journey of exploring Microsoft’s cloud based database solution i.e. SQL Azure.

 

In part 3, i have discussed:

1. Provisioning model of SQL Azure

2. Billing Model of SQL Azure

read more: Getting started with SQL Azure – Part 3 << Paras Doshi

 

just to recap:

In part 2, i have discussed:

1) How to sign up for a Free trial of windows azure (to play with SQL Azure)!

2) How to create your very first SQL Azure database (and a table too!)

3) How to connect to SQL Azure server Via SQL server Management studio.

Read more: Getting started with SQL Azure – Part 2 << Paras Doshi

 

And in part 1, i have discussed

1) Where SQL Azure fits in the windows azure platform?

2) Defined SQL Azure

3) Advantages of SQL Azure

Read More: Getting started with SQL Azure – Part 1 << Paras Doshi