How to Export Table and Query Data to an Excel 2010/2013 File Using SSIS 2012

This video is part of LearnItFirst's SQL Server 2012: A Comprehensive Introduction course. More information on this video and course is available here:

http://www.learnitfirst.com/Course170

Now that we have installed our instance of SQL Server Integration Services 2012, we will play around with it and explore some of its features. We will also cover some key terms, as well as the concepts of packages and tasks.

Highlights from this video:

- What is an SSIS "package" and what does it contain?
- What is a task and what different things can it do?
- What different ways can you create an SSIS package?
- The importance and significance of the Import and Export Wizard
- Is there a difference between the 32 and 64-bit Import and Export Wizard?

and much more...

---

Closed Caption:

so let's actually play with integration
services now we have it installed we're
all ready to go
let's actually start working with it we
have a couple of terms that we need to
come to grips with here first
pink so we create and integration
services package
ok a package is you know somewhat akin
to an executable it contains which
contains one or more tasks these are
integration services tasks tasks are the
functionality of integration services
there are things like the ftp task that
allows you to upload or download files
there is the data flow task that allows
you to move data from a source to a
destination we have the sendmail task
which you can imagine it makes a little
envelope put a stamp on it
no well you get it sends any medical
right uh-huh so the package creates
tasks and we also have solutions which
can contain multiple packages and
there's a few other kind of
behind-the-scenes things that maybe a
couple videos now from we will see more
real-world here but for now it's good
enough to know that an ssis package is
your execution . okay so we execute a
package that's what we the developers
the admins want to have happen the
package then calls the tasks there is
logic within the package if you code it
this way that says hey if this then that
so if it's between 2am and 4pm then go
ahead and use this ftp task if it's
outside of that range use this data flow
task
ok programming logic ok it's a very
graphic GUI interface that is really
easy will play without here in a little
bit here
ok so you have your terms excuse me
package and tasks those are our basic
terms now I think that the logical place
to start
working with integration services here
is going to be in the management studio
I'm not because this is really where we
develop packages
ok so we develop packages in a couple of
different ways either through a wizard
which is what i'm about to show you here
in the next to this video in the next
video or through sequel server data
tools SSD t will do that in a couple
more videos like three videos 23 videos
from now and so SMS though management
studio has a couple of different cool
things i want to show you so let me go
ahead and launch this and one of the
first things that I want to show you is
that notice that the for server types
are listed that you can actually connect
up to your integration services instance
well this is cool and this is great from
a management standpoint this is not
where we will be designing packages
creating packages editing packages
making significant changes to the
package logic flow changing the pipeline
changing how a particular package
responds to the environment
ok so this is management studio and
that's what we're going to be doing
we're going to be issuing management
command we're going to be saying execute
this package schedule this package
design security to this package
ok so let's actually not play with the
integration services yet because we
don't have any packages
let's create a package in the management
studio and so I'm going to go to the
database engine and I'm going to connect
up and check this out i can go over to
my database and let's say bosses ask me
says hey Scott can you give me an Excel
spreadsheet that lists all of our
courses we have that information it's
inside this course table sorry
see it's right inside here and if we
were to run a query you would see in all
right so here's what I can actually do i
can right click on the database go to
tasks and I can go to export data these
are our integration services
ok so
the effect of running these these
launched two wizards are really it's one
wizard it's called the import and export
wizard I think they just used both
commands from a usability standpoint
what if somebody's looking for export
what if somebody's looking for import
and so these create an ssis package
ok that contains one or more tasks and
now there's going to be a slight gotcha
here right let me show you something
here
um let's go through go to tasks and i'm
going to export data and it doesn't
really matter which of these I choose
because they both launch the import and
export wizard going same thing again is
joe i think they chose that just from a
usability standpoint so I say next
choose your data source so let's get a
couple of terms here the data source is
really critical remember that you are
going to extract from the source
ok you are then going to transform and
then you were going to load the
destination
ok so this is etl we're starting this
process so we choose this datasource up
here and this is that where we want to
extract from ok so i say Native Client
this is sequel server 2012 que native
client 10.5 sequel server 2008r2 to
Native Client 10 . o sequel server 2008
and so forth
ok those are the actual version numbers
okay you are using a client to connect
here
ok so we call it the Native Client here
however what can we use a data source
are we required to use sequel server as
a data source know you remember all
those icons we talk about db2 mysql
visual foxpro and all that stuff you can
use almost any data source if you can
get a driver or a provider that will
tell integration services how to make
the connection
you can use something as a data source
now we happen to be looking at the
default installation of integration
services here so these are the default
settings we can go with a flat file
ok we'll talk about flat files in the
next video actually we can use microsoft
access as a data source we can use excel
we can use analysis services see the
10-point oh and 11 . o showing you the
different versions there or call
microsoft sequel server and there are
many many more just because you don't
see it and default that does not mean
it's unavailable to you you just would
have to go and install the driver or the
provider for that source and so we're
going to stick with the Native Client
and so now it's asking me which server
you want to connect to this is the
server name and instance name if you're
using multiple instances
ok so i'm not using multiple instances
this actually maps up with win7 here so
I'm going to connect my data source is
going to be this server
ok my database engine ok how do i
connect it's going to make an in-process
connection here is going to pass my
credentials using windows authentication
if i go choose windows authentication
here if I want to choose sequel server
authentication maybe I want to have this
connection be made in another security
context i could do that as well it's
automatically chosen this database
because i right clicked on this database
hear me but I can choose any of the
other databases that are on this server
i say next
where's my destination hey scroll down
notice that we can make the same choices
we made for the sources here
ok so let's say I want to export to
excel
where do you want to put it okay i can
just type it in if I want to I can say C
colon my excel file dot XLS or I can
click the Browse and go choose where it
is that I want to and but i'll choose
this if I want I
header row in my excel spreadsheet i'm
going to check the box that the first
row has column names now i am leaving
this default alone notice that it
defaults to at this point the older
versions of Excel and xls file
ok i'm leaving that right now I'm okay
with that and I say next
what is it that I want to copy ok so now
we have to define what we want to
extract we've defined the source and the
destination but we now have to define
what it is we want to extract and we can
copy all of a Tables data or we can
write a query and we could write joins
and write very complex queries or very
simple queries
okay i'm going to tell it copy the data
thing and we can choose from tables or
views so it's going to get all of the
data in a table or the entire resultset
from the view and I say next and which
table do i want i want the core stable
so what it's telling me down here you
see the little kind of trying to make it
shiny excel spreadsheet there it's
telling me it's going to create a new
worksheet called course in this excel
file
ok so there will be a a worksheet called
course and now you can click the Edit
mappings over here and and you can see
the data types that is going to map so
it has the source column these are from
my sequel server and this is what it's
going to create because i have chosen to
create that table
ok so it's going to create one called
course ID if I want to come play around
down here i can change this
okay i can change title to course title
ok and i can change data types if i
choose to get the table exist i can drop
it and if i click the Edit SQL this is
the jet SQL statement that it will issue
to excel and see it's creating a column
in the worksheet called course title and
so I don't need to edit the sequel but
we did modify and change the column and
you can hit a preview button to see what
it's going to do and say next
now review your type mapping we have a
datetime issue here okay and it says hey
make sure that you're going to have some
issues you have a little bit of an issue
here with a type conversion so you're
converting from datetime 224 car for
char okay do you want to convert yes we
want to actually convert and you can
actually can't play around with these
and see the errors if you actually
double type here
okay see the conversion information now
when we say next we have two choices
it's going to create a temporary package
and then we can run that package and it
will perform this data flow task that
we've just asked it to do because that's
behind the scenes what we've set up
we've made two connections a source and
destination and we've drawn we've made a
data flow task that says copy the data
from the source to the destination and
if we want to we can save this package
and we can save it in the sequel server
and we can choose to encrypt passwords
and different connection information in
here or we can say you know what don't
store the connection information that we
just added in the wizard you know when
we typed in we didn't really do it but
we could have tried type in the sequel
server login name and password
what does it store that information when
it stores this package
ok so we'll save this package and i'll
show you what that looks like here I say
next give this a name export courses to
excel spreadsheet and you can give it a
description if you want and you learn it
first job database 170 . do . course you
can add in right there export if you
want this just an optional step where is
it going to be stored this is your
integration services here we're going to
store it in the sequel server going and
so where I windows authentication
I say next and it's going to do two
things is going to save the package and
run the package and we say finish lots
of cool steps there we go
12 rows were transferred you can click
on here and get nothing really you can
click on a report here if you need to
we don't need to for this one now we
have an affect our actual database
itself but we can go down and look on
the c drive and see the excel file here
and double click on it and hey look at
their it sure did and it brought it out
as course title and notice down here we
have a spreadsheet called course so we
just exported from sequel server to
excel
ok now there's one gotcha I kind of
alluded to this a little bit later and
let me go ahead and come back over here
we kind of went the long way with this
didn't we we right-click we said tasks
we said export data and we chose this
but when we chose our destination we
chose excel 2003 ok so what if i go over
here and I say see my excel file dot XLS
x xlsx if you remember was the new file
format for excel 2007 and forward so i
choose Microsoft Excel 2007
ok here's a big gotcha you will get an
error
ok and the error it says the operation
cannot be completed the microsoft aceo
lady b 12 . provider and it will happen
to you it will happen to everybody that
you know that ever launches this menu
because nobody wants to say i want to go
back and use Excel 2003 SMS let's just
write this down here and management
studio is a 32-bit application when you
launch
the export wizard it launches a 32-bit
version and gracious and the problem is
that i'm on a 64-bit machine and I ran a
64-bit installation here of sequel
server so when it launches that 32-bit
version there is no 32 bid
sorry 64-bit know i might 32-bit
provider for excel 2007 here so the
solution to this yes you can do it but
you can't use management studio
ok close out of management studio get
out of here go to your Start menu go to
sequel server 2012 and right there you
get the option to choose which one of
these you want
they are the same wizard remember
important and export choose the 64-bit
one and it will work just fine i still
go to 64-bit the exact same screens
nothing seems to have changed right you
change it to win seven or newslocal
destination again it's excel my 2010
excel file dot xlsx if you do not put
the xlsx it will default to annex sls
thanks I'll pass
xls sorry just say they're right and now
i can choose 2007 and it works fine
ok so make your choice and I chose the
wrong database to the wrong source
database I think sorry I make my right
choice copy the core stable again I'm
not going to change it from course title
run immediately and say finish okay and
when I look at it now
sure enough it has created that
correctly in the data is no different is
it
no the file type is the only real
difference and perhaps some metadata in
the XML for the excess LX but really for
us it's know
big difference here typically when
exporting I would be completely fine
exporting to xls but you may run into
file size problems or column with
problems so excess xlsx is going to be
easier and what you just should use
today