How to migrate data from Amazon Redshift to Oracle using SSIS

How to migrate data from Amazon Redshift to Oracle 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-redshift-oracle

In this video, I’ll guide you through migrating data from Amazon Redshift to Oracle using Devart SSIS Data Flow Components. This tutorial walks you through each essential step to ensure a smooth and seamless migration, all without complex coding. You'll see the complete process of transferring data from Amazon Redshift to Oracle and from Oracle to MySQL.

Timestamps:
0:00 Introduction
2:44 Set up Amazon Redshift source
6:11 Set up Oracle destination
9:15 Running and checking SSIS project
9:40 Migration data from Oracle to MySQL

🔥 You can download a trial of SSIS Data Flow Components here: https://www.devart.com/ssis/universal-bundle/download.html?utm_source=youtube&utm_medium=referral&utm_campaign=learn-ssis-redshift-oracle

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

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 Amazon
red shift to Oracle using ssis so the
agenda of today's video tutorial is that
first we will migrate data from Amazon
rad shift to Oracle and then we will
migrate data from Oracle to mySQL using
ssis so we will be using the ssis data
flow components from devart they provide
wide variety of components to migrate
data from n number of different sources
to a of different destinations so you
can check their website and you can
download the trial version for 30 days
and if you like the component then you
can purchase the license and you can get
an additional 10% discount using the
coupon code learn ssis 10 so now let's
see how we can migrate data from Amazon
red shift to Oracle so let me connect to
the Amazon red shift so I'm using the DB
visualizer which is a free Tool uh that
I'm using right now so are you connected
to the red shift and under the
databases I have Dev database okay and
under Dev database under SCH
schemas there are different schemas
right now so I'm using the public schema
and inside the public schema inside
tables I got few tables here for example
I got the products table so if I show
you the data from the products table
script table select star click okay so
it's generating the select statement
okay so if I I execute the query then
you can see the data from the products
table so it contains few records so you
can see that it contains five records
and it contains the information about
different products like product ID
product name category price Etc okay so
I want to migrate the data from the
products table from Amazon RAR shift to
the Oracle so I also have the Oracle
install on my machine and uh in the
Oracle server I also got the products
table here as well but if I show you the
data from the products table so right
now the table is empty okay so the data
in the Oracle will be populated from the
Amazon red shift so we don't have any
inbuilt component in ssis to perform
this particular task so here the dart
SSI data flow components can help us to
migrate data from one source into
another one okay so we only need to
create the connection managers and then
we can just drag and drop their
component which can make it very easy to
migrate data from one source into
another destination okay so right now
the product table is empty and we have
the data on the Amazon red shift site
okay so now let me open the visual
studio so this is my visual studio which
I will be using today to migrate data
from Amazon red shift so to migrate the
data we will be using the data flow task
so I can simply drag and drop the data
flow task from the toolbox into the
control flow
window all right so the data flow task
has been added to the control flow I can
call it as Amazon red shift 2 Oracle
okay so I can double click on this one
and
then from the toolbox I can add a
component from the Amazon red shift so I
can type red here and these are the
three different type of components for
the Amazon red shift red shift Source
lookup and destination so because you
want to the data from the red shift so
we can use the red shift source so I can
simply drag and drop the red shift
Source into the data flow task so the
Amazon red shift Source has been added
into the data flow task now I can right
click and configure this one so we
actually need to add a connection
manager so that we can connect to the
red shift source so from the drop down I
can click on the create a new Connection
Manager all right so here we need to
provide the details of the Amazon red
shift connection so so first of all we
need to provide the server name so I
have all the details available for the
Amazon red shift so I can click on edit
and then this is the server name so I
can simply copy the server name and I
can provide the server name here for the
port number I can check it the port
number is 5439 so we can provide the
port number here okay and for the
username I can check the username from
here it's devart so I can copy this one
and paste it here okay and
now we need to provide the password okay
so for the password I can actually
cancel this one and I can right click
and copy password and then I can provide
the password Here save my password and
then for the database the database name
is Dev here okay DB is Dev so I can type
Dev here okay so I can uh do test
connection
so now you can see that the connection
is successful so that's good I can click
on okay then I can click
okay so now it's connected to the devart
red shift and now I can expand the
public schema so here you can see the
three tables you know those you were
able to see here uh Department
Department 2 and the products okay so we
are interested in the products table if
you want to see The Columns for the
product table so you can expect and this
one so you can see all the columns and
if you want to preview data from here
you want to write the query so you can
simply drag and drop the table into the
query window so it can generate the
select star from table name query
automatically if you want to preview
data so you can click on this particular
icon and you can preview data from here
that's good so I can click close And you
can also use the variables as well in
the VAR statement for example or in the
query so that's a plus point so I can
click okay uh we can actually preview
data so that's good so we can easily
select the data now from the Amazon red
shift now we want to insert the data
into the Oracle so I already shown you
that in the Oracle we got a products
table as well and right now the products
table is empty so now we want to uh
insert the data into the Oracle so for
that one we need to create an oracle
Connection Manager so I can right click
here and click on new connection
from the connection types uh we need to
add a connection for the devart Oracle
so I can click on this one and click on
ADD so this should add a new Connection
Manager all right so here we need to
provide the Connection Manager details
what I can do I can actually open the uh
Oracle SQL Developer and I can right
click on this particular connection and
I can go to the properties okay so the
host name is the local host so I can
copy this information from here and I
can provide it here okay now for the
username I can again go back and I can
copy the username from here okay and I
can paste it here and for the password
uh I can actually copy the password from
here Oracle password okay I can paste it
here save my password and then I can go
to the
advanced and in the advanced there is an
option service name okay so if you see
here there is a service name this
information so this information can also
be copied from here as well X DB1 okay
or you can copy it from here so I can
copy the service name from here and then
I can provide the service name here okay
now I can go back to the general I can
also click on the direct here so it can
show us the different options like uh
the Sid option is also aable here okay
and this is the service name so I can
click on General and I can click on test
connection so now the test connection is
succeeded so that's good so I can click
okay okay so now we have the Connection
Manager for Oracle so to insert the data
into Oracle we can use the Oracle
destination component from devart so I
can simply drag and drop the destination
component and I can connect the red
shift source with the Oracle destination
and then I can configure the Oracle
destination component from The
Connection Manager I can select the
Oracle Connection Manager from component
properties you can see the operation is
bulk insert so that's good now from
table name I need to actually select the
products table okay so I can just see
the products table it should be inside
the system schema all right so here is
the products stable so I can select this
one now I I can go to the column
mappings and you can make sure that
input columns have been mapped with the
destination columns now I can click on
okay so we have configured the data flow
task to migrate data from Amazon red
shift to the Oracle destination so I can
execute the ssis package by clicking on
the start button and it should migrate
data from Red shift to the
Oracle all right so you can see that the
migration of data from Red shift to the
Oracle is successful so that's good so I
can stop the execution of the package
and now let's move to the second part of
the video that we want to migrate data
from Oracle to the MySQL so if you look
at the Oracle so we can also check the
data in the products table now there
should be five records in the products
table so you can see that the data is
migrated correctly now I want to migrate
the data from Oracle from employee table
so in the employee table I got 10
records okay so I want to migrate the
data from Oracle to the MySQL so I have
the MySQL and in the MySQL inside the
customer database I got an employee
table so I can select data from employee
table so right now the employee table is
empty okay so we want to insert the data
into the employee table so uh I can go
back and I can create a data flow task
here okay and I will call this
particular data flow task as or equ Al
to mySQL okay so I can double click the
data flow task and uh I can use the
Oracle source to read the data from
Oracle okay so I can use the devart
Oracle Source I can add it into the data
flow task okay and I can configure this
one we can use the existing Connection
Manager that we created for
Oracle okay and then inside the tables
we got the employee table so I can drag
and drop the employee table into the
query window and I can preview data so
data looks good here so that's good I
can click on close and I can click on
okay so we have configured the Oracle
source so it's so simple okay now we
want to insert data into MySQL so for
that particular thing we need to create
a connection manager for the MySQL so I
can right click in the connection pen
click on new connection now from here I
need to select the Connection Manager
for the MySQL okay so this is The
Connection Manager click on ADD
all right so here we need to provide the
details for the MySQL connection so I
can open the MySQL Connection Manager
click on database and then connect to
database from here you can copy the
server name okay and then you can also
check the port number as well okay so
the port number is this one
3306 which is correct then you need to
check the username which is root so I
can copy it from here okay now you need
to provide the password
so let me copy the password from here
copy password paste the password Here
save my password and then from the drop
down you can select the database so my
database name is customer so I can
select this one and then we need to
click on test connection connection is
succeeded so which is good news so I can
click okay okay so we have created a
Connection Manager for MySQL now to
insert the data into MySQL we will be
using the destination component for the
MySQL so I can drag and drop MySQL
destination okay now I can connect the
Oracle source with the MySQL destination
and then configure the MySQL destination
from Connection Manager we need to
select the Connection Manager for the
MySQL now go to the component
properties the operation is bulk inserts
which is a good news and from the table
name you need to select the employee
table because we want to insert the data
into the employee table and right now
the employee table is empty as well okay
so you can
double check the data the employee table
is empty now go to the column mappings
and make sure that all input columns
have been mapped with the destination
columns click okay so to execute only
this particular data flow task I can
right click here and click on execute
task which will execute the SSR package
but only this particular task okay
because I don't want to execute the
other task at the moment because that is
already
executed all right so you can see that
the the migration of data from Oracle to
mySQL is successful it has migrated all
10 records now we can go back to the
MySQL and we can rerun this particular
query to see whether the data was
inserted or not so I can execute the
select query and you can see that the
data has been migrated to the MySQL okay
all 10 records got migrated so here we
are using four different sources we are
using the actually three different
sources we are using Amazon red shift we
are using Oracle we are using the MySQL
and you can see it's so simple to
migrate data from any source to any
destination we don't have any inbuilt
component in ssis to migrate data from
these sources okay so the ssis data flow
components from devart can be very
helpful if you are planning to migrate
data from any of these sources and if
you got any question then you can
comment on the video so I think that's
it for today's 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'll be notified every time a new
video thank you so much

Video Length: 14:38
Uploaded By: Learn SSIS
Published: 12/23/2024
View Count: 589

Related Software Products
SSIS Data Flow Components for Amazon Redshift
SSIS Data Flow Components for Amazon Redshift

Published By:
Devart

Description:
SSIS Data Flow Components for Amazon Redshift: Redshift Source, Destination and Lookup for SSIS Devart SSIS Data Flow Components for Amazon Redshift add a Redshift Source, Destination, Lookup and Connection Manager to SQL Server Integration Services, for $179.95 a year (Developer license) or $349.95 perpetual. The Destination can load large volumes through Amazon S3 and Redshift's COPY command instead of row-by-row inserts. Redshift speaks the...

Copyright © 2026, Ivertech. All rights reserved.