How to implement the below query using ssis

Question

Can we discuss how to load the below query result into a destination table ,using ssis.I know we can use this in T-sql and also as an OLEDB source query.But still wondering how to implement it ,only using data flow components

SELECT
     CLIENTID       = CAST(PER.CLIENTID AS INT)
    ,CASEID         = CAST(CS.CASEID AS INT)
    ,CAST(RIGHT(ev.oid, 10) as int) AS EventID
    ,ev.ServiceSubtypeCode
    ,ev.ServiceSubtypeCode  +' - '+ev.ServiceSubTypeDesc as ServiceSubTypeDesc
    ,WU.ProviderID as WorkunitProviderID
    ,WU.ProviderName as WorkUnitProviderName
    ,ev.eventstartdate as AssessmentStartDate
    ,CONVERT(CHAR(5),ASM.getstarttimestamp,8) as AssessmentStartTime
    ,ev.EVENTENDDATE as AssessmentEndDate
    ,ev.EVENTENDTIME as AssessmentEndTime
    ,CAST(asm.getAssmtTemplateName as nvarchar(200)) as AssessmentTypeDesc
    ,j.providerid
    ,j.ProviderName
    ,j.ProviderRole
    ,EV.ISCOMPLETED
    , EV.ISFINALISED 
    ,EV.ISREVOKED
    , EV.REVOKEDDATE AS REVOKEDDATE
    ,ASM.OID AS ASSESSMENTID

FROM DBO.ASSESSMENT ASM
LEFT OUTER JOIN DBO.INDIVIDUALPERSON PER        ON ASM.MYPERSON = PER.OID
LEFT OUTER JOIN DBO.[CASE] CS       ON ASM.MYCASE = CS.OID
LEFT OUTER JOIN (
                        SELECT CAST(ST.CODE AS VARCHAR(8))AS SERVICETYPECODE
                                , CAST(ST.DESCRIPTION AS VARCHAR(100)) AS SERVICETYPEDESC
                                , CAST(SST.CODE AS VARCHAR(8)) AS SERVICESUBTYPECODE
                                , CAST(SST.DESCRIPTION AS VARCHAR(100)) AS SERVICESUBTYPEDESC
                                , DATEADD(DD,0, DATEDIFF(DD,0,EV.GETRPSSTARTTIMESTAMP)) AS EVENTSTARTDATE
                                , CONVERT(CHAR(5),EV.GETRPSSTARTTIMESTAMP,8) AS EVENTSTARTTIME
                                , DATEADD(DD,0, DATEDIFF(DD,0,EV.GETRPSENDTIMESTAMP)) AS EVENTENDDATE
                                , CONVERT(CHAR(5),EV.GETRPSENDTIMESTAMP,8) AS EVENTENDTIME
                                ,CAST(VEN.DESCRIPTION AS VARCHAR(12)) AS EVENTVENUE
                                ,EV.ISCOMPLETED
                                , EV.ISFINALISED 
                                ,EV.ISREVOKED
                                , DATEADD(DD,0, DATEDIFF(DD,0,EV.REVOKEDON)) AS REVOKEDDATE 
                                , EV.OID 
                                        from Event ev
                                        LEFT OUTER JOIN     ServiceType AS st ON ev.myServiceType = st.oid 
                                        LEFT OUTER JOIN  ServiceSubtype AS sst ON ev.myServiceSubtype = sst.oid
                                        LEFT OUTER JOIN  AllCodes AS ven ON ev.myEventVenueCode = ven.oid 
                                     )as  EV
ON ASM.MYEVENT = EV.OID
LEFT OUTER JOIN     (
                                        select wu.oid
                                        ,CAST(wu.providerid AS VARCHAR(100)) AS providerid
                                        ,CAST(nm.getfullname AS VARCHAR(100)) AS ProviderName
                                        ,wu.contactname
                                        ,wu.activatedate as StartDate
                                        ,wu.deactivatedate as EndDate
                                        ,case when wu.deactivatedate is null then 1 else 0 end as IsActiveToday
                                        from workunitprovider wu
                                        LEFT OUTER JOIN  dbo.allprovidernames nm    ON wu.oid = nm.myprovider
                                        where nm.myNameType in (02245.0000000252)
                                    ) as WU
ON ASM.MYWORKUNITPROVIDER = WU.OID
Left join   (                   
                                select f.myEvent
                                ,f.myProvider
                                ,f.myproviderrolecode
                                ,f.Max_ProvOid
                                ,CAST(g.providerid AS VARCHAR(100)) AS providerid
                                ,CAST( i.description AS VARCHAR(150)) AS ProviderRole
                                ,cast (h.getFullName as nvarchar (150)) as ProviderName
                                    from(           select d.myEvent
                                                        ,myProvider
                                                        ,myproviderrolecode
                                                        ,d.Max_ProvOid
                                                         from (                 select  A.myEvent, max(b.oid) as Max_ProvOid
                                                                                    from alleventitems a
                                                                                    left outer join ProviderEventItemRole as b  on a.oid = b.myeventitem
                                                                                    group by A.myEvent
                                                                        ) as d
                                                                        left join 
                                                                        (                           select  A.myEvent,b.myProvider,b.myproviderrolecode,a.oid as a_oid,b.oid as b_oid
                                                                                                    from alleventitems a
                                                                                                    left outer join ProviderEventItemRole as b  on a.oid = b.myeventitem
                                                                        )as e on d.myevent = e.myevent and max_provOid = b_oid
                                ) as f
                                left join dbo.allproviders as g on f.myProvider = g.oid
                                left join (
                                                    select * 
                                                    from dbo.AllProviderNames 
                                                    where mynametype ='02245.0000000252'
                                                )as h on f.myprovider =h.myprovider
                                left join dbo.allcodes as i on f.myproviderrolecode = i.oid 
    )as j on ASM.myevent = j.myevent;

Answer 1

Before we begin, a disclaimer:

Complex SELECT queries are best expressed in T-SQL. SSIS is best used for ETL tasks.

Now... with that out of the way. Let's see what we have. That query has fifteen LEFT JOINS nested across three levels: Five at the top, seven at the middle, and two at the bottom. Peppered throughout are a some CAST()s and GROUP BYs. All of those SQL commands can be done with SSIS components.

• JOIN = Merge Join component.
• GROUP BY = Aggregate component.
• CAST = Derived Column component.

Since you have such a large query, I'd recommend breaking this into smaller chunks. Starting with the inner most join.

    select 
        d.myEvent
        ,myProvider
        ,myproviderrolecode
        ,d.Max_ProvOid
    from (                 
        select  A.myEvent, max(b.oid) as Max_ProvOid
        from alleventitems a
        left outer join ProviderEventItemRole as b  
        on a.oid = b.myeventitem
        group by A.myEvent
    ) as d
    left join (                           
        select  A.myEvent,b.myProvider,b.myproviderrolecode,a.oid as a_oid,b.oid as b_oid
        from alleventitems a
        left outer join ProviderEventItemRole as b  
        on a.oid = b.myeventitem
    ) as e 

Translating that to SSIS would look like this.

Above, we're merging four tables into one. You can learn more about how to configure Merge Joins here. Repeat the above pattern for the remaining JOINS and connect them all together and you will have translated the entire query to SSIS!

---

Now that we can see how it may be done, may I ask why we'd want to do this in SSIS?