Import dedicated Records from a Flatfile to a DB

Import dedicated Records from a Flatfile to a DB

• mlcprs

  Hello,

  Thanks for your quick answer.

  The DB Table is empty and I like to load the data from the Flatfile....

  Then I get the error. I tried to go with Lookup, but I have still the same problem...

  Could you give me an idea how to solve the problem

  Thanks for an early answer.

  Regards

  Chaepp

  
  

• Dawa Tsering

  I tested this in a script component and got it to work. The input iwas a simple flat file of one column that had a couple of duplicates. Destination was a one colum table with a PK on it. The arraylist checks if the current row exists in the array already, if it does it gets ignored, otherwise we add a row to the output buffer which is then sent down the line to the destination task. This works but I could not get it to work from scratch - I had to cut and paste a script component I'd used before into the package because i could not get a new one to generate the Public Class Output0Buffer.... statements. (These are in the file with ' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! at the top so they are system generated as part of the Script Task, I just need to figure out how i got them generated the first time and then we can get the code below to work from a new Script component)
  
  Dim alist As New ArrayList
  
  Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
  If Not alist.Contains(Row.Name) Then
  alist.Add(Row.Name)
  Output0Buffer.AddRow()
  Output0Buffer.NameOut = Row.Name
  End If
  End Sub
  
  the Name property of Row comes from the flat file as the Input column
  NameOut of Outpu0Buffer is the Script Output column
  
  

• kenlefeb

  Chaepp wrote:

  Hi Thank you very much for your help. I have found another solution: I simply use the Aggregate function and it works perfect.... Regards Chaepp

  can you please be a little more specific i don't see how the aggregate transformation would solve your problem.

  btw, by "ole db" i meant "ole db command". sorry about the confusion.

  
  
  

• schmack

  Chaepp wrote:

  Hello, Thanks for your quick answer. The DB Table is empty and I like to load the data from the Flatfile.... Then I get the error. I tried to go with Lookup, but I have still the same problem... Could you give me an idea how to solve the problem Thanks for an early answer. Regards Chaepp

  ok, i'll take another stab at it. you could try to use the ole db transformation to insert the rows, and redirect the error rows.
  
  

• AlistairSKing

  Hi ,
  I like to see how this one pans out, as i cannot see how a Lookup would work. By definition Chaepp is importing to a table where the data does not already exist thus the lookup would fail. He could redirect this 'failure' as his error output, but then all his errors would be cached and thus still fail for the same reason as he would have multiple failures on the same data and thus when the data flow passes into the destination to actually write to his database table, it will still fall over. I could see a Lookup working if it would write each row it did not find to the destination table and thus subsequent finds , ie PK violations, could be re-directed, but only if the table is being updated in real time from the lookup which it does not do. I'm thinking a Script component here, not lookup.
  
  Dave
  
  

• curt2go

  Sorry, I'm a beginner.

  What do you mean with 'ole db transformation' The Ole DB Command

  Regards

  Chaepp

  
  

• MillBear

  Chaepp wrote:

  Hello all I got a Problem when I try to store Data from a Flatfile to a DB. The following Error appears in the Progress Control: An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Products_1'. Cannot insert duplicate key in object 'dbo.Products'.". I have a Flat File Source, and would like to store the needed records in a DB. In Column 0 in the Flatfile I have multiple Entries with equal Values. In the DB this Column is set as Primary Key and can only have one Record with the same Value in this Column. How can I read out (or store) only one Record with the same Value from the Flatfile to store it in the DB How can I check if there is a Record from the Flatfile in the DB with the same value in the Primary Key How can I change any of the remaining Columns with different Values in the DB to match with the Flatfile Thanks in advance for any answer Chaepp

  your problem seems to cry out for a lookup transformation. you would need to lookup the primary key in the source to see if it exists. if the key already exists, then redirect the row to the error output where you can stage the data for further processing.
  
  

• Benin

  Hi

  Thank you very much for your help.

  I have found another solution:

  I simply use the Aggregate function and it works perfect....

  Regards

  Chaepp

  
  
• Import dedicated Records from a Flatfile to a DB