How to migrate data from Google BigQuery to SQL Server using SSIS

How to migrate data from Google BigQuery to SQL Server using SSIS


Use promo code LEARNSSIS10 to unlock a 10% discount on Devart SSIS Data Flow Components https://www.devart.com/ssis/universal-bundle/?utm_source=youtube&utm_medium=referral&utm_campaign=learn-ssis-bigquery-sqlserver

Ready to simplify your data migration process? In this video, I’ll demonstrate how to seamlessly transfer data from Google BigQuery to SQL Server using Devart SSIS Data Flow components—no coding skills required!

Timestamps:
0:00 Introduction
0:58 Making Google BigQuery DataSet
6:05 Connect to Google BigQuery
9:21 Connect to SQL Server
10:08 Run migration using SSIS
11:50 Migrating data from SQL Server to Google BigQuery
14:29 Summary

👉 Check full overview of SSIS Data Flow Components for Oracle here: https://www.devart.com/ssis/bigquery/?utm_source=youtube&utm_medium=referral&utm_campaign=learn-ssis-bigquery-sqlserver

🔥 You can download SSIS Data Flow Components for Oracle Trial here: https://www.devart.com/ssis/bigquery/download.html?utm_source=youtube&utm_medium=referral&utm_campaign=learn-ssis-bigquery-sqlserver

💰 You can compare pricing options of SSIS Data Flow Components for Oracle here: https://www.devart.com/ssis/bigquery/ordering.html?utm_source=youtube&utm_medium=referral&utm_campaign=learn-ssis-bigquery-sqlserver

👉 Check full overview of SSIS Data Flow Components here: https://www.devart.com/ssis/?utm_source=youtube&utm_medium=referral&utm_campaign=learn-ssis-bigquery-sqlserver

Happy Learning.

If you have any questions or suggestions, please comment on the video or write to me at “aqil33@gmail.com”

⬇️ Download files and scripts: https://drive.google.com/drive/folders/1-zmuKbL643Ea1nCTVFJzMAyYp8YATZWH
➡️ SSIS Tutorials: https://www.youtube.com/playlist?list=PL_YF%E2%80%938vjjEVEXMf2hEFn0D5tEJV9kRqi
➡️ SSIS real-time scenarios examples: https://www.youtube.com/playlist?list=PL_YF--8vjjEU1Hdlqp2U_q0JNyFPYUIKY
➡️ SSIS Interview questions and answers: https://www.youtube.com/playlist?list=PL_YF--8vjjEVYkoxioTCEKAkBxjLEUUB7
Closed Caption:

