Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2085
  • Last Modified:

SSIS excel file load with dynamic columns

Hi!

I'm looking for expert step by step instructions on how best to code for an SSIS excel file transformation and load:

The case scenario:

An .xls file arrives with summary info in the first 6 rows which are skipped durihg table load, column labels start on row 7 with detail values in rows 8 on.

There are 2 raw tables that the data is loaded to:

(partial examples)

MediaPlanRaw
-----------------
   SurrogateKey  
   MediaPlanId
   GUID
   Clicks

ActionTagRaw
------------------
   SurrogateKey
   ActionTagName
   VBConversions
   CBConversions
   CTConversions

The first three excel file columns will be stored in MediaPlanRaw along with a derived identity column per row.

excel sample:
     A                      B                     C
MediaPlanId   GUID                   Clicks
--------------   -------------          -------
5                    c7dd0a414f0b   17

MediaPlanRaw sample:

SurrogateKey   MediaPlanId    GUID                   Clicks
-----------------   --------------    ------------------   -------
1001                   5           c7dd0a414f0a   3

columns D on, will be stored in the ActionTagRaw table.  They are dynamic 4 column sets of:

ActionTagName
 ViewBasedValue
 ClickBasedValue
 ClickTagValue

excel sample:                                              (column set is repeated n times)
     D                    E           F            G                 H                I              J            K
ATName       VBConv  CBConv CTConv      ATName     VBConv  CBConv CTConv
FY08_OCS   4             18           4                FY08_VS   5             24           6

The Action Tag column sets need to be collapsed to rows along with the derived identity value created for the respective media plan rows loaded in the MediaPlanRaw table.

ActionTagRaw sample:

SurrogateKey   ATName                   VBConversions   CBConversions  CTConversions
1001                 FY08_OCS_Trial       4                          18                       4
1001                 FY08_VisualStudio   5                          24                       18
         

Keeping this logic in SSIS, would someone be able to give clear instructions on a solution to this task?
0
ssebring
Asked:
ssebring
1 Solution
 
reb73Commented:
There might be other solutions, but I'd suggest creating a staging table dumping the entire contents of the Excel data (from row 7) into this staging table initially.. Do not use the Excel Field Names for repeating values (ATName, VBConv etc), instead shadow the Excel column names (E, F, G) which will be unique across this staging table

Then you could initially insert into MediaPlanRaw table and use the GUID column to obtain the surrogate key in MediaPlanRaw  (join on staging table) and populate ActionTagRaw  in a series of inserts as follows -

--Pass 1
INSERT ActionTagRaw
SELECT MPR.SurrogateKey, ST.D, ST.E, ST.F, ST.G
FROM stagingtable ST
INNER JOIN MediaPlanRaw MPR on MPR.GUID = ST.GUID
WHERE COALESCE(ST.D, ST.E, ST.F, ST.G) IS NOT NULL

--Pass 2
INSERT ActionTagRaw
SELECT MPR.SurrogateKey, ST.H, ST.I, ST.J, ST.K
FROM stagingtable ST
INNER JOIN MediaPlanRaw MPR on MPR.GUID = ST.GUID
WHERE COALESCE(ST.H, ST.I, ST.J, ST.K) IS NOT NULL

and so on..
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now