Combine Data and Split into separate txt files for each header/detail row groupings

Combine Data and Split into separate txt files for each header/detail row groupings

• EricEarle

  I sort of did a sketch of what I think might work but really I don't know if I'm using the right components here or how to set this up. Basically, I want to bring back in the records like in the txt file...then split out each header and it's corresponding detail rows to a separate txt file

  this is just a sketch...I'm a bit helpless at this point.

  http://www.webfound.net/try2.jpg

  so for example, one section cut out into a txt file would look liek this in the new txt file:

  http://www.webfound.net/rows.jpg

  I have no clue how to accomplish all this but first trying to combine my data back into a format like the original txt file based on the PK/FK relationship I setup using mnt_HeaderID

  
  
  

• DaveM.

  Yeah, that makes sense.

  Basically, if the tables have different metadata then you can't process them in the same data-path. This does not mean that you can't process them in the same data-FLOW if that's what you want.

  -Jamie

   

  
  
  

• Ed Pinto - MSFT

  Ok, I'm thinking something like this   But isn't this UNION just gonna do the same thing....give me the HeaderRows first, then Maintenance, then Payment rows instead of grouping them like I want:

  http://www.webfound.net/try1.jpg

  Ultimately what I'm gonna be doing is creating individual TXTs for each header grouping then in the end I'll have to take those txts and based on a field I created from my table...or the txt name, do a loop and move them to specific directories (using UNC to our server) by changing the UNC path dynamically based off a lookup table...all this is comlicated.

  
  
  

• dX10

  Jamie, looking at the last post with the sql statement, I changed it. There must be a way to tweak that SQL UNION which does work in SQL and do a grouping like what I had in the original txt file
  
  

• HK.Lee

  one more try on explaining what i want to do.  It looks like to me tryign to combine the fields in SQL isn't gonna work using UNION so maybe I can just use 3 pipelines in SSiS then split.

  So...with that said, lets start over.  Now how can I take the 3 pipelines and split into separate txt files for each header / detail row group combination.

  http://www.webfound.net/3tables.jpg

  example data from each table:

  http://www.webfound.net/mnt_headerRows.txt (header table)
  http://www.webfound.net/mnt_MaintenanceRows.txt (maitnenancerows table)
  http://www.webfound.net/mnt_PaymentRows.txt (paymentrows table)

  Desired outcome based on match of a Unique key field I created in all 3 tables, PK/FK relationship between each header row and it's detail rows in the other 2 tables

  (example of desired txt file creation):

  http://www.webfound.net/desired2.txt

  so there will be many txts created based on each header in short

  
  
  

• Manish_Jain

  or try to do the same using my view, which does the proper UNION that I"m trying to fix then create the separate txt files somehow for each grouping:

  http://www.webfound.net/try3.jpg

  
  
  

• Dylan Morley

  I said the reason I am putting this in tables is because later we need to use stored procs to do some filters on it and I'd rather do the filtering on this data in SQL....so it just allows us to be more flexible in future phases so that we can filter, do some reports, etc. on the incoming data.
  
  

• Glenn Wilson

  As you might see, I cannot do a UNION because the fields are different in all 3 tables..not the same # of columns. I cannot just simply fill the header table with blank columns either..they are so uneven that this is not possible, I tried.
  
  

• guysky

  the biggest problem I have doing the union is that the rows between the 3 tables start out the same but after a few initial fields, have drasticall different # of columns, length of fields, and possibly in the future those datatypes may change behind the columns. Right now, yes, they're all varchar but drastically different in # cols and # chars

  i can try to use placement fillers like ' ',

  but that is maybe not possible as they are so drastically different from one another. I am trying but it's tricky, I have to look and compare all 3 tables to figure out where to put the ' ', and still not sure if in the end that will be possible.

  
  
  

• Marlon_Casusol

  If you're combining data from multiple tables then you probably need to UNION it. It must have the same metadata of course. You can do this in the source component or using a UNION ALL transform in the pipeline.

  To split into seperate files, use the conditional split transform. You'll need a column indicating which dataset the row belongs to in roder to split it all up.

  Can I ask why you're staging to tables in the interim Is this a requirement

  -Jamie

  
  
  

• Noam

  If the metadata of the 3 tables is different then UNIONing them, either in the pipeline or in T-SQL isn't an option. You need 3 seperate loads.

  When I said it could all be in the same data-flow I meant as 3 completely seperate data-paths - that's effectively the same as 3 seperate data-flows. The only difference is that performance can be a bit better. Sorry if I confused you over that.

  -Jamie

  
  
  

• spert

  Jamie, sorry, can you explain how I'd accomplish this in a data flow then that's what I'm not sure of. If I can't use SQL then yea, SSIS would be another option

  
  
  

• Ravimcom

  Thanks Jamie, I'm new to this pipeline stuff. I was able to pipeline the data into tables..just trying to understand using the data paths and what components to use...let me give it a try.
  
  

• shart44

  ok, there was one bigint i overlooked. Ok , now this query runs but the output is bringing in all header rows, then all maintenance rows, then all payment rows.
  
  I want this
  
  header
  related maintenance and payment rows (relation is based on mnt_HeaderID)
  header
  related maintenance and payment rows (relation is based on mnt_HeaderID)
  header
  related maintenance and payment rows (relation is based on mnt_HeaderID)
  header
  related maintenance and payment rows (relation is based on mnt_HeaderID)
  
  just like the original txt file.
  
  How do I form my SQL to do this
  
  So my current SQL is:
  
  SELECT h.mnt_HeaderID as hdr_HeaderID,
  h.BatchDate +
  h.NotUsed +
  h.TransactionCode +
  h.GrossBatchTotal +
  h.NetBatchTotal +
  h.BatchTransactionCount +
  h.PNet_ID +
  h.PartnerCode +
  h.Filler as HeaderRow,
  h.PNet_ID as FilePath
  
  FROM mnt_Header h
  
  UNION ALL
  
  (SELECT m.mnt_HeaderID as mnt_HeaderID,
  m.TransactionDate +
  m.TransactionTime +
  m.AccountNumber +
  m.TransactionCode +
  m.FieldCode +
  m.NewValue +
  m.InternalExternalFlag +
  m.PNetID +
  m.RecovererID +
  m.LoanCode +
  m.NotUsed as mnt_Row,
  ' '
  FROM mnt_MaintenanceRows m)
  
  UNION ALL
  
  (SELECT p.mnt_HeaderID as pmt_HeaderID,
  p.TransactionDate +
  p.TransactionTime +
  p.AccountNumber +
  p.TransactionCode +
  p.TransactionAmount +
  p.InterestFlag +
  p.SelfDirectedFlag +
  p.TransactionDesc +
  p.NetPaymentAmount +
  p.CommissionPercent +
  p.InternalExternalFlag +
  p.PNetID +
  p.RecovererID +
  p.RMSLoanCode +
  p.Filler as pmt_Row,
  ' '
  FROM mnt_PaymentRows p)
  
  output I'm getting now is:
  
  http://www.webfound.net/combined_output_wrong.txt

  
  
  
• Combine Data and Split into separate txt files for each header/detail row groupings