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.

duplicate reference key values

HI, I keep getting this warning with a lookup (full cache mode) that retreives data form a table that contains the following information:

SRCE_SYS TABLE_NAME FIELD_NAME CODE ENGLISH_DESCRIPTION
STATIC STATIC PM_PC_TX_TYPE_CODE G GROSS
STATIC STATIC PM_PC_TX_TYPE_CODE E EXCESS
STATIC STATIC PM_PC_TX_TYPE_CODE F FACULTATIVE
STATIC STATIC PM_PC_TX_TYPE_CODE S SURPLUS
STATIC STATIC PM_PC_TX_RIDER_CODE BOAT BOAT
STATIC STATIC PM_PC_TX_RIDER_CODE RIDER RIDER
STATIC STATIC PM_PC_TX_RIDER_CODE CONT CONTENTS

The column tab matches SRCR_SYS, TABLE_NAME and FIELD_NAME (using constants defined in a derived column transform) .The code column comes from the source. We want to retreive the english_description colum from SRCR_SYS, TABLE_NAME,FIELD_NAME and CODE in the dataflow.

I would normally ignore the warning but sometimes, it seems that the lookup does not match any values and enabling memory restriction on the advanced tab resolve the issue and suppress the warning.

As I said, I keep getting this warning and I don't know why since there are no duplicates in the table? Am I missing something?

Thank you,

Christian

For the three fields SRCR_SYS, TABLE_NAME, and FIELD_NAME, there are duplicate keys.

You aren't matching on CODE as well?|||

Yes, I match the code as well so the combination of all 4 columns is unique. Does SSIS looks for all columns that are matched in the column tab or just the first one?

Thanks,

Christian

|||It matches on all columns that you select as lookup columns.

The following query should identify any duplicates:
select srce_sys,table_name,field_name,code,count(*) from table group by srce_sys,table_name,field_name,code having count(*)>1

duplicate records

I am having a headache working on a huge database which contains 120000 records. the big problem is that the database contains duplicates of the "serial no" of products. Another important field is the "date_purchased". How do I remove the duplicates and retain the serial no with the latest date purchased? I have managed to remove duplicates but the program does not remove on the basis of the date_purchased.Hi,

i found some info's about duplicates on planet-source-code.com as i had same problems:

see attached file (too big to quote here)|||How about doing it in 2 stages ie:-

SELECT DISTINCT SerialNo,Max(DatePurchased) From tbl GROUP BY SerialNo

Then inner Join it to

SELECT * FROM tbl

U could create 1 Statement as a Permanent View or Do it all in a stored procedure & #tmp table

Should work -

U'l prob find it can all be done in one Statement - Some1 Post & I'll C which way is faster

GW|||Or a refinement on GWilley's code

SELECT DISTINCT SerialNo, Max (DISTINCT DatePurchased)
From tbl??
Group By SerialNo

I ran this on something similar on my db (table with over 700,000 records) and it seemed to work correctly.|||you do not need DISTINCT when you use GROUP BY -- groups are distinct by definition

save the serial numbers and max dates in a working table, then delete all the rows that don't have a match in the working table

select serial, max(date_purchased) as maxdate
into keepthese
from yourtable
group by serial

delete from yourtable
where not exists
( select 1
from keepthese
where serialno = yourtable.serial
and maxdate = yourtable.date_purchased )

rudy
http://r937.com/

Duplicate primary keys in input file

'm trying to import a text file but the primary key column contains duplicatres (tunrs out to be the nature of the legacy data). How can I kick out all duplicates except, say, for a single primary key value?

TIA,

Barkingdog

SORT component has the ability to eradicate dupes. Have you looked into using that?

-Jamie

|||

No, but I will.

BTW does SORT allow me to send those dups to an "erorr" file so I can see I can further investigate those "bad" records?

Barkingdog

|||

No, you can't do that. But you could easily use an AGGREGATE followed by a conditional split to get all those where the key appears more than once.

-Jamie

Duplicate last record when using SqlDataAdapter.Update for Insert command

I'm getting duplicate records for the last record in the datatable. No matter how much or how little my datatable contains row records, it always duplicate the last one for some reason. Is there something wrong with my code below? EXAMID pulling from another stored procedure, which is outputed back to a variable.

--Data Access Layer--

If dt.Rows.Count > 0Then

'INSERT EXAM ROSTER

InsertComm =New SqlCommand
sqladapter =New SqlDataAdapter
InsertComm =New SqlClient.SqlCommand("ExamOfficers_AddOfficerSpecificExamRoster", conndb)
InsertComm.CommandType = CommandType.StoredProcedure

sqladapter.InsertCommand = InsertComm

InsertComm.Parameters.Add("@.examid", SqlDbType.Int)InsertComm.Parameters("@.examid").Value = examid
InsertComm.Parameters.Add("@.officerid", SqlDbType.Int, 12,"Officer_UID")
InsertComm.Parameters.Add("@.reimburse", SqlDbType.Bit, 12,"ReimburseToDb")
InsertComm.Parameters.Add("@.posttest", SqlDbType.Int, 12,"Post_Test")
InsertComm.Parameters.Add("@.pqcdate", SqlDbType.DateTime, 12,"pqc_date")
InsertComm.Parameters.Add("@.pqcscore", SqlDbType.Int, 12,"pqc_score")

conndb.Open()

sqladapter.UpdateBatchSize = 100
InsertComm.UpdatedRowSource = UpdateRowSource.None
sqladapter.Update(dt)

InsertComm.ExecuteNonQuery()
InsertComm.Dispose()

EndIf

--Stored Procedure--

ALTERPROCEDURE [dbo].[ExamOfficers_AddOfficerSpecificExamRoster]

@.ExamIDasint,
@.OfficerIDasint,
@.reimburseasbit=NULL,
@.posttestasint=NULL,
@.pqcdateasdatetime=NULL,
@.pqcscoreasint=NULL

AS
BEGIN
SETNOCOUNTON;

InsertInto Exam_Officers(EXAM_UID,Officer_UID,reimburse,post_test,pqc_date,pqc_score)
values(@.ExamID,@.OfficerID,@.reimburse,@.posttest,@.pqcdate,@.pqcscore)

END

Hi,

Try to comment this line InsertComm.ExecuteNonQuery() because it seems like that DataAdapter itself updates the database with its Update method but after that you are also running the command manually with ExecuteNonQuery. I think this is the reason why your last record is saved twice. Try it out.

Hope it will help you

Thanks and best regards,

sql

DTSRun string

I have a job that I moved from one server to another via generate sql script.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290B C36746B34119246D0BA612A999760D84E91CD170332E5D7CB5 90B764064631A1951589D6F310C930314F0F68864FCADAF023 2E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319E D868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
Message posted via http://www.droptable.com
Replace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> /~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290B C36746B34119246D0BA612A999760D84E91CD170332E5D7CB5 90B764064631A1951589D6F310C930314F0F68864FCADAF023 2E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319E D868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.droptable.com
|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:[vbcol=seagreen]
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com
|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:
|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>
> --
> Message posted via http://www.droptable.com
|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an ID
of some object.
Walt Mallon wrote:[vbcol=seagreen]
>It does but you can't see it. You get that string by scheduling the job via
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200511/1

DTSRun string

I have a job that I moved from one server to another via generate sql script
.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/ ~Z0xF8E8465A4E87F65E80A594D219039F3DB02F
031FE0290BC36746B34119246D0BA612A99
9760D84E91CD170332E5D7CB590B764064631A19
51589D6F310C930314F0F68864FCADAF0232
E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B
352C4319ED868A5232FAEDCA9031A0BB43FB
FF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
Message posted via http://www.droptable.comReplace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> / ~Z0xF8E8465A4E87F65E80A594D219039F3DB02F
031FE0290BC36746B34119246D0BA612A
999760D84E91CD170332E5D7CB590B764064631A
1951589D6F310C930314F0F68864FCADAF02
32E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE36
1B352C4319ED868A5232FAEDCA9031A0BB43
FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.droptable.com|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:[vbcol=seagreen]
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>
> --
> Message posted via http://www.droptable.com|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an I
D
of some object.
Walt Mallon wrote:[vbcol=seagreen]
>It does but you can't see it. You get that string by scheduling the job vi
a
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200511/1

DTSRun string

I have a job that I moved from one server to another via generate sql script.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290BC36746B34119246D0BA612A999760D84E91CD170332E5D7CB590B764064631A1951589D6F310C930314F0F68864FCADAF0232E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319ED868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
--
Message posted via http://www.sqlmonster.comReplace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> /~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290BC36746B34119246D0BA612A999760D84E91CD170332E5D7CB590B764064631A1951589D6F310C930314F0F68864FCADAF0232E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319ED868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.sqlmonster.com|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some sort
>> that I can compare?
Message posted via http://www.sqlmonster.com|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
SQLMonster.com" <u3288@.uwe> wrote:
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:
>>Replace the command with
>>DTSrun /n JobName /S Servername /e
>>Look at the DTSrun command for an explanation of the switches.
>>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some sort
>> that I can compare?|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>>Replace the command with
>>DTSrun /n JobName /S Servername /e
>>Look at the DTSrun command for an explanation of the switches.
>>Walter
>>I have a job that I moved from one server to another via generate sql
>>script.
>>[quoted text clipped - 13 lines]
>> same server as the job? Is the long alphanumeric string an ID of some
>> sort
>> that I can compare?
>
> --
> Message posted via http://www.sqlmonster.com|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an ID
of some object.
Walt Mallon wrote:
>It does but you can't see it. You get that string by scheduling the job via
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>> So what you are telling me is there the long string does not refer to an
>> ID
>[quoted text clipped - 14 lines]
>> sort
>> that I can compare?
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1

Dts?

I need to do the following for a client.
1. Download by FTP a zipped file that contains 10 text files
2. Unzip the file to get to the text files
3. Import data from the text files into separate tables in SQL Server.

notes:
Text files are pipe-delimited.
This is a hosted sql server...therefore I don't have full admin rights or access to other folders on the sql box.

Can this be done using a dts? I've never used DTS, but it seems like this is possible just messing around with the DTS

designer.

Can the unzip part be scripted using a activeX/com component?

Could I use my local server to download the file, then use my local enterprise manager and create the dts to import the data into their remote db?

Thanks for any help!!!! :eek:Yes this can be done. You'll probably have to download the FTP task from www.sqldts.com (http://www.sqldts.com). I'm not sure if the zip process can be done via activeX but it can definately be done using xp_cmdshell. As far as your local server, it can be done that way but sounds risky to me for a production process. Why not deploy the DTS package to the host SQL Server and ftp the files to a network location then import the data into the tables?|||Why not just write a sproc?

And as far as it's hosted...are you saying you can't write to a local drive?

DTS: Problems using DTSRUN but okay in Designer.

