Click here to monitor SSC
  • Av rating:
  • Total votes: 388
  • Total comments: 42
Andy Brown

Moving Data From Excel to SQL Server - 10 Steps to Follow

21 May 2013

SQL Server Integration Services provide a versatile way of reading Excel files into SQL Server. A task like this illustrates the advantages of the graphical approach of SSIS. Andy Brown explains.

You need to create a package to import data from an Excel workbook into a SQL Server table, using SQL Server Integration Services as a tool. What could possibly go wrong? Well … plenty, as it turns out. The aim of this article is to help people to avoid all the pitfalls that I fell into when first learning SSIS.

This article uses SSIS 2012, the latest version at the time of writing, but you won’t notice many differences if you’re using 2008 or 2005. The article assumes that you’re using SQL Server Data Tools – Business Intelligence within Visual Studio 2012 to create SSIS packages: Business Intelligence Development Studio (BIDS) was used until SQL Server 2012. Do Microsoft have a whole department devoted to thinking up misleading names for software?

Our Simple Example

Let’s suppose that you want to import an Excel workbook of purchases into a SQL Server table (you can download this workbook here):

Purchase ledger workbook

Our purchase ledger workbook, complete with2 unwanted title rows, a useful heading row and a couple of awkward blank rows at the bottom.

You could use Excel to manually delete the top two title rows and bottom two blank rows to make life easier, but this would be kind of cheating (and also kind of pointless, since whatever application produced the Excel workbook of purchases would just recreate the unwanted rows next time you ran it). To quote the words of Caiaphas in the musical Jesus Christ Superstar: “we need a more permanent solution to our problem”.

What we want to transfer to the table

Here’s what we’d like our final SQL Server table to contain: the 5 purchases.

Before you begin, make sure that you’ve closed your Excel workbook down. If you run any SSIS package to import data from an Excel workbook which is open, you will get some horribly misleading error messages.

Step 1 – Create a Project

Before you can play about with data (sorry: extract, transform and load it), you need a project to do it in. Go into SQL Server Data Tools or Visual Studio, then choose to create a new project:

Creating a new project

You may be able to miss out this step if you’ve just gone into SSIS for the first time.

At the top of the dialog box which appears, make sure you create the right sort of project:

Selecting the type of project

Choose to create a business intelligence SSIS project.

You can then give your project an exciting name (at least, more exciting than Integration Services Project1, which is what I’ve used!):

Nanme the project

Choose a name and location for your new package.

SSIS will now create a new project, and also (by default) a new package too, imaginatively called Package.dtsx. There are two ways you can see that this is what’s happened. One is that you can see the package in Solution Explorer:

The new package

The new package created on your behalf (if you can’t see Solution Explorer, choose‘View SolutionExplorer’ from the menuto show it).

The other clue that SSIS has created a package for you is that it’s staring you in the face!

Control Flow is the default view

By default you are put in Control Flow view, which is like a flow diagram showing the sequence in which tasks that you create will execute.

Step 2 – Create a Connection to your SQL Server Database

Before you continue, you need to make sure that you’ve created a connection to your SQL Server database. To do this, first right-click on the ‘Connection Managerspart of Solution Explorer:

Create a new connection manager

Right-click to create a new connection manager.

The most efficient way to link to SQL Server is using an OLEDB connection manager:

Select an OLE DB Manager

Choose to add an OLEDB connection manager.

Now click on the ‘New… button to create your new connection manager:

Click 'New' to create the connection manager

Create a new connection manager as above.

Choose your server, authentication method and database on the next screen, then select ‘OK’ twice to see your new connection manager listed:

The new connection manager should be listed

It makes sense to create this connection manager for the entire project, since it’s likely you’ll use the same connection in other packages within the same project.

I haven’t shown any more details about this here for two reasons: the settings will be different on your machine, and anyone reading this article is likely to have created connections many other times in many other software applications!

Step 3 – Create a Table

You can’t import data into a non-existent table, so the next thing we’ll do is to create the table shown below. We could do this manually within SQL Server Management Studio, but we’re aiming for an automated solution which we can run time and time again, so instead we’ll create the table as part of our SSIS package.

The new table

Our table will look something like this: we’ll import the item name, price and quantity, but the purchase id will be generated automatically. As for the total in column E of our spreadsheet – we’ll just choose not to import that, since it can be recreated by multiplying the Price and Quantity columns at any time.