hi friends my name is Akil Ahmed and in
this particular video tutorial I will
show you how to migrate data from Google
bigquery to SQL server and vice versa so
the agenda of today's video tutorial is
that how we can migrate data from Google
big query to SQL server using ssis and
then we'll migrate data from SQL Server
to Google bigquery using ssis as well
for this particular migration we will be
using the ssis data flow components from
devart they provide a wide variety of
components to migrate data from one
source to another destination so if you
want to try their components then you
can download the trial version which is
free for 30 days and if you want to
purchase their component then you can
get an additional 10% discount using the
learn ssis 10 coupon code so let's see
how you can migrate data from Google
bigquery to SQL Server so I have an
account on the Google and if you want to
for example log to the Google big query
then you can just type Google big query
okay and and then you can just simply
log into it okay and then you know you
can click on it like try it in console
so this will open the console for the
Google big query okay where you have the
different options available so this
particular window got popped up okay so
I have an account on the Google big
query so I have this particular default
project and in this particular project I
have the demo data source okay so if you
want to for example want to create a new
data source data source you can think of
like a data database okay so in the
database we can have tables similarly in
a data source we can have the tables as
well so we can create a new table here
but if you want to know like how to
create a data source so you can simply
click on it on the project and then
click on the create data set so you
simply need to provide the name of the
data set and that's it uh the default
region is this one us but you can select
the other region as well okay and then
click on the create data set so it will
create a new data set but I'm going to
use the existing data set so I have this
demo data set and I want to create a
table here so to create a table you can
click here and then click on create
table you can create the table using the
SQL query as well but right now I will
show you how you can create a table from
a CSV file so I have CSV file on my
local machine so I will create the table
using that particular CSV file so in the
first option there are multiple options
either you can create an empty table so
you can select this option create empty
table and you can Define The Columns
here add field however there are some
other options as well that you can
upload a CSC file or maybe other file
like Excel file from here or you can
upload the file from Drive Amazon S3
Google big table or as your blob storage
so I will use the upload option from
here and then I can browse the file so I
have a CSV file on my machine like this
one uh it contains I think I think 442
records it's a CSV file so I'm selecting
this particular file so it has
automatically detected that the format
of the file is CSV it can import some
other files as well like Json AV Park
and orc so our file type is CSV okay so
the destination project will be this one
evident V this one and the data set will
be this one demo data set and you can
give the table name so I can give the
table name is employee for example and
on the SCH schema I can select on this
one autod detect so it will
automatically detect the schema from the
CSV file now there are some options like
partitioning on the table but right now
I'm not going to do any kind of
partitioning I will just create the
table as it is so we don't need to look
at the advance options as well just
click on the create table and it will
create a new table and will upload data
from the CSC file into the
table so the employee created so that's
good so if you want to preview the data
from this particular table then you can
just click on this option and click open
new tab okay so this will show the data
from the table so here it's showing the
schema like these are the columns in the
table and they data type however if you
want to see the data so you can click on
the preview option so here you you can
actually see the actual data from the
table so this is the data like ID first
name last name email gender okay you can
also query the table as well so for
example if you want to query the table
so you can click here and then there is
an option query so if you click on the
query then it will generate the query
for you so suppose I want to select all
column so I can write star here like
select a star from this is the project
name till here P6 this is uh data set
name and this is the table name so if I
run the query now so it will select all
the records from the table limiting them
to up to 1,000 records okay I think this
table contains 442 records so I can
actually show you like how many records
this particular table contains so I can
execute this query count star so you can
see that the total number of Records in
the table are 442 Okay so in the first
step what we will do that we will uh
migrate the data from the from this
table from the employee table from
Google big query into the SQL Server so
I have the SQL Server instance here okay
and I got the same employee table with
the same schema here in the SQL Server
so if I execute the query then you can
see that the table is right now empty so
I got the employee table but the table
is empty right now so we will migrate
the data from the Google big query to
this particular table employee table in
the the SQL Server so let's open the
visual studio so this is my uh Visual
Studio where we will write the code to
migrate data from Google bigquery to SQL
server using the devot ssis data flow
components so to migrate the data we
will be using the data flow task so we
can simply drag and drop the data flow
task from toolbox into the control flow
window so I can actually call this
particular task as uh big query to SQL
ser
okay now I can double click on this one
now because we want to migrate data from
Google big query so our source should be
big query source so I can simply drag
and drop the dard big query Source into
the data flow task all right so the big
query Source has been added to the data
flow now let's right click on this
particular task and click on edit so
this is the deard B query Source editor
inside The Connection Manager we need to
click on the create a new connection
manager so it should create a new
connection to the Google big query all
right so this is the default option that
will pop up so here you can click on the
web login okay so it will ask you like
using which account you want to connect
to the big query so I'm using this
account so now it's saying that the dart
ssis data flow components wants access
to your Google account so you can click
on continue and now you can see that the
authorization is successful all right so
the first option is the project ID so
the project ID is this one evident wi
this one okay so you can copy this
information from here till this one P6
so I can copy this information and I can
paste it here now the data set ID you
can also you know get from the same
location demo so this is the data set ID
this one so I can paste it here okay now
the cloud storage bucket in our case the
cloud storage bucket name is this one uh
let me open this one uh big query ssis
okay so this is the bucket name so I can
go back to visual studio and can paste
the bucket name here now I can click on
the test connection so you can see that
the connection is successful so that's
good I can click on
okay
okay so I'm connected to the demo data
set and inside the demo data set I got
the employee table which is the only
table at the moment if you have multiple
table then it will show you all the
tables okay so I can simply drag and
drop the employee table into the query
window so it will automatically generate
the select statement if you want to
preview the data so you can click on
this option preview data and it will fet
the data from the big query and it can
show the data here okay which is cool so
I can click on close you can also use
the system variables and the user
defined variable in the query as well
which makes the things really Dynamic so
we are done here like we will select all
data from the employee table and we'll
migrate it to SQL Server so so that's
okay for the big query Source editor so
I can click on okay so our big query
Source has been configured now we want
to migrate the data insert the data into
the SQL Server so we can simply use the
allb destination so I can simply drag
and drop the oldb destination into the
data flow task and then we can connect
the big query source to the oldb
destination right click on the oldb
destination click edit here you can
create a Connection Manager to the
school database so we already have a
connection which we can use from data
access mode we will select table or fast
load and then from the name of the table
we can select the employee table go to
the mappings here you can see that all
the input columns have been
automatically mapped with the
destination columns that's good good
because their names are same if one of
the column is not ma then you can map
them manually as well now click okay so
now we have configured the data flow
task to migrate the data from Google big
query to the SQL Server so I can click
on the start button and it should
migrate data from Google bigquery to SQL
Server so you can see that the migration
of data 442 records from Google B query
to SQL Server is successful I can go
back to the SQL server and I can run the
select quy so you can see that the data
is successfully migrated here so which
is good okay so you can see the
migration is successful which is a good
news okay now suppose if I want to
migrate data from the uh you know SQL
Server into the Google big query then
how we can do that okay so suppose uh if
I insert 10 more records here like
insert into
employee okay and if I
selected 10 records from the same table
okay so now uh we got 452 records now we
have in the SQL Server now suppose if I
want to migrate this data to the Google
big query okay so uh suppose let me
truncate the existing table like
truncate table and the table
name so I can execute this particular
query
so this statement removed 442 rows from
the employee so which is good now let me
uh check the count again from the table
now it should have Zero
Records yeah you can see that now we
have Zero Records so now let's try to
migrate data from SQL server and migrate
all 452 record from SQL Server to Google
bqu into the same table employe table
okay so I can stop the execution of the
package and now let me disable this task
and let me just drag and drop another
data flow task okay and I will call this
particular data task as SQL Server to
Big query okay and this time because we
want to read the data from the SQL
Server so our source will be oldb source
so I can simply drag and drop the oldb
source into the data flow task and I can
configure this one
I can write a query here like select a
star from employee okay so it will
select the data from the employee table
from SQL Server now our destination will
be the big query so we can use the deard
big query destination and then we can
connect the oldb source with the big
query destination and configure the
bigquery destination from The Connection
Manager we can use the devard big query
Connection Manager now go to the
component
properties the action will be insert
here inside the object name you need to
select the table name so our table name
is this one demo. employee demo is the
data set and the table name is the
employee okay now we can go to the
column mappings and from the column
mapping make sure that all input columns
have been ma with the destination
columns so that's good so I can click
okay so now we are good to execute this
particular package now now this time it
will migrate data from SQL Server to
Google
bigquery all right so you can see that
the migration of data from the SQL
Server to Google big query is successful
so it's so simple that you can simply
just drag and drop couple of components
and you can migrate data from one source
to another destination you know so the
dart components make it very easy to
migrate the data from n number of
sources to n number of destinations so
now let's go back to the Google query
and verify that the data Got Loaded into
the employee table okay so let me
execute this particular
query so now you can see that we got 452
records here so which is perfectly fine
if you want to preview data so we can
preview the data as well from this
particular
table so you can see that the data got
migrated correctly okay so I think
that's it for today's video if you got
any question then you can comment on the
video thank you guys for watching the
video and if you like the video then
please click the like button do
subscribe to our Channel press the Bell
icon and click on all so that you will
be notified every time new video thank
you so much

Video Length: 14:44
Uploaded By: Learn SSIS
Published: 1/10/2025
View Count: 1,855

Related Software Products
SSIS Data Flow Components for Google BigQuery
SSIS Data Flow Components for Google BigQuery

Published By:
Devart

Description:
SSIS Data Flow Components for Google BigQuery: BigQuery Source, Destination and Lookup for SSIS Devart SSIS Data Flow Components for BigQuery add a BigQuery Source, Destination, Lookup and Connection Manager to SQL Server Integration Services, for $179.95 a year (Developer license) or $349.95 perpetual. They connect to Google BigQuery with OAuth, read with full BigQuery SQL support, and load data by uploading CSV files to Google Cloud Storage in...

Copyright © 2026, Ivertech. All rights reserved.