Practical SSIS Webinar: Integration MySQL, PostgreSQL, Salesforce, and Zoho Books data

Practical SSIS Webinar: Integration MySQL, PostgreSQL, Salesforce, and Zoho Books data


In this video, we walk through an e-commerce integration scenario using SSIS.

Key Tasks Covered:

- Extract data from MySQL, PostgreSQL, Salesforce, and Zoho Books.
- Transform the data to fit the business requirements.
- Load the processed data into a central business database.
- Automate workflows to ensure smooth, ongoing data integration.

This video shows you how to build an efficient, automated workflow in SSIS to streamline data flows across multiple systems, providing a unified view of customer order status for better decision-making and customer outreach.

🔥 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=webinar-29-10

👉 Check the full overview of SSIS Data Flow Components here: https://www.devart.com/ssis/?utm_source=youtube&utm_medium=referral&utm_campaign=webinar-29-10
Closed Caption:

so hello everyone my name is Yuri I'm
the head of partnership team here at
Dart and together with me today ail he
is our technical specialist SSS expert
who will help us to uh show you a
technical part of today's webinar so
let's get
started we are all here to learn new
skills and improve data management so
you're at the right place today we will
show you ssis and action demonstrating
how it simplifies complex data workflows
our webinar has two parts first a quick
overview of ssas and DS ssis components
then a technical walkth through on
building an ssas solution to integrate
multiple platforms and let's
go so uh in this webinar we are focusing
on e-commerce business with data spread
across multiple systems oh my SQL for
customer orders postre SQL for the
seller app sells for cm and Zoho books
for accounting the goal is to regularly
synchronize the data from this systems
to track order status paid contacted or
pending then segment customers and
generate emails lists for marketing and
customer services follow-ups we will
demonstrate to you on how to automate
this processes with
SSS let me introduce SQL Server
integration services or SSS a powerful
Microsoft tool for data integration and
workflow automation SSS handls complex
data tasks across various sources
offering flexibility to connect and
integrate data from multiple systems and
platforms seamlessly SSS is also a ETL
tool that automates extracting data from
Source systems transforming it into
desired format and loading it to the
Target destinations SQL Server
integration Services stand out feature
is a is userfriendly
guy and allowing you to build robust
data workflows without heavy coding
perfect for both beginners and advanced
users all right so let's dive into the
next part and find out what ssis can do
for your data ssis excels and importing
and exporting data across systems and
formats making it
simless it's also a great migrating data
from Legacy systems through Modern
platforms like the cloud with minimal
disruption SSS ensures real time or
scheduled synchronization for data
across multiple systems and it supports
data replication for availability and
Disaster Recovery finally SSS automates
regular backups to protect critical data
from loss or
corruption with all these features it
makes managing your data so much easier
cutting down a manual tasks and keeping
everything consistent and
secure SSS offers secure reliable
infrastructure with Microsoft top
security standards ensuring data
Integrity throughout the integration
process it also complies with industry
regulations making it ideal for data
sensitive sectors like finance and
Health Care another key feature is that
SSS Solutions are self-hosted this means
everything is hosted on your own
infrastructure given your complete
control on servers and security measures
so you can tailor the environment to
meet your specific business needs and it
ensures that the sensitive data stays
under the direct management when we talk
about cost- effective maintenance ssis
really shines with self-hosting you can
have fixed costs so you only pay for
servers you're using with predictable
pricing unlike Cloud servers where Cod
can exper rise based on usage on storage
SSS allows you control budget friendly
expenses there are no surprise fees or
variable pricing which helps reduce the
overhead this setup makes SSA is the
ideal solution for businesses that deal
with sensitive or privacy regulated data
whether it's gdpr compliance hap or any
other privacy standards SSS ensures
you're meeting the those strict
regulations by hosting uh on a
self-managed secure infrastructure you
can control access to the data directly
significantly reducing the
risk of unauthorized access or brides
and finally let's talk about
extensibility one of the great things
about SSS is how customizable it is you
can extend uh its functionality through
custom components making it adapt able
to your specific needs it's also
incredibly flexible so as new data
source and formats come into play SSS
can easily evolve to handle them keeping
your data integration processes future
proof and
efficient let's explore the SSS
components this high performance
component supports over 50 data sources
including maj databases like MySQL
postgre SQL data warehouses like Amazon
red shift and snowflake cloud services
like CRM e-commerce platforms Etc each
component is optimized for seamless
connectivity and performance The Source
editor offers SQL support for precise
data extraction and
manipulation while high performance
lookup components ensuring quick
accurate data matching the destination
components enable fast efficient dat
data loading ensuring smooth scaling
without performance issues with dart you
can integrate data faster more
accurately and with minimum
flexibility now let's talk about D
flexibility with its two product
versions you can start with a 30-day
free trial giving you plenty of time to
test the functionality and performance
in your specific data integration
environment simply visit D website to
find and download this sis component
that fits your needs installation is
quick and hustle free so you can start
integrating data with just a few clicks
no complex setups
required additionally on the a website
you will find the wealth of instructions
and resources on installing and
configuring your SSS infrastructure and
packages so don't worry you will have
all support you need to get started
and I'm thrilled to announce aquil Ahmed
aquil runs the popular YouTube channel
learn ssas where he shares tutorials
tips and insights on mastering ssas
today he'll demonstrate you how to
develop an SSS project in real time
giving you a Hands-On look and building
a seamless data integration workflow
Akil thank you for joining us today and
passing the word to you hello yeah thank
you
Yuri okay can you hear me
okay yes I can hear you well and I see
your screen so we can
get all right so uh today we are going
to uh fetch data from four different
sources MySQL POG SQL Salesforce and zoh
books and then we will do some data
transformation services and after some
data trans transformation we will
actually insert the data into POG SQL
database into different tables based on
the different criterias and we'll also
insert the data into the MailChimp as
well so that the MailChimp can be used
to send the emails further so here we
are going to uh look at this one of the
scenario that there is an e-commerce
project that has a website based on the
mySQL database and then there is a
seller application based on a Pog SQL
database and Salesforce is used as the
CRM and Zoho books is used as the
accounting service so we have four
different sources and then we have two
different destinations so let's see how
we can write the ssis package uh to
migrate data from four different sources
do some transformation and insert data
into the two different destinations
using the data flow components from
devart so this is my usual Studio 2019
that we are going to use today so to
migrate the data we will be using the
data flow task so we can simply drag and
drop the data flow task into the control
fow window and then we can configure
this one so our First Source uh it will
be the MySQL Source okay so we can use
the MySQL component so we can type MySQL
and it will show us the components from
the devart so I have devart install on
my machine so that's why uh I can see
different components here so I can
simply use the MySQL Source component to
into the data flow task which can
actually F data from MySQL so I have
MySQL install on my machine this is my
SQL workbench and I got a customer
database and in the customer database I
got few tables so if you want to for
example see data from the MySQL so we
will be using the two tables here uh the
orders table which contains some data
for different orders and then there is a
customers table which contains data for
different customers okay so we will be
actually uh pulling the data from two
different tables orders and customers
based on a inner join on the customer ID
and we will be selecting data from two
different tables so if I show you the
data so this particular data we will be
fetching from the MySQL so to connect to
the MySQL we can simply use the dver
component from MySQL so we don't need to
write any like any line of code we can
simply just make a connection to the
MySQL using the dver MySQL Source editor
so there is an option MySQL connection
so we can simply create a new Connection
Manager here and we don't need to write
any single line of code and we just need
to provide the connection string and
that's it and it can actually F the data
from the MySQL site so here we need to
just provide some basic details like the
server name so I can go to here and from
the database I can click on connect to
database and I can get the host name
from here uh which I can provide here
and then I need to provide the
username uh so the username for the
MySQL is root I can copy this one paste
it here and then I need to provide the
password for the root user so I have
saved my passwords at the key pass so I
can provide the password Here save my
password and then I can click on the
drop down so it will displ all the
available databases in the MySQL so we
we are interested in the customer
database so I selected this one and then
I can click on test connection so the
test connection is successful so that's
good so I can click okay so we have
successfully made a connection to the
MySQL uh in simp in just simple clicks
and now on the left side I can see the
customer database and if you expand this
one so I can see all the different
tables from the customer database for
example if you want to see the data in
one of the table so you can simply drag
and drop the table into the query window
and it can automatically generate the
select statements for that particular
table if you want to preview data so you
can click here and you can preview data
from without running the package you can
simply preview data here as well so I
can close this one so I want to uh
select the data uh using the inner join
query so I can copy the query from here
and I can paste the query in the query
window and if I click on prev so I can
still see the data here so that's good
so I can click close And I can click
okay so we have configured one of the
source here which is the MySQL Source
now we need to us select data from
another source which will be the
Salesforce source so uh I have an
account on the Salesforce and if you
want to for example select data from
Salesforce so we can use the developer
console app so I can click here
developer console
and here we can write the SQL queries uh
to select the data okay
so uh let me copy the query like that I
will use so I will use this query to
select the data from the contact table
we have different tables here in the uh
Salesforce site so if I click on execute
so it will actually execute the query on
the Salesforce site so we are selecting
only 10 records from here if you need to
select more records or less records so
so you can just change the this query
option here so if I Pro change the query
to five so it will return only the five
records okay so that's fine so uh now we
want to F the data from the sales force
or from the ssis package so we have a
component so I can type Salesforce here
and it will display as the component
from the devart so we can simply drag
and drop the component into the data
flow task and then we can simply
configure this one we need to make a
connection to this
Salesforce so uh so that it can connect
and it can f the data from any table
that you
want so now we just need to provide all
the details so that it can make a
connection to the Salesforce so I can go
back again and there is an I can go to
edit entry uh so this URL you will get
once you will create an account on the
Salesforce site so you will get this URL
and then you need to provide the your
user name password and the security
token as well so if you an account on
the Salesforce then you should have all
these details so let me copy the
security token from here and then I can
actually get the uh username and the
password so I can copy the usern name
and I can provide the details here and
then I can actually copy the password as
well and I can provide the password so
these details you need to provide and
then I can click on test connection so
the connection is successful so that's
good so I can click okay so we are able
to make a connection to the Salesforce
now so on the left side you can see all
the objects from the Salesforce suppose
if you want to fet data from any object
so you can click on that one and you can
also view the columns in that particular
object and same way if you want to
preview data from any table so you can
simply drag and drop into the query
window and you can click on preview so
this will actually fze the data from
that particular table okay so for sales
Source I want to execute this query I
want to fetch only 10 records only
selected columns so I can paste the
query here and I can preview data so it
will display only the required columns
and only 10 records so I can click okay
so this way we have actually configured
two sources mysl source and the
Salesforce source to F the data from
these two sources now you also want to
fet the data from post SQL as well so I
have PG admin 4 tool and I have
postgress SQL installed here right now I
have two databases postgress and the
e-commerce so in the postgress database
in the inside the public schema I got
four different tables here so inside
these four tables we got some data that
we can use to F data from from the
postgress so we will be using the
sellers and the seller orders table so
the seller table contains the data for
different sellers like this one and same
way there is some data from the seller
orders as well like order
ID so we can actually use this
particular query to join these two
tables together and it can f data from
both the different tables like some
columns from one table and other columns
from different table so now to connect
to the post G SQL we can simply drag and
drop the components from dver we don't
need to like write any kind of code on
need to install any driver something so
we can simply type postgress and then we
can use the postgress equal Source here
now same way we can just right click
edit and then we can make a new
connection here so that it can connect
to the POG
SQL and now uh we need to provide all
the details like the server name
username password all the basic details
like those we provided for the MySQL so
for getting the user uh server name we
can right click on the server name
properties from the connection we can
get the server name from here we can
provide the server name and then uh we
can check the port number so port number
is 5433 so we need to provide the port
number here
5433 and then we need to provide the
username so I can provide username here
then we need to provide the password
Here save my password then we can click
on the drop down so it will show you the
the databases so our source tables are
inside the postgress database so I can
select this database test connection so
the connection is successful so I can
click okay okay on the left side you can
see all the different tables inside that
particular database so we got four
tables if you want to preview data from
any table so you can see the data here U
but I want to select data based on this
particular query so I can provide write
my query here and I can preview data so
these these are the columns and data
that will be fed when the package will
run so I can click okay so so far we
have configured three sources now the
fourth Source will be the Zoho book
source so I have my account on the Zoho
books and if you see here so it contains
data for different items like we got
data for different customers then we got
data for invoices so for
invoices uh these are the records like
five records for the invoices and the
invoice status is paid for three records
for one it is draft and for another one
another one it is overdue so we will be
fetching the data from the invoice table
so for connecting to the Zoho books we
can type Zoho books here and there is a
component for the Zoho books so we can
just drag and drop the source component
for the Zoho books and we can configure
this one so we can make a connection
here so a new connection that will will
be connected to the Zoho books account
so we just need to provide some simple
details and then we should be good to
connect so here from the data center I
can select the Europe because my account
is using this data center and I simply
need to click on the web login so it's
trying to uh connect to my account I can
click
next and then I need to just click on
accept because it want to se it want to
get the data from
the from the uh Zoho books so the
connection was successful I can click on
test connection so the connection is
successful so I can click okay okay now
from the left side you can see all the
different tables here if you want to
select data from any specific table so
you can simply drag and drop the table
into the query window and you can
preview data then it will actually
display the data from that particular
table for different for all the columns
so you can see that there is a lot of
Records data available in that
particular table but I'm interested only
some specific columns so I want to
select the data from the invoices table
so I can paste the query here and I can
click on the preview data so this is
displaying the data for five orders from
the invoice table and the in status of
different orders is this one like paid
or overdue or draft okay so I can click
okay so this way we have actually uh
configured the four different sources
okay and now what we will do we will
actually combine the data from first two
sources into one different output and
then we will combine data from these two
sources into one different output and
then we will finally combine all the
data together and then we'll insert data
based on some conditions and after some
Transformations into the post G SQL and
into the MailChimp so we want to combine
the data into one different output so
for combining the data uh we can use the
merge join in ssis so I can simply drag
and drop the merge join transformation
but before using the M join we always
need to sort data so that's why we need
to use the sort transformation here so I
can simply drag and drop the sort
transformation into the data flow
task okay so we can actually combine the
datab based on this similar column so if
you see here uh we got order ID we got
email here and then on the right side uh
we got the email so it seems like we can
uh join the database on the email so we
can we need to sort the data on the same
column so I can select the email from
here click okay same way I can uh sort
the data based on the email from the
right side as well now I can connect the
S transformation to the merge joint and
it will be used as the left input so
similar to the uh joints in the SQL
Server Like We have inner joint left
joint full full outer joint same way we
have the joints in the ssis and this the
left table will be used as the like
MySQL will be used as a left table so I
can click okay now from the right side
by default it will be used as the right
table so I can connect the S
transformation with the merge joint and
then I can just simply configure this
one so the join type will be inner join
and it has automatically
uh have done it join on the email column
because we sorted data based on this
particular column now we want to select
all columns from the left table so that
in the final output all columns will be
used from the left table however from
the right table I want to add for
example um like the phone then title
then account ID and then this one
assistant name so so all these columns
like the sort one these four columns I
have added from the right table so I can
click okay so we have um now we have
combined the data from two different
sources into one different output same
way we want to do for the uh post G SQL
and for the Zoho books as well so I can
use
a sort transformation again because I
want to use a merge join so I can just
simply use the two s transformation and
then I can use the merge join
transformation so if you look at the
data here so we got the order ID Here
and Now on the another one we got the
order ID here as well so it means we can
join based on the order ID column so I
can sort the data based on the order ID
okay and from the right side I can also
join the data based on the order ID so I
can click okay now I can connect the S
transformation with the merge join so
this will be used as the left output
left input and this one will be used as
the right input now I can double click
on it so it's saying that both inputs of
the transformation must contain at least
one sorted column and those columns must
have matching metadata so we have
actually uhu we have sorted the data
based on the order ID so it means that
their metadata might be different so if
you want to check the metadata for these
columns so
you can double click here and then you
can check the metadata so for order ID
the data type is dd4 which means it's
in32 and now if I click the metadata for
the order ID so it is dtore wstr which
is nare 255 so they should be exactly
same either they should be nare or they
should be integer so let me convert it
to integer using the data conversion
transformation so I can use a data
conversion transformation and I can
change the data type of the uh order ID
okay so I can connect the Zoho books
with the data conversion transformation
and then I can select the order ID now
from the data type I can use 4 byte sign
integer so it created a new column copy
of order ID and then I can connect the
data conversion with this sort three
transformation and here instead of the
order ID column I can use the copy of
order ID so now the data type of copy of
utor ID is same whatever is the data
type for the first one so now we should
be good now so I can double click on it
and the joint type will be inner join
here so that because I want to select
only the matching data from both the
tables so you can see that now the
joining join is based on the order ID
and on the copy of order ID so I want to
select all columns from left side and
from the right side I want to uh select
like invoice number then date status so
date will be the invoice date so I can
type invoice I can name the column as
invoice date and then I can name the
column as invoice
status so we are selecting the three
columns from the right side invoice
number invoice date and the invoice
status so I can click okay so now uh
from the two different from the four
different input now we actually created
two different output okay now we we want
to combine these two input uh with a
single output okay so to combine the
data we will again use the M joint
transformation so I can simply drag and
drop the mge joint transformation into
the data flow task now if you look here
on the left side we were using the uh
email column and on the right side we
were using the order ID column so now we
need to combine the data based on a
similar column so we have a similar
column order on the left side as well uh
because if you see here we have order ID
here and then on the right side we have
the order ID here as well so it means
like we can join the database on the
order ID so for that particular thing
the order ID should be um the data
should be sorted based on the order ID
so on the right side it is already
sorted based on the order ID so we don't
need to sort here again but on the left
side it was sorted based on the email so
we need to sort it based on the order ID
so that's why uh we need to use a sort
transformation on the left side so I can
simply drag and drop a sort
transformation here and I can sort the
data based on order ID click okay now
this will be used as the left input
source now from the right
side from right side this will be used
as the right source and the joint type
we will use at inner joint
and now we will select all columns from
left side however from the right side we
can select the like seller ID then
seller name we can use invoice number
invoice date invoice status so these
columns we have selected from the right
side so I can click okay so now data is
combined into one single output okay and
now uh I want to add some additional
columns into the data flow for example I
want to add the current dat time I want
to add the machine name I want to add
the username so for doing that
particular thing for adding some
additional columns we can use the dried
column transformation so I can simply
drag and drop the drive column
transformation and then I can configure
the dri column transformation here to
get uh to get the current date time we
can use the get dat function uh from the
ssis and I can call it as created and
now to get the machine name we can use
the system variables and there is a
system variable machine name that I can
use here and I can call this particular
column as machine name same way I can
add use a system variable
username the username can be used to uh
get the name of the user who is running
the package so I can call it as user
name now if you look at the data type so
the data type of the First Column is n
15 however the data type of second
column is and worker 18 but what I want
is that I want the final uh data as
verer 50 okay so I want to do some data
type conversion so for type cast we have
some inbuilt functions here so I can use
this one
dtore St Str and I need to provide the
length here so I can provide the length
as 50 and the code page I can provide is
1252 so this will convert the data type
to workare 50 same function same data
type I can give for the second this
third column as well so we have added
three additional columns here in the
data flow and change the data type here
so I can click okay so now we have used
the drive column
transformation now after that we can use
a multicast transformation because we
want to um send our data to multiple out
but like we want to send the data to
post gra SQL as well and as well as to
MailChimp so we we can use a multicast
transformation so I can type multicast
here and I can simply drag and drop the
multicast transformation into the data
flow task and I can connect the drive
column with the
multicast now after the multicast we can
use a conditional split transformation
because uh if yeah there is one
additional thing that in the post SQL
database uh we want to insert the data
like if you see this one we want to send
the data to the POG SQL database so I
had two post equal database the first
database it contains the source table
however the e-commerce database this
contains the destination tables so if
you expand the schemas so in the tables
we got four tables here so I can type
query tool so we have like paid orders
table pending orders other orders phone
list so these four tables they will be
actually populated from the data
from four different sources so right now
in inside the paid orders table we don't
have any data same way the pending
orders table this is empty as well the
other order this is also empty and if
you look at the phone list table so this
is also empty so all four tables are
empty and they are at the post gar SQL U
System inside the e-commerce database so
I want to write the data into these four
tables so based on some condition we
want to split the data across these
tables so for the first three table we
will use a conditional split
transformation to split the data based
on the order status and for the phone
list we will actually insert all data
all the phone numbers along with the
customer name phone number customer ID
into this particular table so from the
multicast we will use a conditional
split transformation so that the data
can be splitted according to the
condition so I can use a conditional
split transformation here and then I can
connect the multicast with the
conditional split transformation and
then I can double click this one and I
can write a condition here so my
condition will be according to the
invoice status so we got a column
invoice status so I can drag and drop
into the condition here and I can write
a condition like if invoice status equal
to paid then it will be sent to the paid
orders okay same way if the invoice
status
is
overdue then it will be sent to
the pending
orders and now all the different all the
default all the other remaining orders
they will be sent to the conditional
split default output and then they will
be inserted to the other orders table so
I can click okay and now we need to use
the POG SQL destination so that the data
can be written into the different post
equal tables like this will be called as
U paid
orders okay and I can copy and paste
this one now this will be called
as pending
orders and then the third one this will
be called as
other
orders now I can connect the first
conditional split with the paid orders
and from the output I can select paid
orders for this one I can select the
pending orders and for third one it will
select the other orders okay so we have
uh connected the the conditional split
transformation with the post SQL
destination however we have not created
a connection to the post G SQL for the
Commerce database because the first
connection that we created that was
connecting to the postgress database
however our source database and
destination database they are different
so we need to make a new connection so I
can right click click on new connection
and now here we need to make a
connection to the e-commerce database
onto the post G
SQL so this will display us the
connection manager and from here I can
select the
devart uh post gra SQL I can click add
and again this will display as The
Connection Manager pin where we need to
provide the like the server name all the
details for port number so I can go go
back to the post G SQL right click here
properties uh
connection the server name is this one
Local Host and then the port number is
5433
now the username I can copy from
here and then I need to provide the
password see my password now from the
database I can select the e-commerce
database test connection so the test
connection is succeeded I can click okay
okay now I can just rename this
particular Connection Manager
to e-commerce so that I I can easily uh
figure out like this is the e-commerce
postgress connection
okay so now I can configure the paid
orders so from The Connection Manager I
can select the e-commerce Connection
Manager from component properties uh
here I can select the table name so the
table name should be paid orders for
this one I can go to the column mappings
so the input columns have been M with
the destination columns click okay so we
have configured paid orders same way we
need to configure the pending orders
uh from the table name we need to select
the pending orders okay so it will do a
bul bulk insert operation here okay from
the mapping this is good I can click
okay now I need to select the other
orders same thing Ecom uh e-commerce
postgress equal then I need to select
the component
properties from here uh we need to
select the other
orders okay go to column mappings all
input columns are mapped here
automatically okay so I can click okay
so we have configured like um we have
make made a connection to the four three
different tables however there was one
additional table as well um that's
called as phone list phone list will
contain all the phone numbers of the
different customers so for that one we
can uh take an output from the multicast
and can directly insert the data into
the phone list so I can use another post
SQL destination here and I can call it
as phone list and I can connect the
multicast with the phone list so from
The Connection Manager I can use the
e-commerce postgress SQL from component
properties I can select
that phone list option yeah this table
column
mappings and click okay so we have just
um ma the data to insert insert data
into the post SQL database now we also
need to insert the data into the
MailChimp as well so I have an account
on the MailChimp U let me log to the
account and right now it contains only
two records so the MailChimp is used to
send the emails to different you know
customers so if you go to for example
audiences so in inside the all contacts
you can see the list of contacts like
list of email addresses so right now I
have only two email addresses Akil 33
and for this one Sophia Jackson so I
have only two records available right
now okay so I want to insert the data
into the MailChimp from the ssis package
so we can use the MailChimp component
from devart so we have components for
different like like for a lot of
different sources and for a lot of
different destinations we have the
components so you simply need to just
drag and drop the component into the
data flow and you can use them you just
need to make a connection to that
particular source and just configure it
and that's it you don't need to write
like any quote in cop or something so
now I can actually make a connection to
the MailChimp right click new
connection so this will actually open
The Connection Manager pen where you can
select the devart MailChimp Connection
Manager and then you can just provide
the details here so I can select the
dart MailChimp and then I need to click
on ADD so this way it will open a
Connection Manager pin where I think we
simply need to log to our account so
that it can actually verify that the
account is correct so we can click on
web login and
then uh we need to provide the username
and password
login so now it's saying like do you
want to allow the devart ssis component
to access your account so I can say
allow because we we want to insert the
data into the mail Chim so authorization
is successful so that's good I can go
back and I can test the connection so
the connection is successful so that's
good news I can click okay so now we
have created a connection manager for
the MailChimp as well so that's good now
I can connect the multicast
transformation with the MailChimp and
then I can configure the MailChimp here
from The Connection Manager I can use
the dver ma Chim Connection Manager from
the component properties from the object
name there is a list members table
actually the list members table contains
the email addresses of different people
now we can go to the column mappings and
inside the column mapping we need to
actually map The Columns the most
important column is is the email ID uh
that will be used to send the email to
different persons and now we can map the
other columns like the first name so we
can M first name here last name because
there is a space on the last name on
right side and there is no space on left
side so that's why it did not get mapped
automatically so we can manually map
them as well now we can map the phone
number as well so so this is phone
number and there is a full name so we
can map the customer name so the the
important columns are maed now there are
two important columns uh there is a
MailChimp ID and there is a list ID so
this list ID this will be unique for all
the records and this ID this should be
you the list ID this will be actually
same for all the records so when you
create an account on the mail Chim so
you can use the dart component from uh
deart component and you can use the
source component to fetch the data from
the mail chimp so as soon as you will
fet the data from the mail chimp and
will insert the data into a SQL Server
table or into a CSC file so you can
actually see the value for the list ID
same way you can actually see the values
for the ID like for the existing like
right now I have to uh two email ID on
the mail Chim so if you f the data into
a database so you can actually see the
values of the ID and you can see the
value of the list id id should be unique
and list ID can be same for all the
records okay so we need to so at the
time of inserting the data to the mail
chimp we need to provide the values for
these two column as well so to add the
values to these two columns I can add a
drived column transformation in between
and uh I can add two extra columns one
for ID and another one for list ID so I
can use the dried column transformation
here
so this one and I can connect the multic
class with the dri column and then I can
add two new columns here so the one
column will be the MailChimp
ID so I can simply copy and paste this
one so if you see here like the first
few column these first few characters
this will be same however we are
appending the order ID and Order ID will
be unique for every record so this will
make a unique ID okay so I can call this
particular column as ID and then the
second column will be the list ID so
this will be same for all the requests
so you can check like you can you can
use the component and you can get data
from um MailChimp so you can easily
figure out like what is the list ID for
your account so for my account this is
the list ID that I will be using so I
can call this particular column as list
ID so you simply need to add these two
column and now we are good so I can
click okay and then I can connect the
drive column with the devart MIM
destination I can configure this one and
then I can go to the column mapping now
from the for the ID I can map the idid
column and same with I can map the list
ID column as well so we should be good
it can insert the data for email and
then for full name then first name last
name phone number all these details okay
so at my MailChimp account I only two
records I can again refresh the
data so that you can see that there were
only two records here so it should
ideally insert five additional records
and you can insert as many records as
you want so
these are the column names so so our
ssis package is ready and uh it can be
executed so before executing the package
we can actually save it okay and then we
can actually build the ssis package so
that it can actually the build is
required uh before running the package I
think it can help us because if you want
to for example deploy the package to
some different server or something so we
can actually build the project so as
soon as the project will be built then
we can run the package and yeah so here
if you see we use the four different
sources from devart we did not return
any single line of code and we simply
just uh made some connection uh to
different sources like to mySQL we
simply provided the server name username
password port number same way we
provided the details for the postgress
SQL as well and for Salesforce we
provided like the host name username
password and the token which is
available when you create an account on
the Salesforce and on Zoho books it was
so simple that we just simply uh you
know verified our account okay so the
build has completed successfully so
that's good news so now we should be
good to execute the package so when the
package will run it will F data from
four different sources so from first two
sources it will actually combine data
will make one output then from these two
sources it will combine data will make
one output then it will again combine
data into single output then it will add
three additional columns here then using
the multicast it will insert the five
records into the phone list table on
post G SQL and then using the
conditional split transformation
according to the data in the database so
like for three records here if you see
the status the status of three records
is paid so three record should be
inserted to the uh paid orders table so
right now the paid orders table is empty
however the three record should be
inserted Here and Now One record should
be inserted to the pending orders and
one record should be inserted to the
other orders table and in the phone list
table all five record should be inserted
to the phone list table and on the mail
Chim side all five record should be
added here okay so yeah we are good to
run the package so let's click on the
start button so this should actually U
Pull the data from four different
sources and then after some
transformation it should insert the data
into the post G SQL database and into
the
MailChimp so finger
crossed so right now it's trying to uh
connect to the different systems and
trying to get the data so yeah so the
package is running so it has actually
inserted the data into the post G SQL
here it inserted as expected three three
records it inserted to the paid orders
table so you can go back to the database
and can check the data inside the paid
orders Table Three records are inserted
and these are the paid orders so you can
check the status here that the invoice
status is paid now one one record has
been inserted to the pending orders so
you can go to the pending orders and you
can check the data here so the invoice
status is overdue here one record got
should be inserted to the other orders
so you can check the data here as well
that it's draft and inside the phone
list you should see the five records so
here is the customer customer ID their
name phone numbers and this is the
current date time when the package was
executed this is machine name and this
is the username so it inserted the data
into the post G SQL and into the
MailChimp as well if you open the
MailChimp so you should see the five
additional email IDs here so if I
refresh the uh my web page then I should
see the five additional records here so
you can see five additional email IDs
here so yeah so it's so simple that
Within few clicks you can easily write
your code in the ssis so for example if
you are coming from a background uh
where you don't know like maybe cop or
something or even if you know cop then
it can take you several weeks to several
months to write the code into you know
write the code to access data from
different sources and to yeah we have
spent us you know so much time to uh you
know provide you better option here so
even you can use the variables here so
if you have like some user defined
variable or some system variable so you
can use the variables inside the query
as well to make the query Dynamic you
know yeah so um yes so this was the demo
like how you can use the different
components so we have 50 plus components
so you can
simply read data from any source and you
can write data into any destination so
yeah so yeah that was for this
particular
demo great thank you so much ail it was
really great that we took all of this
data and put in different
tables all right then so thank you so
much once again so your attendees please
write your questions in the chat if you
have them we will be answering by the
end of the presentation and now we have
a few more slides for you let me start
presenting give me a
second okay so that's the my screen and
it's time to talk about the pricing and
Licensing right now how to license the
as SAS
components so there are two main pricing
models available of course that's the
perpetual and subscription Perpetual you
may once have a lifetime license of the
product with this option you get one
year of support and maintenance and
upgrades free of cost and uh you can
extend this support for up to three
years or even more if it is required for
your project or Tender so let us know
and the subscription provides you one
year of access to the product with
support and upgrades includ it of the
entire subscription period so to
continue using the product you must
renew your subscription annually and the
difference of course the Perpetual will
be still valid but uh even if the
support and maintenance is expired but
if you would like to get an update of
course you have to renew your license to
get the latest version of our product
additionally there are three additions
to choose from so Developer Edition
ideal for individual developer or small
teams server Edition best for Server
based deployments and larger team or
Enterprise environment and the site
license a complete solution for
organization ations needing a wide scale
deployment across multiple
sites for those looking for extra
support but also offers priority support
this optional an addon provides faster
response time and direct access to
senior support Engineers for any
critical issues you may encounter so if
you have any questions about pricing
models which like or any addition is the
best fit for you so you and basically
for your needs feel free to write your
questions in the chat and we'll be glad
to answer
them so moving
next so why choose D yeah so let's
discuss the key advantages of choosing D
for ssis needs comprehensive support so
with DT you have access to a dedicated
support team that knows the ins and outs
of every DT product whether you're
facing technical challenges or need
guidance their team is ready to assist
you with expert level knowledge regular
updates uh so de is committed to
constantly improving this product so
you'll receive frequently updates
frequent updates that enhance the
functionality and performance of your
ssis
product many connectors uh so one of
major strengths of dver SSS components
is a fast range of connectors dver
regularly adds new components for
emerging data sources ensuring you have
all the tools for the fast for the
latest
platforms and of course detailed
documentation theart also offers
comprehensive documentation to support
you every step of the way from
step-by-step guides to tutorials uh to
detailed examples you'll have all the
resources to implement and optimize your
SSA Solutions efficiently when you
choose de you get a complete reliable
solution with ongoing support and
enhancements
and thank you so much for all all you
for participation and engagement
throughout today's webinar we hope
you've gained valuable insights to help
your data integration Journey stay
connected with us for more resources
updates and support as you continue
working with ssas and dver ssas
components now we are opening up to a
Q&A session so feel free to ask anything
you want we will try to to reply to any
questions you might
have and of course you will get the
recording of This webinar and you can
contact us at sales de.com if you have
any sales questions or Partners at d.com
for
partnership okay I see that we have no
questions so far thank you so much again
uh for being with us today let's stay in
touch and have good rest of the
day bye-bye

Video Length: 55:33
Uploaded By: Devart
Published: 11/6/2024
View Count: 240

Related Software Products
SSIS Data Flow Components for PostgreSQL
SSIS Data Flow Components for PostgreSQL

Published By:
Devart

Description:
SSIS Data Flow Components for PostgreSQL: PostgreSQL Source, Destination and Lookup for SSIS Devart SSIS Data Flow Components for PostgreSQL add a PostgreSQL Source, Destination, Lookup and Connection Manager to SQL Server Integration Services, for $179.95 a year (Developer license) or $349.95 perpetual. They connect straight to PostgreSQL 8.0 through 18 with no client library or ODBC driver, and the Destination bulk-loads with PostgreSQL's own...

Copyright © 2026, Ivertech. All rights reserved.