Databases
StackOverflow2013
Postsdbo
POSSIS - import file with columns populated like sub-headers (omitted on some rows)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSSIS - import file with columns populated like sub-headers (omitted on some rows)
    text
    copied!<p>I am importing an Excel file which is formatted like a report - that is some columns are only populated once for each group of rows that it belongs to, such as:</p> <pre><code> CaseID |Date |Code 157207 | | |8/1/2012 |64479 |8/1/2012 |Q9967 |8/1/2012 |99203 </code></pre> <p>I need to capture one of these group headers (CaseID, in the example above) and use it for subsequent rows where the field is blank, then save the next value that I encounter. I have added a variable (User::CurrentCaseId) and a Script transform, with the following code:</p> <pre><code>public class ScriptMain : UserComponent { string newCaseId; public override void Input0_ProcessInputRow(Input0Buffer Row) { if (!Row.CaseIDName_IsNull &amp;&amp; Row.CaseIDName.Length &gt; 0) newCaseId = Row.CaseIDName; else newCaseId = "DetailRow"; } public override void PostExecute() { base.PostExecute(); if (newClaimNumber != "DetailRow") Variables.CurrentCaseId = newCaseId; } </code></pre> <p>Basically, I am trying to read the value when present and save it in this variable. I use a conditional split to ditch the rows that only have the CaseID and then use a derived column to put the variable value into a new column to complete the detail row. Alas, the value is always blank (placed a data viewer after the derived column). I modified the script to always set the variable to a fixed string - the derived column is still blank.</p> <p>This seemed like a good plan... I received some feedback in the MS forums that you can't set a variable value and use its new value within the same Data Flow Task. If that is so, the only solution I can think of is to write the CaseID out to a table when present and read it back in when absent. I really hate to do that with several million rows (multiple Excel worksheets). Any better ideas?</p> <p>Best, Scott</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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