How to split large table data into multiple Excel Sheets on a Excel File by using SSIS Package-P169

SSIS Video Scenario:
How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
which was dividing the data to multiple sheets depending upon distinct value in one of the column.

In this post, we are going to split the rows without using any column value. Think about a situation where you have 3.5 million records and you would like to write 500,000 on each of the excel sheet.

The package should be able to take number of rows per sheet as variable value so we can change anytime we like.

link for the script used in the video
http://www.techbrothersit.com/2016/03/how-to-split-large-table-data-into.html

Check out our Dynamic Excel Source and Destination videos in SSIS Video Tutorial playlist on below topics


How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task
How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task
How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
How to split large table data into multiple Excel Sheets on Single Excel File by using SSIS Package
How to Export All tables of a database to Excel Files with Date-time in SSIS Package
How to read Cell Value from Excel by using Script Task in SSIS Package

---

Closed Caption:

welcome to check brothers the
denominator in this video we're going to
learn how to split large table data into
multiple excel sheets on single axle
file by using ssis packages so think
about a scenario where you have a 2.5
million records and you would like to
split maybe 500 thousand records on each
of the sheet so in this case that you
will be creating the one external file
with five shoot so I have a small table
and it has only 12 record think about
that if I would like to split the the
three records on each of the shooters so
i will create an excel file and that
there would be four excellent sheet so
and that a good will be on each of the
shooter we can change it let's say
tomorrow you would like to have six
records on each of the sheet that's
that's absolutely fine and over maybe
you would like to have five records on
each of the sheet or so you can do that
in case of a we need fiber as we have
1200 first two sheets will have five and
five the rest of the last search it will
have only two records
let's go ahead and create this package
and experimental now open sdt sequel
server data tools a come to the ssis
packages right click your new SS is a
packager let's create some variables so
we can change the value of these
variables by using the configuration in
different environments such as qat and
production as well as we would like to
change your the number of for a record
content each of the shoot we should be
able to do it to dynamically by using
the configuration and variable values
though i'm going to use the folder
source folder that's where our oh
actually i would say destination folder
and that's where we would like to put
the file and go back to folder structure
here i have a destination folder that
could be any name doesn't really matter
whatever the name you decide that's fine
i'm deleting the existing files come
back to the sdt sequel server data tools
is your folder path here then what the
name of the xml file you would like to
create in this case I'm going to go to
tech brothers IT
that that's the name i would like to
have from Axl filed the time part will
be added later
so that's it good every time with
another package it should create a new
excellent file so i hear executed
excellent she is
exhales filename that's really confusing
x + filename sheet name this so I'm
gonna go tech brothers IT is very early
morning so I got up and I don't before
even office work started I should make
some video and now roll count per sheet
that's going to restring let's say we
start with three toes parachute and the
last thing we need our table from
wherever need to expect daughter Rose
them
let's call this one capable name now
this is going to be a string as well i'm
going to use a disabled what i have here
baby daughter total sale doesn't act was
itwas so so we'll create the connection
there we are all set for the variables
now next we will be creating connection
manager and we need to read the data
from sequel server table right click
here in the connection managers go to
New with your dark net connection and
delete existing one let's create the new
look here it will get us the sequel
server instances on local machine order
on the network so this is what i'm going
to use i have tech brothers IIT just
consider it okay okay let's rename this
one so I'm NP sequel 2016 that's really
not great name and then you will create
the configuration this name you don't
want to put that there so i would like
to have something meaningful that
calorie like okay this is a connection
manager and DB connection and that's
better
and then you will have your database
name in your case it will be your name
maybe customer sales whatever you have
let's bring a script task in the control
flow pan and opening will be using these
variables of i mean i'm using up we want
the sharper for the scripting up in a
script task you can use the visual
basically the cord i'm going to provide
is written in C sharp oh so click here
and we add those variables unless you
use a variable here it would be nice if
we can just click on the name and it
will sort order so we named the user
variable come on top and then we don't
have to go all the way up and down to
find the user variables up so we have
for using variables all set
I don't edit script is going to open ask
window where we can write our C sharp
order while its opening a distributor
window we can go to take brothers
itv.com and I need to show you feel
things up here once you are intact
brothers itu will be going to the ssis
video tutorial Oh once you are there you
will be going to skip tasco and under
the strip task I have excellent source
and destination skipped a dynamic oh you
will be going to the last actually
second last article here how to split
large that are taking a table data into
multiple accidents sheets on single x +
5 using ssis package so you will click
there and then i have written the poster
if you're in the office you can watch
the video fine you can follow the poster
and create the packages from here i'm
going to copy the script from here
so first of all we need some namespaces
as we will be using different functions
or methods that from these namespaces up
let's go back to the editor and here
under the namespaces the region we are
going to hit enter and paste i added
system dial as i will be reading a some
file information let maybe I want to
believe the existing file in this case
so that that's where i can use that
system . data . only to be as i'm
writing to the XO I need this namespace
so as I'm reading data from sequels are
what I need system . sequel client named
spacer now come for the down here lets
the delete this part of because i have
already included this part in the
bored so you don't need this go back to
the tech brothers and copy this whole
thing
let's copy all the way till here copy
and go back to the editor and a ps2
right here is a public would mean and
add your coat here so that's where we
need to pass the record let's save it
and build it if there would be any added
we should see that a couple of things to
notice from here if they did not to us
that any editor so that's grating but
there are some things we need to
consider by ourself these variable names
you see that they are we have to claim
some local variables here and then using
a Texas is variable them why because i
don't want to type of this whole thing
DTS right variable everywhere in my
coder so I i want to only use these
variables y squared is variable and
they're using and setting the value of
this variable by using ssis variables up
first of all i'm using the hamstring
that time so including a string type
variable and women of the year month
date and hour and minute and second in
that so i can use to create excel file
with the daytime
no I'm not adding this part above the
try so i can use this in the catch block
for header files as well if that
happened I need to create that better so
i will create this with the same and
daytime know what I'm using 4x ola it
will not let me okay this excellent was
processing and that's that a file in
case that happen now we're in the try
block we are starting a reading the data
and loading the data to the axle and if
it will go to the catch block i'll show
you all the way now here i was saying
even at the building succeeded
we need to compare our variable names of
whatever very feminine name we are using
here see folder pattern now tablename a
record counter our shooter so let the
take a look at what we have first follow
we need excellent filename ok so i have
an excel file named a spine instead of
for the next part
I have a folder path here so in this
case i have created destination folder
so that's two different variable if you
will use the same very different
variable and use the same skip is going
to fail so let's change that s the
nation order why I'm doing it I could
have just created the same variable
because i want to tell you even hear the
build succeeded everything works fine
these variable can create problem so you
have to have come back and match your
variable name exactly what you have .
necessary yes package a row count
pressure to see here i created the rukon
per sheet and then we have different so
go back
here's the row count per shoot that's
what we need to count actually CMT sheet
so now pretty much we had all set we
have tablename destination folder and
excel file name
let's take a last look excel file name
and real counterpart sheet okay i need
to the correct spelling for real count
for shoot and the destination folder
can't see you
okay great now here what i'm doing i
have declared these variables that same
in the values of this is the i took this
one as a row counter was taken as the
spring so I'm converting to the interior
here great and then once and row counter
Percy decimal why I'm doing decimal
because of lets the go back to sequel
and if we have a selector 12/5 so now it
is going to give us to its not given us
any decimal value so we need exact value
it so i can work at this number to
decimal now you see what happened now we
got the exact decimal number and from
here i'm using that there is one
functional call the ceiling up so we
will look once you put that is going to
give us the above value let's say
anything you have to . little one or
something that is going to give you
three because they're now in this case
let's say
we have 1200 records we are dividing
into the multiple sheets and each should
have a 305 records first issues will
have yes sir and we need actually three
sheets so by doing it i am creating a
number of sheets i need to create oh so
thatthat's thatthat's starting if I will
just use the divider the total number
for this is the number of Records I want
on each sheet this is the total number
if I was just divide i will get to so
what will happen with two records i have
in this case so file first two sheets
will be fine and you each will have a
five records that but the third one that
would be noted one so i need to create
that I can multitask mall and then I
used ceiling to give me three issues so
especially in these cases when the
numbers are not exactly dividable so
let's go ahead and take a look further
here i'm saying excel file and because
i'm using excel file and plus the
daytime parts or I couldn't and set the
value for this one now already
connection let's the only to be
connection unity connection creating a
new instance ok command will need to be
commander new order to become and i will
use this Wonder and here I'm
constructing the connection string for
excellence arms and provider microsoft
AAC daughter at the only be . well .
code and data sources a folder path +
x-l fire that's where we would like to
create our axle filer okay Sandra
properties and we have a header yes sir
and now drop the exle file if existence
in this case you would ask okay you are
creating the file with the daytime and
$OPERAND hours minute an evident
everything is a hotel at the second
you're not going to have that file
that's absolutely right but if tomorrow
your requirement does Jesus say okay we
want to have a package that can keep
running every hour but if the fine is
that it should delete but leave the file
for each of the day in that case you
will just remove this part of and legal
the year month and day it will come back
and drop that file and create a new one
every time it is going to override it
and that that's one of the things
elected there and also you can run
your file of multiple times in the day
you're not going to run the second level
i know that but still like there are
scenarios where now you will end up in
this case that the small changes if you
don't want
okay fine deleted so it is not gonna
hurt if you have it there
fine in cases your scenario change you
can use it now we are using now my
internet connection
ok we have already the connection
created a desk called BB and this
quarter connection discotheque brother
IT why i did not use the DB connection
of as it is because i want to tell you
even the build succeeded and everything
is just greater you need to take a look
on your connection manager named by
yourself you have to fix it so whatever
you are using in my escape but that's
not gonna work for you you shouldn't use
your connection manager named what you
have created and in this case we have
created a baby and the skirt connection
discotheque brother I t three distinct
group values for each excellent she
don't know this is Vera remember our
sanity use select ceiling count start to
get all the cum from the table divided
with that number and get the rounded
value ababa let's say in this case we
were getting a two-pointer for so
ceiling is going to give up as that
whole number that will be like next
number is three right so if you have a 1
in this case that let me put the sitting
here
so if you will divide with less stamina
so it will normally what happened
let me copy this one I'm gonna use this
thing here
ok so now you see the difference here
with 1.71 when you divide 12 with salmon
so that's what we get with the ceiling
it's going to go to the next whole
number so that's the purpose laker with
the record huh
yes that we have total 12-7 records
parachute what will happen to the five
records that we need to shoot so so
that's why I'm using sitting here one
more time
okay then I'm not putting a decimal
decimal here and then loop culture so
I'm kind of looping and create the
nubile so we can use that here get the
kind of sheets that need to be created
so once we have this query I need to run
this query and get the value in the loop
counter so that's a very our quest is
gonna run and see the value in the loop
counter now we have seven string shoot
name that's what i would like to create
she 21 sheet1 sheet2 and sheet3 and all
that in your case let's say if you would
like to say that brother shoot so if you
would like to use that you can use it
your choice whatever you want to use the
tech sheet for the sales you do so you
can provide the name with whatever you
like
here's the one more thing that you could
have carried the variable and use the
sheet name i didn't use it so i have to
come here if I need to make a change if
you will have a variable you can use
change it by using configuration anytime
so I'm saying here in teacher start
counting 0 and then the integer and
counter record count per sheet oh so
that's where i'm setting the values of Y
i'm setting the value I have total 12
records oh so what I need to do i need
to grab the record from here I don't
have any really I i have ID here but I
don't want to use the idea maybe
tomorrow I would not have ideas that
table so tables can be different columns
can be whoever I want to build something
dynamic I don't care whatever the column
2 or to your for that is the idea there
is no idea i want to create my owner
numbers
here so i used let me go back here and
let me copy assaulting i'm using CTE and
with the row number function i'm
creating my own role so let's go back
here paste and take a look on this from
table so let's say that this is a the
table i know it is the time consuming
but I have to explain all that this is
very important to end up and i have seen
tons of requirement for this scenario so
you see I am creating a new row number
so from here once i have the wrong
number i can divide this the sheets
according to this rule number now i know
i had the common sheets I no need then
here i can use that is start counter and
and counter so between the raker let's
say I'm starting with this one and till
three so that's my starting like you can
start from zero go to three fine and
less than equal to refine next door when
it comes up
ok start with the and greater than three
and less than equal to 60 so this is
where we are building that you have you
see that i am using these variables that
to build an entire sequel first I have
this i added their own number then in
the very closeted I'm not getting the
values on each of the new but different
for each of the sheeter so go back here
and now you see that i have these two
variables that I have to set the values
where ever increasing values or set the
values for these variables on the next
loop so you will see here i'm setting
the value at the end of gadgets I'm
gonna restart row count 10 at that point
and now it is a record count per sheet
and andrew locanda is the record
compensator so once I add these values
is this is this one starting with zero
this was not starting 0 the this was
already listed three or five or whatever
number we have given that is going to
keep adding and buildable very closeted
for the next shooter so we'll start with
the first year and then keep building
and keep writing now once we have our
sequel builder we are running that
sequel and filling up the data that then
whatever we need now the data is in the
memory we need to write that Daddy
sequel Jeter we need a list of the
columns of because the excellent
excellent we have to have cleared the
shoot first and then load the data so
here I'm getting up the list of the
columns and putting up appearances
around right there is space for the
column name it will take care of their
part and here okay I'm saying no now I
needed every column of my ex oh shoot is
going to be textile you can have an
water water whatever works best for you
and once the column listed building i'm
building a create table statement so
once i have the create table statement i
can execute data so these are like we as
in ok lady connection use the connection
string is the connection string open the
connection and then this is a command
should use our the connection already
connection and finally we have any
quality now once the table is created
that will be our sheet and hear what we
are doing we are right in the data so we
will be looking through all the records
of what we have in the data center in
memory and then we are building insert
statements here and the writing those
values 0 so this part is going to write
that still here once that's done
not next to the values will be set for
starter account and andrew counter and
that will be used for the next query
right here and also allocate a new sheet
as a the loop counter is going to be
treason and we will be keeper riding on
to the new sheet so that till it does
not and let's review this quickly so we
declare the variables and local variable
so we can use the ssis variables to set
the values for these we have a excel
connection and having that ok we will be
using already be commander for
disconnection we are building our
connection screen for axial we are using
our
sequel server connection i do . we have
created here we are building a new query
for the distinct counter for the sheet
so depending upon the total record of
the table / the record count person we
have decided then once we have a the
counter we need to run that query and
get the counter here we are Cena okay
what would she named then we start
counting at zero that's the lego car
starting with 0 record as we have
created the wrong number so we can use
that and andrew contest the record count
what we have defined for the shooter
these variable will be used in other
select very to give us the record for
each of the shooter here I'm looping
through so I we know that all the total
sheet number regarded to already in the
loop counter so it's just a for loop and
saying okay tell it is not less than or
equal to do loop counter so keep lupanar
on each of the loop we are using
different starting and ending the row
counter as we have created that your
number by using CT and common table
expression and we have used in row
number function over-ordering we're
really nothing to order one because we
don't really want you any column we just
want to create that real number so i use
that one filled up data in the memory
ok make connections to make a column
lister so we can use that in creating
the other excellent shooter that's done
here and then finally we are writing all
the records from that data center 2d the
axle shito and finally we're
incrementing that role counter that's
the starting and ending so we can use it
for the next you to back here right into
the next year so that's good
here is our catch block even case that
it happened and all that disappeared
other we are going to end up we're using
folder path variable see here that's
where we should be using up the
destination folder in the next video is
actually I I kind of got better and i
created another variable called lot
older
so you can create your lock folder if
you like and use a variable value hear
that
that you have a folder here for the log
files that you would like to play the
log file
ok provide that pad create a variable
and use it the same way we use the
destination folder here and exhale
filename and then we are adding a date
time and the dark log so that's the log
file will be created
let's save it I know it's a lot of taco
has don't like me when a dog allowed so
i hope this will help somebody who's
struggling the distant audio so I'm
going to start a package let it fail or
success
ok failed that's good thing so we need
to debug now come back here go to the
folder and that's where our k we are
creating an hour longer than the
destination folder that's where actually
we need to create over exile as well so
open it and that now element cannot be
found in collection that's great so we
know that some of the variable order
maybe connection manager is missing work
has incorrect name so you see here
variable that get Adam that means the
variable is not a datum so what I want
to do need to compare this one more time
so here i have destination folder i have
this record comfort so let me do one
thing up if I would you use the
organizer put these ones are in now
copy i'm gonna open a notepad most of
the time we have a two monitors an
officer and it is much more easy to work
with a name and we have a role . shooter
i really like when you guys leave the
comments and say something about it like
okay good job a job or even worse the
app it really helps i mean in a way it
will improve my way of teaching and I i
can see like what people are expecting
so if you feel like okay this guy's not
doing good job here okay this can be
done better way please let me know
try my best to change that ok
destination folder so we have
destination folder we have tablename
fine and they're excellent filename
filename and we have roll count per
sheet rowcount shooter do that's the
anything they're here to be contact
brothers IT I really don't see anything
it is the complaining about it exhale
filename exhale filename fine
destination for and not I didn't we are
humans right sometimes you can't be with
the things that right there appreciate
roll count per sheet so this is very
dizzy a getting really tricky like okay
we'll what exactly we are making I don't
know I excel file name one more time lo
que les the don't sit cancel come back
here and let me see destination folder
so destination folder excel file name
Rokon per shoot cabling you might be
already know what is that because i mean
i am not be able to see and you gather
able to seize or we are using four
variables fine we have them right here
so contact sheet fine and here let me
try to minimize and see if I can compare
them parallel
come on later I don't want to do it is
not ok let's make this one of the bigger
so we can compare in power
ok xml file name that looks fine to me
destination folder
ok and Rokon parachute on shit that
looks fine and tablename that looks fine
now the only tech a connection i really
don't want to spend all our time here i
am i'm sorry really like oh ok let's go
back and see if I can use a summer break
points so I'm gonna hit her here and
let's see if that helps oh and this is
the only things we are using here so
save it close it
okay i know you guys are excited to see
how it works and how it is going to load
that number of records on each of the
sheet from the table you don't want to
be but all these things but this is part
of the data to be straightforward and
literally my eyes can see that like
whatever I'm making here so let's see if
that helps we are going to figure that
out
this is what once you put the break .
what happened
it does stop on those places and you can
literally see the things ok this is
where the error is that you need to
remove those Bighorns later and if you
run it and it does take like a few
seconds when we run into it because it
will open that window for you and you
can continue so let's see if this is
fine and we can hit continue
they had first one past the second
pastor table this walk onto appreciated
ok so Rokon per sheet is the problem
that's the problem so row count per
sheet let's copy from here and go
exactly open our script up and take a
look
row count
see I knew that I made a major mistake
big mistake see or UNT right and here i
have c or UNT fine but there I have see
all I am missing you
fine but has no big deal I i know you
guys like me and it's not
it was good practice sir so we gonna
removal you from here or 11 we can do we
we can say okay so you NT per shoot fine
and you learn something good lesson from
here like okay you can't make a mistake
with the spelling and all that offer
these are no variable and see or you
count pressure now if we make a change
here in the variable we have to come
back to script and kinda riad that one
because that's the totally new variable
so let's go back here and find that one
we are getting too close here okay
no it should be just fine
you will learn a lot of ways to debug
our packaging and just the scripting it
really did help so okay what exactly
that now we are going to go back and
take a look here especially cast is not
valid
so which cast it is talking about now
this part let's see let's go back here
now we are going to debug i am sure the
Iran this one on my other computer is
there was no problem and now we are
getting into problems here we have a
into string string string that they what
we have here let me see the pause to
because sometimes we get betters
okay so okay see I was actually using
the row count parachute as a number so
that's fine i'm going to use as a number
and I don't want to use a spring here so
let's use this one as integer fine we
should be fine now i'm sorry if i was
using as a string here than what i
should do i I should convert inside so i
did not convert that one that if you i
was talking about that if you're using
it as an interior here you have to kind
of come back here let me show you what
I'm talking about
so if you are you creating your sis
variable as an integer when you come and
use here you have to tell explicitly
tell here so you okay i wander into your
32 record contract is variable and this
variable the strings you don't have to
do it but with the interior you have to
pass this conversion so that's what i
was talking about so still we are using
quindi integer so that's fine
close it I'm really sorry for a second
i'm on this one I'd I had no plan to
take time better
these are the small things are coming up
automatically
now it is damaged should not fail i'm
gonna be really disappointed so it
passed though that's great let's go back
and take a look here now we should be
coming to the destination folder one
more thing see destination folder has no
excel file let's go one step back and it
isn't here so you have destination
folder and that it is going to be one
parent folder the sheet 1 is greater
sheet to sheet 3 and shit for records i
come incorrect all looks good but this
should be in the destination so that's
not there
why because here and at the end of the
destination folder we have to put back
selection now let's go ahead and run
okay that's fine now we should go back
here and excellent fighters it so now
you see that we have a excellent one two
and three each one has a 3 records that
because there were 12 records and we
were we want to have only three records
per sheet now if I will go with five
records i'm gonna run the package this
time it should create the only three
sheets completed successfully
now we have done to shoot so so five
records five records and five records
sorry Ted to read through the records
that I'm really sorry to records that
because that we have five records here
right here
that was the first was a header so five
records here and then finally we have a
2 records and one was header now if we
look go above that we can do it so we
let they would like to have only one one
or two sheets so we can go let's say I
will go with nine record count per sheet
night I'm gonna run it it will create
the only to shoot so so just worked fine
now this part is more playing and the
you know you can see that nine records
and here we see that only three records
now if you would like to go with the one
you can always a more pertinent to our
water lemon
so you well as it has the maximum number
we have here so your choices
thanks very much for watching this video
i will put the link in the description
for the script so you can go from there
and that just make sure you use the
exact same or variable names i have used
to skip that's how you will not make
mistakes about it was good learning i
like that experimenting and showing you
guys doing how to accept how to find the
plug problems and that's where as the
beginners that this really helps oh so
if you're expecting you just need to
copy and paste to the cord you don't
need to walk to but it's a good idea to
learn and maybe this will open a new way
for you to think for different scenario
you can use some quote from here and
build a new scenario
alright then thanks and i will see you
guys next video