To create the table, first double-click (or click and drag) on the ‘Execute SQL’ task to add a task to the control flow which should be visible on screen (we want to create the shell table within this task):

Select 'Ececute SQL Task'

This task will run some SQL to remove any existing purchases table, and create a new one.

I tend to give my tasks long, descriptive names (geeks may prefer to use shorter meaningless names!):

The taks has been renamed

The Execute SQL task after renaming it.

You can also add sort-of-comments to packages using something called annotations:

Adding an annotation

You can right-click to add an annotation to your package – they appear like post-it notes:

How annotations appear

Anyway, returning to the main story, you can now edit your Execute SQL task:

Editing the Execute SQL Task

The easiest way to edit any SSIS task is to double-click on its icon, although you can also right-click on the task and choose ‘Edit… as above.

In the dialog box which appears, choose to connect to your database, using the connection manager that you’ve just created at project level:

Choose your connection manager for the project

You can use a project-level connection manager in any package.

You can now enter the SQLStatement property, specifying the SQL that SSIS should run for this task. Here’s what I’ve used for this article.

IF EXISTS (SELECT 1 FROM information_schema.tables where table_name like 'tblPurchase')
    DROP TABLE tblPurchase
-- create a table to hold purchase ledger items
CREATE TABLE tblPurchase(
	PurchaseId int PRIMARY KEY IDENTITY(1,1),
	ItemName varchar(50),
	Price float,
	Quantity int

This will first delete any table called tblPurchase which already exists, and then create a new, empty one. The PurchaseId column is an identity one, which will automatically take the values 1, 2, 3, etc. Here’s what the Excecute SQL task dialog box now looks like:

The Execute SQL dialogue box

The SQLStatement property as it appears after you paste in the text.

It’s time now to test that this works by running your single-task package:

Run the package

Right-click on the package name in Solution Explorer and choose to execute it as shown here. SSIS will save your package automatically before executing it.

If all goes well, you should see this:

The tick shows that the package has run

The green tick means things went well!

If your package doesn’t run at this point, you may be trying to run it on a 64-bit computer. The default mode in SSIS on a 64-bit SQL Server installation is 64-bit. In this case, you have to specifically change the mode to run a package. I don’t want to clutter this article up with an explanation of how to do this so please refer to this article for how to do this.

You should now have a table, which you can view in SQL Server Management Studio if you should so wish:

There are no records in the table

The table has no records in – yet!

You now need to stop the package running:

Stop debugging

Select the menu option (or press the keystroke above) to stop your package running, and wave goodbye to the green ticks for now!

It’s now time to create the data flow tasks - although first we need to create an Excel connection.

Step 4 – Create an Excel Connection

Before you can import data from an Excel workbook, you will need to create a connection to it. You should probably create this connection within your package, as it’s likely that this’ll be a one-off (you won’t need to use the same connection in any other package):

Create a new connection

Right-click in the ‘Connection Managers section of your package, and choose to create a new connection.

Note that you could alternatively use the ‘Source Assistant to do this, but I always like to do things explicitly:

The Source Assistant Wizard

The source assistant as it appears in the SSIS toolbox – it’s not the Voldemort of wizards, but it’s not the Dumbledore either.

You can now choose to create an Excel connection:

Choosing an Excel Connection

There are a few other choices!

Browse to your Excel workbook and choose it:

Select your workbook

Leave theFirst row has column namesoption ticked.

When you select ‘OK’, you should see your Excel connection:

The connection should now be visible

You could rename this connection manager, but we’ll leave it as it is.

Step 5 – Create a Data Flow Task

It’s time now to start the real work! We want to add a data flow task to the control flow tab of your package. This new data flow task should import data from the Excel workbook into the SQL Server table (although as we’ll see, things can go awry at this stage).

Selecting a Data Flow Task

Add a ‘Data Flow task to your package, and rename it to say ‘Import data (as above).

You now need to get the two tasks shown to run in sequence; you’ll firstly want to create a new table to hold your purchases, and then import the data into it. To do this, click on the first task and drag its arrow onto the second. This arrow is called a precedence constraint.

Connecting the tasks

Connecting the tasks, so they follow on from each other.

You can now double-click on the data flow task to edit what it does – we’ll spend the rest of this article in the data flow tab of SSIS:

The Data Flow tab is currently empty

The (as yet empty) Data Flow tab for the Import data task.

Step 6 – Creating the Excel Source

Data has to come from somewhere, and in our case it’s from Excel:

Select Excel as the data source

Drag an Excel Source from the SSIS toolbox onto your empty data flow window (here we’ve also then renamed it).

You can now double-click on this source to edit it, and tell SSIS where it should get its data from:

You can now specify the data source

SSIS will automatically guess the connection manager if you only have one Excel connection manager for this package/project, but you’ll still need to choose the worksheet name (as shown above).

It’s a good idea now to preview your data, by clicking on the ‘Preview…button:

The preview of the table

We’ve got obvious problems with our first 2 and last 2 rows, but we can solve these by losing any rows for which the first column is null, which we’ll do shortly using a conditional split transform.

It’s a good idea now to rename all of the columns, so that you know what they refer to:

Renaming the columns

Click on the ‘Columns’ tab (as shown above), then give the output columns better names, as we’ve done here.

When you select ‘OK’, you should have an Excel source with no errors shown for it:

The Excel workbook with no errors

Now to do something with this data!

Step 7 – Removing Rubbish Data

The next thing we want to do is to divert all of the purchases with nulls in to… nowhere, really! To do this, add a conditional split transform to your data flow:

Add a conditional split to the data flow

Add a Conditional Split as above (here we’ve renamed it also, to lose the nulls), and direct the output (or “data flow path”, if you want the technically correct name) from the Excel course into it.

You can now double-click on the ‘Conditional Split task to configure it. We’ll set up two flows out of it:

Data where the id column is null will go down a pipe called ‘Case 1 (which we won’t actually connect to anything); while

All other data will flow down a pipe called ‘OK Data’.

Here’s how to set this up:

How to configure this

Set up an output (called ‘Case 1 by default) which tests the condition that the Id column is null. You can drag the ISNULL function and Id column down into the ‘Condition box to avoid having to type them in.

At the bottom of this dialog box you can type in a name for your default output:

Naming the output

Here we’ve called the default output ‘OK Data’.

Step 8 – Piping the ‘OK Data’ into a SQL Server Table

We should be getting near the end of our journey now – all that we should need to do is to send the good data into our purchases table. Here’s how to do this:

Add an OLE DB Destination

Add an OLE DB destination (as shown above) – here we’ve renamed ours asPurchases table.

You can now drag the green arrow from the ‘Lose the nulls transform into the Purchases table destination:

Connecting the transform to the table destination

When you release the arrow, you’ll be asked which output you’re choosing: ‘Case 1 or ‘OK Data’ (the two outputs from the conditional split). Choose ‘OK Data’.

Having mapped data into the purchases table, it’s now time to configure it. Double-click on the Purchases tabledestination to edit it:

Editing the 'Purchases table' destination

Firstly, choose the connection manager to use (although you probably won’t have to do this, as SSIS will assign it automatically if you’ve only got the one), and the table to target.

You can now choose which columns from Excel to map onto which columns in the SQL Server table:

Specifying the column mapping

Be warned – the Item column will cause a problem soon… !

Here’s what you’ll be left looking at when you choose OK:

The Purchases table is displaying errors

There’s a problem with the ‘Purchases tabledestination.

If you mouse over the red circle, you’ll see what the problem is:

Highlighting the warning displays the issue

The problem is that Excel uses Unicode data, and we’ve created a varchar column in SQL Server.

Step 9 – Identifying and Solving the Data Conversion Problem

When you’re creating columns in SQL Server, you can use either nvarchar or varchar for variable length strings:

Selecting the string type

The varchar data type uses half the amount of bytes that nvarchar uses, because it can’t store extended characters. We could have used nvarchar and avoided this problem!

The benefit of using Unicode is that it allows you to store international characters: currently over 110,000 different characters from over 100 scripts, according to Wikipedia.

However, we used varchar, so we need to convert our Excel Unicode characters into normal ones. To do this we can use a ‘data conversion task. First, however, we need to break the link we’ve created:

Breaking the existing link

Right-click on the link between the transformation and the destination and delete it.

You can now add in a ‘data conversion task:

Adding a data conversion task

Here we’ve added a ‘Data Conversion task (shown selected on the left), and renamed it to ‘Turn Unicode into varchar. The next thing is to pipe our data into it:

Mapping the data flow again

Pipe the ‘OK Data’ from the conditional split transform into this further data conversion transform.

You can now double-click on the ‘Turn Unicode into varchar data conversion task, and say what it should do:

Converting the data from Unicode to VARCHAR

Here we’ve chosen to create a new column called ItemVarchar, which takes the Item column and turns it into a non-Unicode string using the default ANSI code page.

I’ve also changed the length to 50 characters at this point. This will mean that strings longer than 50 characters will be truncated, giving rise to truncation errors. Dealing with these is beyond the scope of this article – for now it’s sufficient to note that none of the purchase descriptions is long enough for our example to cause us any worries.

Nearly there! You can now take the output from this data conversion task and feed it into the Purchases table destination:

Another error, as the column mappings aren't redone

We’ve still got an error, as we haven’t redone the column mappings for the destination.

You can now double-click on the Purchases table destination to configure the column mappings:

Configuring the column mappings

Choose to map the newly derived ItemVarchar column onto the ItemName column in the SQL Server table.

All of your errors should now have disappeared, and you can run your package!

Step 10 – Running the Package

The final step is to import your data by executing the package:

Execute the final package

Right-click on the package in Solution Explorer to execute it (wish we’d renamed it …).

Here’s what the data flow should look like:

The data flow

Yeah! It’s four ticks from SSIS!

You should now have 5 purchases in your tblPurchase table:

The SQL table with all data mapped from Excel

OK, it would have been quicker to type them in on this occasion, but you’ve now got a package which you can run every month-end, and which will work whether there are 5 purchases or 500,000.


Integration Services is just one of those software applications which is a joy to use. I hope this has encouraged you to use it to automate moving data around in your company. There’s nothing quite so satisfying as seeing the green ticks appear next to all of the tasks in your packages when you run them!

Andy Brown

Author profile:

Andy works as a trainer and consultant for Wise Owl Training, providing SQL Server, .NET, Visual Basic and (reluctantly!) C# training for businesses in the UK.

Search for other articles by Andy Brown

Rate this article:   Avg rating: from a total of 388 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: Moving data from excel to excel server
Posted by: Pragyan (view profile)
Posted on: Monday, May 27, 2013 at 4:50 PM
Message: Excellent article Andy. Thank you for the simple steps.

Do you have some article how to import multiple tabs from xlsx into SQL server ?

Subject: Really nice
Posted by: sbodevguru (view profile)
Posted on: Tuesday, May 28, 2013 at 3:29 AM
Message: Great article Andy, wish it was written a few weeks ago when I was literally tearing my hair out with a task identical to this. Any chance of a follow-up showing how to deal with error outputs? It's one area I just cannot get my head around - I would love a nice simple way to produce a clean, easy to read list of human-readable errror for each step in the process...

Subject: Exactly what we need
Posted by: Rupertsland (not signed in)
Posted on: Tuesday, May 28, 2013 at 8:31 AM
Message: Thanks Andy for your article. It was very helpful. I am a novice-intermediate SQL Server administrator for an environmental consulting company in Canada. I'm am confronted with the task of uploading biological/ecological data from many hundreds of Excel spreadsheets into a SQL Server database. SSIS was always a mystery to me. I have been using the import/export wizard to upload data, but this step came only after going through Excel worksheet tabs and stripping out all formatting - a tedious, time consuming task, riddle with many problems. Your solution is better. How can I upload multiple worksheets within an Excel file? Can they be uploaded in a certain sequence?

Subject: Nice Article
Posted by: Anonymous (not signed in)
Posted on: Tuesday, May 28, 2013 at 11:51 AM
Message: Thanks for the article Andy. A couple of additional items I would like to see are handling changing worksheet names eg jun 2013 , 06/2013 and also an easy add in container for file renaming\archiving and deletion of files older than x days. I have done this in a short dos script as I am not clear how to handle this easily in SSIS without a lot of variables that need to be added to the package

Subject: Exactly what we need
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 29, 2013 at 4:23 AM
Message: Rupertsland: Yse you can upload multiple Excel sheet within and across many work books. You will need to use ForEachLoop in SSIS to navigate from one workbook to the other. I can give you a demo if you want but there are many examples you can pick from that are online.

Subject: process fro .csv files
Posted by: Anonymous (not signed in)
Posted on: Saturday, June 1, 2013 at 7:12 PM
Message: Hi, i hav egot atask to import the .csv files into one of the existing table in a database. could you please help me with this.

Subject: Various answers
Posted by: AndyOwl (view profile)
Posted on: Tuesday, June 11, 2013 at 7:43 AM
Message: Sorry about the delay in replying to any comments - I hadn't actually realised that the article was live! I may write a follow-up article on dealing with error outputs some day, but it's a big topic. To import multiple tabs the easiest way I can think of would be to put each worksheet in a separate workbook, then write a Foreach loop in SSIS as someone recommends to import each one. This may appear here as a future article! Thanks for all the kind comments.

Subject: info missing
Posted by: Koen Verbeeck (not signed in)
Posted on: Wednesday, June 12, 2013 at 11:45 PM
Message: Hey Andy, nice tutorial, but there's content missing. For example: what about varying data types in one column. In that case you need to set IMEX=1 in the connection string and possibly change the TypeGuessRow setting in the registry to alter the behaviour of the ACE/JET OLE DB provider.

A simpler option to deal with the missing column names and the NULL values is to write a SQL query to the excel file, instead of just importing everything.

Subject: Why doesn't Excel play nicely?!
Posted by: CGSJohnson (view profile)
Posted on: Thursday, June 13, 2013 at 1:25 AM
Message: To Koen's point, why do we have to add registry hacks, in order to get Excel to play nicely with SQL Server?! It can be REALLY frustrating at times.

I have found that formatting the input MS Excel usually works too -- to avoid the truncation of characters in columns.

Subject: Mixed data types
Posted by: Anonymous (not signed in)
Posted on: Thursday, June 13, 2013 at 3:17 AM
Message: How about Mixed data types
Is this still a problem with SSIS 2012?


Subject: Validation Validation Validation
Posted by: R.Briggs (view profile)
Posted on: Thursday, June 13, 2013 at 4:40 AM
Message: You might like to try Excel Database Tasks (EDT) - this does include a fair number of Client and Server validation options (including automated transaction handling) - and allows you send data to table or stored procedure.

The aplication will accept pasted data - or you can configure the data source tab to import from an Excel file - or any other data source in fact.

I think you will find EDT very interesting:

Here you can find out allot more:


Subject: Validation Validation Validation
Posted by: R.Briggs (view profile)
Posted on: Thursday, June 13, 2013 at 5:20 AM
Message: You might like to try Excel Database Tasks (EDT) - this does include a fair number of Client and Server validation options (including automated transaction handling) - and allows you send data to table or stored procedure.

The aplication will accept pasted data - or you can configure the data source tab to import from an Excel file - or any other data source in fact.

I think you will find EDT very interesting:

Here you can find out allot more:


Subject: Another option for moving data from Excel to Sql
Posted by: Dan holmes (not signed in)
Posted on: Thursday, June 13, 2013 at 6:01 AM
Message: Depending on the file size and other requirements/limitations this Powershell solution may work.

Subject: SQL SSMS GUI Shortcut to config of SSIS package
Posted by: Ness (view profile)
Posted on: Thursday, June 13, 2013 at 6:33 AM
Message: You could side step a lot of the configuration here if you use the GUI for the SQL management Studio (SSMS) to create the basic package via the import/export functionality and then save it as a SSIS package (last option before you run (potentially) the import/export). Then open the package and edit it afterwards.

Subject: Enjoyed the humor...
Posted by: Mikus (not signed in)
Posted on: Thursday, June 13, 2013 at 6:59 AM
Message: Made it not such a dry read. Thanks!

Subject: Creating a database in sql 2008 R2
Posted by: Anonymous (not signed in)
Posted on: Thursday, June 13, 2013 at 7:26 AM
Message: How best can I create a DB in SQL 2008 R2 without going through the scripting method?

Subject: Validation
Posted by: Ramon Lopez (not signed in)
Posted on: Thursday, June 13, 2013 at 7:38 AM
Message: Excellent article Andy.
What's the procedure in order to this package can run automatically each hour or a daily basis
Thanks in advance

Subject: Thanks for comments
Posted by: AndyOwl (view profile)
Posted on: Friday, June 14, 2013 at 2:34 PM
Message: Thanks for all the comments. The scope of the article was deliberately limited to being a more-or-less introductory tutorial to SSIS, to get people over early hurdles (the same ones I experienced when learning the product).

As to the humour - I try! Some of the more extreme jokes got lost in the edit ...

Subject: Excel Connection Manager Error
Posted by: Pooja (view profile)
Posted on: Tuesday, June 25, 2013 at 7:00 PM
Message: This is exactly what I was looking for. I followed the steps but encountered the following error when trying to create an excel source - "Could not retrieve the table information for the connection manager 'excel connection manager'". I looked around and found that the Run64BitRuntime property has to be set to false. It's already set to false. I am using VS 2012 on Windows 8. Can you point out what am I doing wrong?

Subject: Excel connection manager error
Posted by: AndyOwl (view profile)
Posted on: Wednesday, June 26, 2013 at 2:26 AM
Message: To be honest, I don't know! I'd recommend, who should be able to help.

Subject: how to give excel file path at runtime and to edit mapping columns
Posted by: Dave_S (view profile)
Posted on: Monday, July 29, 2013 at 4:56 AM
Message: Hi Andy,
Really thank you for such great works ..i am new to SSIS,, would like to ask how we can give excel file path and to edit columns mapping at run-time as every time new data received and some times needs to
profile data (mapping is required to append current files with the old one) ///

Subject: How to access Excel Files that coming from different Server
Posted by: atetter (view profile)
Posted on: Friday, September 6, 2013 at 2:52 PM
Message: Hi Andy,
This is a good stuff for us just learning SSIS. I have question if it's possible.

I have an excel files that will serve as input source that is located from different server. The SSIS package is located in my machine. The path where the excel files are located is in \\SharePointSever\sites\test\ExcelFiles\test.xls when I use this path in my excel connection manager it's giving me an error cannot read.

The package works if the excel file is located on my machine same location of the package. What is the best approach to do this if the Excel file is located from different server?

I am newbie in SSIS, a help will much be appreciated.

Subject: Linking to Excel workbook on different server
Posted by: AndyOwl (view profile)
Posted on: Saturday, September 7, 2013 at 2:43 AM
Message: The short answer, aletter, is that I don't know! Just tried Googling various possibilities, and can't find the answer either. One obvious idea would be to write a file task to move or copy the Excel workbook to the current server as a preliminary control flow task.

Subject: how add a form to this cycle
Posted by: The Coder (view profile)
Posted on: Monday, January 27, 2014 at 4:14 AM
Message: hi ,

excellent article but i wondering how can i add a windows form to this article to select a different excel time (browse) a new file to check evrey time

Subject: how to install bussiness intellgience templates
Posted by: The Coder (view profile)
Posted on: Monday, January 27, 2014 at 7:00 AM
Message: how to install bussiness intellgience templates

i have vs 2010 & vs 2013
sql server 2012 & 2010

all are installed just guide me with a link of download

Subject: Excel files and BI templates
Posted by: AndyOwl (view profile)
Posted on: Monday, January 27, 2014 at 11:57 AM
Message: Hi

Not sure that you can select a different Excel file each time you run the package with a popup window, but you could certainly edit the Excel connection to choose a different file.

As to installing the BI templates, not sure, I'm afraid - way beyond the scope of this article! You could try

Subject: THanks
Posted by: The Coder (view profile)
Posted on: Tuesday, January 28, 2014 at 6:02 AM
Message: Thanks alot andy

ok can you help me on this

Subject: Selecting File for Import
Posted by: R.Briggs (view profile)
Posted on: Wednesday, January 29, 2014 at 6:34 AM
Message: If you are interested in a tool to do this, here is an example allowing 'Select an Excel file for import' when running the task:

Subject: Great and Useful Article
Posted by: JoeWolfe (view profile)
Posted on: Friday, March 7, 2014 at 10:47 AM
Message: I ran in to a problem with the worksheet name PlItem$. It gave an error. If anybody else gets this just rename the worksheet. I used Sheet1 and it worked fine. You wouldn't have an article on going the other way, i.e. SQL to SQL would you?

Subject: Useful Article
Posted by: Niranjan0390 (view profile)
Posted on: Tuesday, December 9, 2014 at 3:25 AM
Message: Hi its really useful to me and I also want to know the reverse. is. Moving Data from SQL Server to Excel in SSIS

Subject: Usefull and Easy
Posted by: (view profile)
Posted on: Monday, January 5, 2015 at 12:32 AM
Message: This article saved my time and life.
Easy to understand and apply with pictures.
Thank you for your share and work.

Subject: Excel souce data preview issuse
Posted by: srinua (view profile)
Posted on: Friday, February 6, 2015 at 11:54 AM
Message: Hi ,I try to implement same way,but i strucked on excel source data preview time,I got 2nd row data(#used in simple-talk example remains along with null column and 3 rd row shows header columns). what ever you provide excel source data preview time you did not get any excel columns like id,item,sal collumns.can you please provide package with excel document.its very helpfull.please tell me i strucked and how i solved this issue.

Subject: Excel source data not work when preview time
Posted by: srinua (view profile)
Posted on: Friday, February 6, 2015 at 10:58 PM
Message: Hi Excel source data look like below
#Location Query
#Code: ( \ EMPTY)
Code Name
CT001 Buckingham Estates
CT002 Emeritus at Woodbridge
CT003 The Greens at Cannondale
CT004 Emeritus at Litchfield Hills - ALF
CT005 The Hearth at Tuxis Pond
I try to skip first 2 line to use same concept.but mine not work.please tell me how to achive this issue in ssis to import excel data into sql table using ssis

Subject: Excel source data not work when preview time
Posted by: srinua (view profile)
Posted on: Saturday, February 7, 2015 at 12:01 AM
Message: Hi Excel source data look like below
#Location Query
#Code: ( \ EMPTY)
Code Name
CT001 Buckingham Estates
CT002 Emeritus at Woodbridge
CT003 The Greens at Cannondale
CT004 Emeritus at Litchfield Hills - ALF
CT005 The Hearth at Tuxis Pond
I try to skip first 2 line to use same concept.but mine not work.please tell me how to achive this issue in ssis to import excel data into sql table using ssis

Subject: What is the errror?
Posted by: (view profile)
Posted on: Saturday, February 7, 2015 at 12:38 AM
Message: Can you give more detail about your issue?
Where is the error?
What is the error?

Subject: Excel Connection Manager
Posted by: (view profile)
Posted on: Saturday, February 7, 2015 at 1:05 AM
Message: Please uncheck "first row has column names" in excel connection manager or make first row column names.

Subject: Excel Connection Manager
Posted by: (view profile)
Posted on: Saturday, February 7, 2015 at 1:05 AM
Message: Please uncheck "first row has column names" in excel connection manager or make first row column names.

Subject: SSIS package deployment
Posted by: Joblackk23 (view profile)
Posted on: Friday, February 13, 2015 at 10:42 AM
Message: Hi Andy,

Great article. I have a a few end users that i'd like to create custom excel reports to linkup to SQL. These users are in no way SQL or SSIS inclined to execute or use any of these tools and so I was curious if the SSIS package can be deployed without installing VS on each machine i need to link excel to SQL.

Any help would be greatly appreciated.

Subject: Excel Reports
Posted by: R.Briggs (view profile)
Posted on: Friday, February 13, 2015 at 12:01 PM
Message: Hi Jo Black

you might like to consider EDT for multi user Excel parameterised reporting.

You can create Excel reports from most database types - including grouping and task level print options.

EDT of course also enables you to create data maintenance and data transfer tasks too.

Cool thing is EDT runs from a shared network folder without having to maintain any spreadsheets at all.


Subject: Spot on!
Posted by: mark_zudeck (view profile)
Posted on: Friday, April 29, 2016 at 12:55 PM
Message: Great course, Andy!
Every detail is covered.
I feel like an SSIS guru now.

Subject: SSIS--Urgent
Posted by: nitika2509 (view profile)
Posted on: Wednesday, June 1, 2016 at 11:44 PM
Message: Hello Sir,

I want to load data into Tabular model throu SSIS.
Is it possible?
if possible plz give me example.Its very urgent

Subject: Very good thanks
Posted by: Dominic Houlbrooke-Bowers (view profile)
Posted on: Thursday, June 2, 2016 at 8:44 AM
Message: I had not used and ssis package since a 2005 so a good refresher and worked first time. Thanks

Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
How to Build and Deploy a Database from Object-Level Source in a VCS

It is easy for someone who is developing a database to shrug and say 'if only my budget would extend to buying fancy... Read more...

 View the blog

Top Rated

Clone, Sweet Clone: Database Provisioning Made Easy?
 One of the difficulties of designing a completely different type of development tool such as SQL Clone... Read more...

Database Lifecycle Management: Deployment and Release
 So often, the unexpected delays in delivering database code are more likely to happen after the... Read more...

SQL Server Security Audit Basics
 SQL Server Server Audit has grown in functionality over the years but it can be tricky to maintain and... Read more...

The PoSh DBA: Assigning Data to Variables Via PowerShell Common Parameters
 Sometimes, it is the small improvements in a language that can make a real difference. PowerShell is... Read more...

Issue Tracking for Databases
 Any database development project will be hard to manage without a system for reporting bugs in the... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.