DTS & Indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS & Indexes

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2005-06-10 : 08:31:28

Hi Guys,

I think i have a simple question. I am doing a Data Transfer task to dump a 2 million record table from sybase to SQL server 200 database...I created the DTS package for this and everything works fine..the data got transferred fine...then on this table on sql server i have put a index(manually created one using wizard) for better search results...

Now if i schedule this package to dump the data again from the sybase database...does the index remain valid on sql server table....if not how do i put the index programatically after the transfer is done...

Thanks

-Dupati

mfemenel
Professor Frink

1421 Posts

Posted - 2005-06-10 : 09:17:41

When I do things like this, I prefer to drop the index, truncate the table (or delete if you want to log the deletion),import the data, then recreate the index. It's going to give you a much faster load time than leaving the indexing dropping data from the table and then trying to insert into it. In DTS you can do an execute sql task. To drop the index you'd use drop index tablename.indexname, make this step 1. Step 2 is a sql task using "truncate table tablename". Step 3 is your import. Step 4 is another sql task create index "indexname" on tablename(field to indexname).

Mike
"oh, that monkey is going to pay"

dupati1
Posting Yak Master

123 Posts

Posted - 2005-06-10 : 09:37:42

Hi Mike thanks for your suggestion...I created two sql task but i am unable to set the steps in tandem...

it looks something like this...

sybase->SQL Server->Sql task(create Index)

but the first step Sql task(Drop index) i am unable to link it to the SQL server...

when i tried to see the work flow properties and tried to create a new one...it did not gave me destination as SQL Server...

??

Any suggestions...

-Dupati

dupati1
Posting Yak Master

123 Posts

Posted - 2005-06-10 : 09:39:34

What i mean to say is how do i make this dropping index step as step 1 and upon success move on to data transfer task...it does not give me that option in the work flow properties window??

Thanks

-Dupati

dupati1
Posting Yak Master

123 Posts

Posted - 2005-06-10 : 10:02:46

i did something like this...Drop Index on success ->do data transfer-> then create index

but data transfer step is failing after Drop Index step is successfully done...??

What am i doing wrong?

dupati1
Posting Yak Master

123 Posts

Posted - 2005-06-10 : 10:09:38

here is the log file:

The execution of the following DTS Package succeeded:

Package Name: NotesPackage
Package Description: (null)
Package ID: {23CABDD1-E5B1-4390-8A9E-0B3FD014DA5E}
Package Version: {8145F741-FC52-4E02-B1CF-EBE459BC0901}
Package Execution Lineage: {AF6D4194-9ED5-4796-91F9-3D5B84F75455}
Executed On: D008713
Executed By: vdupati
Execution Started: 6/10/2005 10:08:52 AM
Execution Completed: 6/10/2005 10:08:57 AM
Total Execution Time: 5.125 seconds

Package Steps execution information:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Invalid class string
(Microsoft Data Transformation Services (DTS) Package (800401f3): Invalid class string
)
Step Error code: 800401F3
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Step Execution Started: 6/10/2005 10:08:57 AM
Step Execution Completed: 6/10/2005 10:08:57 AM
Total Step Execution Time: 0 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 6/10/2005 10:08:52 AM
Step Execution Completed: 6/10/2005 10:08:57 AM
Total Step Execution Time: 5.109 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_2' was not executed

-Dupati

dupati1
Posting Yak Master

123 Posts

Posted - 2005-06-10 : 10:20:35

Ignore all my messages...i got it working now...:)

Thanks

clarkdl
Starting Member

1 Post

Posted - 2005-12-19 : 09:10:50

quote:
Originally posted by dupati1

Ignore all my messages...i got it working now...:)

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources