Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If I come across as pedantic in this response, it is not my intention. Based on the comment "I'm new at coding and having a problem troubleshooting" I wanted to walk through my observations and how I came to them.</p> <h2>Problem analysis</h2> <p>The desire is to split a single row into multiple output rows based on a delimited field associated to the row.</p> <p>The code as it stands now is generating the appropriate number of rows so you do have the asynchronous part (split) of the script working so that's a plus. What needs to happen is we need to 1) Populate the Child ID column 2) Apply the item prefix to all subsequent row when generating the child items. </p> <p>I treat most every problem like that. What am I trying to accomplish? What is working? What isn't working? What needs to be done to make it work. Decomposing problems into smaller and smaller problems will eventually result in something you <em>can</em> do.</p> <h2>Code observations</h2> <p>Pasting in the supplied code resulted in an error that itemList was not declared. Based on usage, it <em>seems</em> that it was intended to be itemName. </p> <p>After fixing that, you should notice the IDE indicating you have 2 unused variables (posID, childID) and that the <code>variable txHolder is used before it's been assigned a value. A null reference exception could result at runtime.</code> My coworker often remarks warnings are errors that haven't grown up yet so my advice to you as a fledgling developer is to pay attention to warnings unless you explicitly expect the compiler to warn you about said scenario.</p> <h2>Getting started</h2> <p>With a choice between solving the Child ID situation versus the name prefix/suffix stuff, I'd start with an easy one, the child id</p> <h2>Generating a surrogate key</h2> <p>That's the fancy title phrase that if you searched on you'd have plenty of hits to ssistalk or sqlis or any of a number of fabulously smart bloggers. Devil of course is knowing what to search on. No where do you ever compute or assign the child id value to the stream which of course is why it isn't showing up there.</p> <p>We simply need to generate a monotonically increasing number which resets each time the source id changes. I am making an assumption that the inbound ID is unique in the incoming data like a sales invoice number would be unique and we are splitting out the items purchased. However if those IDs were repeated in the dataset, perhaps instead of representing invoice numbers they are salesperson id. Sales Person 1 could have another row in the batch selling vegetables. That's a more complex scenario and we can revisit if that better describes your source data.</p> <p>There are two parts to generating our surrogate key (again, break problems down into smaller pieces). The first thing to do is make a thing that counts up from 1 to N. You have defined a <code>childId</code> variable to serve this. Initialize this variable (1) and then increment it inside your foreach loop.</p> <p>Now that we counting, we need to push that value onto the output stream. Putting those two steps together would look like</p> <pre><code> childID = 1 For Each item As String In inputListArray Output0Buffer.AddRow() Output0Buffer.ParentId = keyField Output0Buffer.ChildId = childID ' There might be VB shorthand for ++ childID = childID + 1 </code></pre> <p>Run the package and success! Scratch the generate surrogate key off the list. <img src="https://i.stack.imgur.com/UB4NN.png" alt="surrogate key generated"></p> <h2>String mashing</h2> <p>I don't know of a fancy term for what needs to be done in the other half of the problem but I needed some title for this section. Given the source data, this one might be harder to get right. You've supplied value of Apple01, Banana01, Spoon1, Fork1. It looks like there's a pattern there (name concatenated with a code) but what it is it? Your code indicates that if it's less than 3, it's a suffix but how do you know what the <em>base</em> is? The first row uses a leading 0 and is two digits long while the second row does not use a leading zero. This is where you need to understand your data. What is the rule for identifying the "code" part of the first row? Some possible algorithms</p> <ul> <li>Force your upstream data providers to provide consistent length codes (I think this has worked once in my 13 years but it never hurts to push back against the source)</li> <li>Assuming code is always digits, evaluate each character in reverse order testing whether it can be cast to an integer (Handles variable length codes)</li> <li>Assume the second element in the split array will provide the length of the code. This is the approach you are taking with your code and it actually works. </li> </ul> <p>I made no changes to make the generated item name work beyond fixing the local variables ItemName/itemList. Final code eliminates the warnings by removing PosID and initializing txtHolder to an empty string.</p> <pre><code>Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim childID As Integer Dim delimiter As String = "," Dim txtHolder As String = String.Empty, suffixHolder As String Dim itemName As String = Row.ItemName Dim keyField As Integer = Row.ID If Not (String.IsNullOrEmpty(itemName)) Then Dim inputListArray() As String = _ itemName.Split(New String() {delimiter}, _ StringSplitOptions.RemoveEmptyEntries) ' The inputListArray (our split out field) ' needs to generate values from 1 to N childID = 1 For Each item As String In inputListArray Output0Buffer.AddRow() Output0Buffer.ParentId = keyField Output0Buffer.ChildId = childID ' There might be VB shorthand for ++ childID = childID + 1 If item.Length &gt;= 3 Then txtHolder = Trim(item) Output0Buffer.ItemName = txtHolder Else 'when item length is less than 3, it's suffix suffixHolder = Trim(item) txtHolder = Left(txtHolder.ToString(), Len(txtHolder) _ - Len(suffixHolder)) &amp; suffixHolder.ToString() Output0Buffer.ItemName = txtHolder End If Next End If End Sub </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSSIS - Script Component, Split single row to multiple rows (Parent Child Variation)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USbillinkc
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSSIS - Script Component, Split single row to multiple rows (Parent Child Variation)
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODear Bill, it wasn't pedantic at all. I was little frustrated at time for figuring out compiling errors. For some reason or perhaps due to my ignorance BIDS isn't allowing me to run a debugger where I can watch the variables. I'm getting by "on error resume next" for now. I thank you for your kind help and time on this. It helped me a lot in terms of finding solution methodically. For String mashing, I have pretty "dirty" data input and I don't have structured rules for base and suffix. Except that base seems to come before alphanumeric suffixes, majority of which is less than or equal to 3dig
      singulars
      1. PO
      1. USElegantFellow
    2. COI can't enforce consistent length of data. It appears that I can't filter out all the peculiarities of my data, but just to cleanse them in broad general pattern for future usability. Once the data is cleansed, I plan to reference these data using fuzzy lookup like Levenshtein or Jaccard Index. Thank you again for your kind help. The surrogate key works beautifully.
      singulars
      1. PO
      1. USElegantFellow
 

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