Databases
StackOverflow2013
Postsdbo
POIs it possible to create a Fact table with multiple sql scripts in SSIS?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs it possible to create a Fact table with multiple sql scripts in SSIS?
    primarykey
    data
    text
    <p>Let's say I have four different scripts, and all of the columns will be used to create a Fact table, if I paste these four scripts in the SQL command in the OLE DB Source, would it work? Taking into consideration that I created a table that is composed of all the columns in the four SQL scripts I used. If it's possible... then can I put four different scripts inside the SQL Command in OLE DB Source?? Would it cause confusion in the mapping section even if create all of the columns (in the four scripts) in the table that will be put in the OLE DB destination?</p> <p>SQL 1:</p> <pre><code>Select r.ResourceID, r.SubSubFunctionCode, r.SubLocationCode, r.Site, SiteDesc, t.Resourceid, sum(ItemValue1) as HoursOutstanding, sum(ItemValue1 * isnull(case when itemvalue2 = 0 then CostRateRegOper else CostRateIntOper end,0)) as OperCostOutstanding, sum(ItemValue1 * isnull(case when itemvalue2 = 0 then ChargeRateRegOper else ChargeRateIntOper end,0)) as OperRevenueOutstanding, sum(ItemValue1 * isnull(case when itemvalue2 = 0 then CostRateRegHome else CostRateIntHome end,0)) as HomeCostOutstanding, sum(ItemValue1 * isnull(case when itemvalue2 = 0 then ChargeRateRegHome else ChargeRateIntHome end,0)) as HomeRevenueOutstanding, HomeCurrCode, sum(ItemValue1 * isnull(case when itemvalue2 = 0 then CostRateRegNat else CostRateIntNat end,0)) as NatCostOutstanding, sum(ItemValue1 * isnull(case when itemvalue2 = 0 then ChargeRateRegNat else CHargeRateIntNat end,0)) as NatRevenueOutstanding, NatCurrCode from ODS_Staff_Task t, ODS_CurrentResource r where t.TaskTypeID in (5,6) --missing time and R.RESOURCEID = T.RESOURCEID and r.resourcetypecode &lt;&gt; 'C' and t.sitedesc = r.resourcesite </code></pre> <hr> <p>SQL 2:</p> <pre><code>Select SiteDesc, t.Resourceid, sum(ItemValue1) as ExpenseToApprove from ODS_Staff_Task t where t.TaskTypeID in (2) group by SiteDesc, t.Resourceid </code></pre> <hr> <p>SQL 3:</p> <pre><code>Select SiteDesc, t.Resourceid, sum(ItemValue1) as HoldsToRelease from ODS_Staff_Task t where t.TaskTypeID in (3) group by SiteDesc, t.Resourceid </code></pre> <hr> <p>SQL 4:</p> <pre><code>Select SiteDesc, t.Resourceid, sum(ItemValue1) as MonthlyReviewsOutstanding from ODS_Staff_Task t where t.TaskTypeID in (4) group by SiteDesc, t.Resourceid </code></pre> <p>Many Thanks!</p> <p>Beau</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USBA82283
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CODo you want to do it like this (I do not know if will cause confusion that way.), or would you not rather put each script in its own step to root out confusion for the next person looking at it?
      singulars
      1. POIs it possible to create a Fact table with multiple sql scripts in SSIS?
      1. USInus C
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload