duplicate rows in 1 csv file

Hi, I am trying to import data from a csv files to a OLE DB Destination. The csv files contains all transactional changes . For example for a particular record the firstname, lastname, email address records change within the same csv file. I need to save only the last updated record from the csv files. I have tried "slowly changing dimensions" but these dont work when there is duplictes within the same csv file. Also have tried 'Sort' but this only stores the first occurance.
Any ideas how i can store the latest changed data within 1 csv file.

How does the process distinguish between the latest data and duplicate data?

Is there a date as part of the row or is it just the last row wins?

Kirk Haselden
Author "SQL Server Integration Services"

|||the last row wins. there is no timestamp field . This is the way designed by the third party developers. Records are saved in csv files as changes are made. I have to put all these rows of data into a datawarehouse without duplicating the primary key.

DTS vs triggers

Hi,

I've got the following problem:

I'm using DTS to transfer fata from csv files into a table.
In the file there are often duplicated rows, therefore I made a
trigger
on the table.

The trigger works fine when i tried in query analyzer, but it not
launched
from the dts.

I've learned that i have to turn of the fast load option in the
properties of the transform data task object.
When I turn this option off, the task will fail with the following
message:

"The number of failing rows exceed the maximum specified.
Cursor operation conflict."

I wonder why this happens. There are no check constraints on the table
against duplication.

Thanks in advance for any helpBijesz wrote:
...
> "The number of failing rows exceed the maximum specified.
> Cursor operation conflict."
> I wonder why this happens. There are no check constraints on the table
> against duplication.

I may have an answer:

Let's assume you've saved your DTS job on the SQL Server, so that when
you are in Ent. Mgr. you can click on the DTS folder, click on the
'local packages' icon, and find your saved DTS job there.

Once there, you'll see the DTS job displayed as a set of 3 objects: 1)
the Excel icon for your source; 2) an arrow pointing to your MSSQL
destination, and; 3) your MSSQL destination.

If you right-click on the Excel or MSSQL objects and open the
Properties window, as I did, you'll be perplexed.

It finally occurred to me to right-click on that arrow and open-up yet
another Properties window there.

Once there, if you click on the 'Options' tab, you'll find a 'Max Error
Count' property. Nice place to hide it, eh?

hth,

victor dileo

DTS validation

Hello, I'm kind of new in database programming and I have to make a DTS where I put a CSV file with a list of names and emails and it has to validate the emails (but really validate them, not only the '@.') and if the email is valid is inserted to a table and if it is invalid it goes to another table.
Because I'm new I dont know were to start, first I need to know if there is a regular expression something on sql server because I have this expression that really validates a mail and doing it with SUBSTRINGs and CHARINDEXes would be really long (I think).

regexp="^[\w-\.]{1,}\@.([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
This works great with ASP but how do I put it in T-SQL syntax..

Bye,
Thanks in advance.You could use an ActiveX script in the DTS package. It supports both VBScript or Java Script.

Or you could create an Extended Stored Procedure and use it in your DTS package.

To create an extended stored procedure DLL by using Microsoft Visual C++

1. Create a new project of type Win32 Dynamic Link Library.

2. Set the directory for include files and library files to C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Include and C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib, respectively.
On the Tools menu, click Options.

3. In the Options dialog box, click the Directories tab and set the directory for include files and library files.
On the Project menu, click Settings.

4. In the Project Settings dialog box, click the Link tab. Click the General category, and then add opends60.lib to object/library modules.

5. Add source files (.c, .cpp, and .rc files, and so on) to your project.

6. Compile and link your project.
This comes from Books Online.

Hope this helps some.

Originally posted by ArgenSQL
Hello, I'm kind of new in database programming and I have to make a DTS where I put a CSV file with a list of names and emails and it has to validate the emails (but really validate them, not only the '@.') and if the email is valid is inserted to a table and if it is invalid it goes to another table.
Because I'm new I dont know were to start, first I need to know if there is a regular expression something on sql server because I have this expression that really validates a mail and doing it with SUBSTRINGs and CHARINDEXes would be really long (I think).

regexp="^[\w-\.]{1,}\@.([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
This works great with ASP but how do I put it in T-SQL syntax..

Bye,
Thanks in advance.|||Thank you for your help, I solved it with an ActiveX script I just pasted the email validation function from my ASP code and it worked great, now I have another question, here is the link if someone cares, is about the first part of my question above.

http://www.dbforums.com/showthread.php?threadid=949654

Bye, Thanks.

DTS to run open file

Hi All

On the Sql Peformance Monitor, I have scheduled a log to give me memory and perfomance of my server and I store the results in .csv file every hour and it works perfect.

Here comes the problem when I make a DTS to take data from .csv to Sql, my problem is, I get this error "The process cannot access the file because it is being used by another process". Basically it tells me that I have to close the file before I can run my DTS.

I dont want close this file manually and run my DTS afterwards, I want my DTS to do this automatically since I won't have access to that server when I am finished as I am going off from the clients premises when I am finishedOriginally posted by msenoelo
Hi All

On the Sql Peformance Monitor, I have scheduled a log to give me memory and perfomance of my server and I store the results in .csv file every hour and it works perfect.

Here comes the problem when I make a DTS to take data from .csv to Sql, my problem is, I get this error "The process cannot access the file because it is being used by another process". Basically it tells me that I have to close the file before I can run my DTS.

I dont want close this file manually and run my DTS afterwards, I want my DTS to do this automatically since I won't have access to that server when I am finished as I am going off from the clients premises when I am finished

Hi,

maybe it is possible for you to make aa copy of the .csv file and get data from this copy??

Lambik|||Thanks, I did that and it worked perfectly

DTS to output .csv

I am selecting the following fields from my table and drop them into my .csv, the problem is that if i select all the fields which I commented out, and click on define columns, populate from source, execute, then on the Destination Tab, none of my columns are selected, it is blank, please help, here is my table

select
trad_type,
reference,
principal,
book,
strategy,
cpty,
buy_sell,
Quantity,
ident_type,
ext_ident,
sec_name,
price,
price_divisor,
traded_net_ind,
trade_ccy,
trade_ldt,
value_date,
commission,
exchange_fee,
other_fees
gross_consid,
net_consid,
sett_ccy,
trad_sett_ccy_xrate,
/*trad_sett_ccy_xrate_mdv_ind
trad_inst_ccy_xrate
trad_inst_ccy_xrate_mdv_ind
*/
pb,
acct,
inst_class
/*cont_desc
pl_book_ccy_xrate
Id*/
from Table1Use the sql analyzer to get the list you want. Copy the list to a spreadsheet and change the file type. Or, create a temp_table and select into the temp_table.|||I still get the same problem.