(SQL Server 2000, SP3a)
Hello all!
I've got a package that contains other sub-packages. The "outer" package that I'm trying
to execute has a bunch of Global Variables (GVs) that control the Source and Destination
server/database/credential information. The "outer" package doesn't really do anything
with these GVs except to pass them into the sub-packages, and those are responsible for
connecting to the various servers and "doing their thing".
When I load my outer package for the first time (it's a structured storage file) and use
the Designer to assign the values to the GVs and run it, everything works fine. However,
when I try to do similarly with DTSRUN, I get an odd error:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun OnStart: DTSStep_DTSExecutePackageTask_MyPackage
DTSRun OnError: DTSStep_DTSExecutePackageTask_MyPackage, Error = -2147467259 (80004005)
Error string: DTSStep_DTSExecuteSQLTask_1: Invalid authorization specification
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
I'm not sure why I would be getting this "Invalid authorization specification" error,
unless DTSRUN is having a hard time transferring the GVs from the "outer" package to the
"inner" package?
Any help would be greatly appreciated. Thanks!
John PetersonI finally put some rudimentary logging in the package to spit out the GVs. Turns out,
when I was invoking the package for DTSRUN, I was defining the GVs:
SourceLogin
DestinationLogin
And my package was expecting:
SourceUser
DestinationUser
Go figure! :-)
Once I fixed that, all was well.
Thanks for your help!
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23qJbvrzUEHA.1152@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a package that contains other sub-packages. The "outer" package that I'm
trying
> to execute has a bunch of Global Variables (GVs) that control the Source and Destination
> server/database/credential information. The "outer" package doesn't really do anything
> with these GVs except to pass them into the sub-packages, and those are responsible for
> connecting to the various servers and "doing their thing".
> When I load my outer package for the first time (it's a structured storage file) and use
> the Designer to assign the values to the GVs and run it, everything works fine.
However,
> when I try to do similarly with DTSRUN, I get an odd error:
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
> DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
> DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
> DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
> DTSRun OnStart: DTSStep_DTSExecutePackageTask_MyPackage
> DTSRun OnError: DTSStep_DTSExecutePackageTask_MyPackage, Error = -2147467259 (80004005)
> Error string: DTSStep_DTSExecuteSQLTask_1: Invalid authorization specification
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> I'm not sure why I would be getting this "Invalid authorization specification" error,
> unless DTSRUN is having a hard time transferring the GVs from the "outer" package to the
> "inner" package?
> Any help would be greatly appreciated. Thanks!
> John Peterson
>

DTS/Async Stored procedure/Import huge data

I have a table which contains approx 3,00,000 records. I need to
import this data into another table by executing a stored procedure.
This stored procedure accepts the values from the table as params. My
current solution is reading the table in cursor and executing the
stored procedure. This takes tooooooo long. approx 5-6 hrs. I need to
make it better.

Can anyone help ?

SamirCursors are evil!!!!

However, to be able to fully answer your question, we need to see your
stored proc/schema or even the results of a showplan.

I have used DTS/stored procs to import all sorts of data and have
never once had to resort to cursors (maybe I have just been lucky).
Generally speaking, I normally just do a select statement from the
source table which is used as input to the destination and doing any
casting/coercion along the way.

eg

create proc finky as

insert into
source
(a
,b
,c
)
select
a as char(2)
,IsNull(b, '')
,c
from
destination

samirpandey@.hotmail.com (Samir Pandey) wrote in message news:<33d418fa.0308071922.5061c0a4@.posting.google.com>...
> I have a table which contains approx 3,00,000 records. I need to
> import this data into another table by executing a stored procedure.
> This stored procedure accepts the values from the table as params. My
> current solution is reading the table in cursor and executing the
> stored procedure. This takes tooooooo long. approx 5-6 hrs. I need to
> make it better.
> Can anyone help ?
> Samir|||Hey thanks for your help. but...i just can't simply insert data in
the destination table. I need to run the values thru a logic which
exists in stored procedure.(e.g. if existing price is less than the
new price, then update another field which means that the price has
reduced/increased on a particular day)... and some other i make in the
stored procedure.

Thats why i need to run the values thru a stored procedure.

now can you help...?
thanks alot...

PromisedOyster@.hotmail.com (Mystery Man) wrote in message news:<87c81238.0308080439.79404088@.posting.google.com>...
> Cursors are evil!!!!
> However, to be able to fully answer your question, we need to see your
> stored proc/schema or even the results of a showplan.
>
> I have used DTS/stored procs to import all sorts of data and have
> never once had to resort to cursors (maybe I have just been lucky).
> Generally speaking, I normally just do a select statement from the
> source table which is used as input to the destination and doing any
> casting/coercion along the way.
> eg
> create proc finky as
> insert into
> source
> (a
> ,b
> ,c
> )
> select
> a as char(2)
> ,IsNull(b, '')
> ,c
> from
> destination

DTS transaction problem

I created DTS package that has two connections “Source” and “Destination”, contains several “Execute SQL” tasks and several “Data Driven Query” tasks after them. For all tasks workflow properties are set to “Join transaction if present”, “Rollback transaction on failure” and “Fail package on step failure” except the last “Execute SQL” task that has also “Commit transaction on successful completion of this step” property set. The problem: when package started it successfully complete all “Execute SQL” tasks, but first DDQ task produce error “Transaction context in use by another session”. What can be a reason of such behavior? How to solve this problem? I need everything done in one transaction.

Thanks.

Andrey,
You're on the wrong forum. Try http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.dts

-Jamie