Import dedicated Records from a Flatfile to a DB

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: 0x80004005Description: "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 matchwith the Flatfile?

Thanks in advance for any answer

Chaepp

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.|||

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

|||

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.|||

Sorry, I'm a beginner.

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

Regards

Chaepp

|||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|||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|||

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

|||

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.

import data from MySQL to SQL Server

Hi All
I'm trying to import data from mySQL database to SQL server 2005. I installed mySQL odbc driver (3.51) and configured system DSN. However when I start SQL server import/export wizard mysql is not an option in data source dropdown.
What do I miss?
Thanks,
Vlad.Hi,
I never worked with MySQL, but there is a general approach. If you were able to create DSN, try to create a DTS package on SQL Server. I do not use SQL Server2005, but 2000. There is Other Connections icon under Connections in DTS designer. Configure this connection for MySql. The rest is yours - just configure the destination for your MySQL data.
Regards|||Hi,
I agree with dbmjs that creating a DTS package will probably be an easy way of transfering the data. Just a couple of quick notes:
1) DTS in sql server 2000 works great for smaller < 1mil records. Anything bigger than that it seems to run into some issues. (You can always make a couple of smaller packets and import them)
2) The DTS equivelant in SQL Server 2005 is named SQL Server Integration Services (SSIS), and works great no matter the size or amount of records.

Hope this helps
Good luck,
Reghardt

import data from excel to SQLserver

Hi all!

I need to import data from excel file to SQLserver. What is the best way to do this?
Please give as much explanations as possible (code example would be very-very helpful).

Any ideas are wellcome.
Thanks.The best way to do this is DTS = Data Transformation Service. Use the Wizards in SQL Server. It can't be more simple than that.

Greetings,

Mark|||Mark,

Thank you for your answer.
How it could be done if the server is on a remote computer? I use some hosting service - is it still possible to use DTS or something like this?

Sorry, if my problem is too simple for you. I am new to .NET, so everything is still difficult to me.

Thanks for help.|||Hi

Another alternative, is not the best one, very raw solution ...

write the script to connect to excel, read from excel and insert into sql server ...