Is it possible to use multiple data sources inside an SSIS package under the OLE DB connection manager?

Question

The query is used for inserting new records only.

How can I use this query? whether the sql script is inside the Execute SQL Task: SQL command task or OLE DB Source: SQL Command task when using 4 different Data sources(databases) such as database A,B,C and D.

In the Data Source under the Solution Explorer, I added four databases under one server, these are database A,B,C and D. Would it be possible to add four databases with four corresponding data source inside the OLE DB connection Manager??

OLE DB connection manager: Database/Data Source A, B, C, D <--- Is this possible??
Data Access Mode:
SQL Command

    SQL Command Text:

    Insert into DWResourceTask.dbo.DimEntity

    select 
    a.EntCode, 
    a.Name, 
    a.Active, 
    a.AccessLevel, 
    a.SiteURN, 
    a.CompanyURN,
    a.SiteName, 
    a.SiteDesc, 
    a.SiteURL
    from 
    (Select e.EntCode, e.Name, e.Active, e.AccessLevel, ss.SiteURN, ss.CompanyURN,
    ss.SiteName, ss.SiteDesc, ss.SiteURL
    from A.dbo.Site ss, A.dbo.LegalEnt e
    where ss.localsiteflag = 1
    and e.active = 1

    UNION ALL
    Select e.EntCode, e.Name, e.Active, e.AccessLevel, ss.SiteURN, ss.CompanyURN,
    ss.SiteName, ss.SiteDesc, ss.SiteURL
    from B.dbo.Site ss, B.dbo.LegalEnt e
    where ss.localsiteflag = 1
    and e.active = 1

    UNION ALL
    Select e.EntCode, e.Name, e.Active, e.AccessLevel, ss.SiteURN, ss.CompanyURN,
    ss.SiteName, ss.SiteDesc, ss.SiteURL
    from C.dbo.Site ss, C.dbo.LegalEnt e
    where ss.localsiteflag = 1
    and e.active = 1

    UNION ALL
    Select e.EntCode, e.Name, e.Active, e.AccessLevel, ss.SiteURN, ss.CompanyURN,
    ss.SiteName, ss.SiteDesc, ss.SiteURL
    from D.dbo.Site ss, D.dbo.LegalEnt e
    where ss.localsiteflag = 1
    and e.active = 1
    )a

    where not exists (select * from DWResourceTask.dbo.DimEntity w
                      where(a.EntCode=w.EntCode
                            and a.Name=w.Name
                            and a.Active=w.Active
                            and a.AccessLevel=w.AccessLevel
                            and a.SiteURN=w.SiteURN
                            and a.CompanyURN=w.CompanyURN
                            and a.SiteName=w.SiteName
                            and a.SiteDesc=w.SiteDesc
                            and a.SiteURL=w.SiteURL))

Please Help. Thanks in Advance!

Answer 1

I will elaborate on the Data Flow task for you. Hopefully this helps.

1. Drop in a Data Flow task to your workflow. Mine below is called "Load CSV to SQL RawData Table".

2. Within the Data Flow task, add your origin (Source) and destination. These can be flat files, database connections, etc.

3. Within the origin, select the Connection and columns you'd like to include.

4. Open up your destination and select the Connection and table to which the data is going to go.

5. Next, map the origin columns to the destination columns. You can do this manually, or by right-clicking and Matching on Name. That's it for that connection.

6. Repeat this process for your other Source and Destinations as part of your overall process flow.

7. Test your SSIS package, then deploy.

Answer 2

This is not what you achieve with Execute SQL Task! But can easily be done with Data Flow Task, and simply that's the main idea of SSIS - avoiding such complicated SQL expressions.

1. Create connections for each database A, B, C, D, DWResourceTask, Destination

2. Put separate data source for each database: A, B, C, D. Fill with select statements, and Union All them all.

3. where not exists part can be done with a lookup to DWResourceTask.dbo.DimEntity. No match output will give you the proper records.

4. Put data destination and direct above rows into it.

Answer 3

I created a view which looks at both sources and then the view become my single source.

I rather let SQL Server do the hardwork for me and leave for SSIS to do the data transfer ..