0

I'm importing two data sources. The problem is that one data source has a date, the other does not. Everything in the data flow has the same date and is inserted as one transaction into the same destination table.

Source A: [Calendar Date], [Item Number], [Units Sold]

Source B: [Item Number], [Inventory Units]

Final Output: [Item Number],[Calendar Date],[Units Sold],[Inventory Units]

Source A & B always refer to the same date. Therefore I want MAX([Calendar Date]) applied to every row in the final output. I just can't figure out how to do that in SSIS

Update: Possible Solution?

So what I did was add a temp id of 1 to every row following the merge of A&B. I then split the data, grouped by the MAX(DATE) which in this case is the only date. Then I joined back both sides on 1=1 thus giving each row the single value of MAX(DATE). Is this the recommended approach?

enter image description here

8
  • What is the PK on both sources? A: ([Calendar Date], [Item Number]), B: ([Item Number])? Is there a 1:1 relationship between A and B? – Oliver Rahner Sep 25 '14 at 19:02
  • In this case item number is the PK. However I am doing a full outer join because items without sales are suppressed from source a, and items without inventory are suppressed from source b – ElPresidente Sep 25 '14 at 19:08
  • Ah, so SourceA would show units sold over time. Source B shows RIGHT NOW. You need to only pull in what was sold for the last date and then pull all the inventory and mash that together where possible – billinkc Sep 25 '14 at 20:58
  • Both are daily and both are for the same day. The only issue is that Source B does not provide a date (though I know it's for the same day as A). Therefore after the join, some products have NULL date values because they don't exist in A. – ElPresidente Sep 25 '14 at 21:06
  • Excuse me but it's still not clear to me. So all rows in Source A have the same date? – Oliver Rahner Sep 25 '14 at 21:34
1

I took a different approach and well, mine's going to be a bit easier to maintain. ;)

I have created a Variable named SourceDate. I'm going to run a query against my Source A table and derived the maximum date in there. Due to data type quirkiness, even though I used a date datatype, SSIS 2008 freaks out and thinks I'm changing data type so I explicitly cast it to datetime

My control flow looks like the following.

enter image description here

SQL Derive MAX date

I return a single result set and assign into my variable ordinal position 0. This gives me the thing I need to ensure I'm only pulling current data out of table A and since it's the same for all values, it'll used after the merge join.

SELECT
    CAST(MAX(SA.CalendarDate) AS datetime) AS CalendarDate
FROM
    dbo.SourceA AS SA;

In my data flow, I'm going to use two OLE DB Sources. If this were all on the same source, I'd have you craft the appropriate query there and be done with it but this approach allows you to use two different servers and/or RDBMSes.

Source Query A

Since I like to make things complex, I assumed that Source A could have many days worth of data in there but we only wanted the most recent. Therefore, I'm going to use a parameter of ? and map that to my Variable @[User::SourceDate]

SELECT
    SA.ItemNumber AS ItemNumberA
,   SA.UnitsSold
FROM
    dbo.SourceA AS SA
WHERE
    SA.CalendarDate = ?
ORDER BY
    SA.ItemNumber ASC;

Source Query B

SELECT
    SB.ItemNumber AS ItemNumberB
,   SB.InventoryUnits
FROM
    dbo.SourceB AS SB
ORDER BY
    SB.ItemNumber ASC;

The important thing is that for a Merge Join to work, you must have sorted data. Sort in your database. It has indexes which might mean the data is already sorted for you. But, when you do that, you have to tell SSIS that this data is sorted. You do this by going to the Advanced tab after right clicking on the OLE DB Source component. You mark the Output as Sorted and then you need to identify the SortKey position. Since I'm sorting on ItemNumber I will put a value of 1 there.

In my source queries, note that I'm renaming ItemNumber. After the join, I don't know if my Left, right or Both sources had an ItemNumber.

I merge both data sets together based on the ItemNumber matching and use a type of Full Outer Join

I then add in Derived columns.

The first adds my Variable @[User::SourceDate] as a column called CalendarDate.

The second derived column helps me render a single column called ItemNumber I use an expression to pick the first non-null version of my two columns ItemNumberA and ItemNumberB

ISNULL([ItemNumberA])? [ItemNumberB] : [ItemNumberA]

My Data Flow ends up looking like

enter image description here

Database setup

I spun up a little database with some data to demo against. Nothing fancy here.

CREATE TABLE dbo.SourceA
(
    CalendarDate date NOT NULL
,   ItemNumber int NOT NULL
,   UnitsSold int NOT NULL
);

CREATE TABLE dbo.SourceB
(
    ItemNumber int NOT NULL
,   InventoryUnits int NOT NULL
);

-- add 3 values, 2 for today, one for yesterday
INSERT INTO
    dbo.SourceA
(
    CalendarDate
,   ItemNumber
,   UnitsSold
)
VALUES
    (CURRENT_TIMESTAMP,100,10)
,   (CURRENT_TIMESTAMP,200,20)
,   (DATEADD(d, -1, CURRENT_TIMESTAMP),100,10);

-- Add inventory. Something for items sold + something not sold
INSERT INTO dbo.SourceB
(
    ItemNumber
,   InventoryUnits
)
VALUES
    (100, 30)
,   (200, 10)
,   (300, 7);

Biml

Biml, the Business Intelligence Markup Language, describes the platform for business intelligence. Here, we're going to use it to describe the ETL. BIDS Helper, is a free add on for Visual Studio/BIDS/SSDT that addresses a host of shortcomings with it. Specifically, we're going to use the ability to transform a Biml file describing ETL into an SSIS package. This has the added benefit of providing you a mechanism for being able to generate exactly the solution I'm describing versus clicking through many tedious dialogue boxes.

So, install BIDS Helper if you haven't already. Run my table create scripts. Fix the Connection String to point to wherever you created the tables and Whoosh, you have my solution. Adapt for your actual table and servers.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection ConnectionString="Provider=SQLNCLI11;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=tempdb" Name="CM_OLE" />
    </Connections>
    <Packages>
        <Package Name="dba_77666" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="DateTime" Name="SourceDate">2014-01-01</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL ConnectionName="CM_OLE" Name="SQL Derive MAX date" ResultSet="SingleRow">
                    <DirectInput>
SELECT
    CAST(MAX(SA.CalendarDate) AS datetime) AS CalendarDate
FROM
    dbo.SourceA AS SA;
                    </DirectInput>
                    <Results>
                        <Result Name="0" VariableName="User.SourceDate" />
                    </Results>
                </ExecuteSQL>
                <Dataflow Name="DFT Make Data">
                    <Transformations>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC SourceA">
                            <DirectInput>
SELECT
    SA.ItemNumber AS ItemNumberA
,   SA.UnitsSold
FROM
    dbo.SourceA AS SA
WHERE
    SA.CalendarDate = ?
ORDER BY
    SA.ItemNumber ASC;

                            </DirectInput>
                            <Columns>
                                <Column SourceColumn="ItemNumberA" SortKeyPosition="1"></Column>
                            </Columns>
                            <Parameters>
                                <Parameter Name="0" VariableName="User.SourceDate" />
                            </Parameters>
                        </OleDbSource>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC SourceB">
                            <DirectInput>
SELECT
    SB.ItemNumber AS ItemNumberB
,   SB.InventoryUnits
FROM
    dbo.SourceB AS SB
ORDER BY
    SB.ItemNumber ASC;

                            </DirectInput>
                            <Columns>
                                <Column SourceColumn="ItemNumberB" SortKeyPosition="1"></Column>
                            </Columns>
                        </OleDbSource>

                        <MergeJoin JoinType="FullOuterJoin" Name="MJ Mash all">
                            <LeftInputPath OutputPathName="OLE_SRC SourceA.Output" />
                            <RightInputPath OutputPathName="OLE_SRC SourceB.Output" />
                            <JoinKeys>
                                <JoinKey LeftColumn="ItemNumberA" RightColumn="ItemNumberB"></JoinKey>
                            </JoinKeys>
                        </MergeJoin>


                        <DerivedColumns Name="DER Add Calendar Date">
                            <Columns>
                                <Column DataType="Date" Name="CalendarDate">@[User::SourceDate]</Column>
                            </Columns>
                        </DerivedColumns>

                        <!--
                        We have two ItemNumbers, One of them is populated, possibly both.
                        Create a single column that has the ItemNumber
                        -->
                        <DerivedColumns Name="DER Unified ItemNumber">
                            <Columns>
                                <Column DataType="Int32" Name="ItemNumber">ISNULL([ItemNumberA])? [ItemNumberB] : [ItemNumberA]</Column>
                            </Columns>
                        </DerivedColumns>
                        <!--  
                        Perform any NULL handlings here
                        -->

                        <DerivedColumns Name="DER Fix outstanding nulls">

                        </DerivedColumns>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Update

With the updated information that this will be coming from a file source, the package will change in 3 places.

The first will "SQL Derive MAX date". Instead of using an Execute SQL Task, you will need to use another Data Flow and wire the Source to an Aggregate transformation. Find the MAX date and then use the ... variable sampling thing to assign 1 row into our Variable (or use a Script Component, which is what I'd do).

If the source file that has a date could hold a mix of older and newer data, then you'd need to add a Conditional Split transformation after the data source A to filter out anything older than Max datetime derived in addendum 1 in "DFT Make Data".

Finally, you will need to add a Sort transform immediately after both of your data sources/Conditional Split in the "DFT Make Data" step.

1-2-3, solution holds true. Too lazy to update the Biml as I don't know your file definitions.

Future reader note, this does force you to reprocess one of your source files. If it's a few hundred/thousand/million rows, depending on your IO subsystem, that may not be a concern. If you have a slow IO sub, then you'll need to investigate alternate mechanism of

5
  • I think my only issue with this approach is the fact that one bad record could poison the entire well. Correct me here if I'm wrong billnkc, but I would rather take the road of grouping on Source A dates, which are provided so that if one record is bad, it only impacts one record not all. – Glen Swan Sep 25 '14 at 22:02
  • @GlenSwan Help me understand the concern. How will a poisonous record in A pooch the results? – billinkc Sep 25 '14 at 22:03
  • Sorry, misread a comment. You are applying MAX(DATE) from every source record in the flow. For some reason I read that as taking a sample from the entire data set and applying to all. – Glen Swan Sep 25 '14 at 22:05
  • Thanks. There's some extra info in here I wanna check out. I'm only fed a text file for both sources which makes it constrained. But let me review. – ElPresidente Sep 29 '14 at 17:08
  • Providing specifics in your questions can never hurt the supplied answers. – billinkc Sep 29 '14 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.