Home » MS OfficeRSS

Index droping problem in SSIS package?

Hi All,

I have one ssis package. After load the staging data I am creating index in one column. I am dropping the index end of package. But sometimes after create the index if I got any error I need to re run the package, already index is created so that’s why  once again I will get a error. So that purpose drop index statement I used before create index sqltask.If the first time package runs the table don’t have index. If the table have index it will drop the index otherwise it will skip that task. I used below code. But I am getting the error index is not dropped properly.

IF EXISTS (SELECT name FROM sys.indexes WHERE name =

  N'IND1_TRAN_DT

')

 DROP INDEX  IND1_TRAN_DT

 ON SLS_STNT_DTL_STGNG;

 

Thanks

CMK

 

8 Answers Found

 

Answer 1

Hello Cmk1,

Your code  looks ok, are you sure you are selecting the correct database where your index  is in Execute SQL task?

What exactly error  you are getting?

Thanks

 

Answer 2

Thank for your response Aamir

Already i executed created index  execute sql task.After that i executed drop  index execute sql task.Once again i executed created  index task.I got the table  already have the index.But i didn't get any error  on drop index task.that mean index is not dropped properly.

Thanks

CMK..

 

Answer 3

what happens when you execute it in Management Studio? Do you see the index  dropped?
 

Answer 4

Just now i checked index  is not dropping.

 

 

Answer 5

if i use the below statment index  is drropped but i want drop  the index above statement

DROP

 

INDEX IND1_TRAN_DT

 

ON SLS_STNT_DTL_STGNG;

Thanks

CMK

 

Answer 6

CMK...

Check the existence of the index  before creating  and dropping.

 

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = N'IND1_TRAN_DT')

create index [N'IND1_TRAN_DT] ON [dbo].[SLS_STNT_DTL_STGNG;] ([ColumName])

 

IF EXISTS (SELECT name FROM sys.indexes WHERE name =  N'IND1_TRAN_DT')

 DROP INDEX  IND1_TRAN_DT ON SLS_STNT_DTL_STGNG;


And also make sure that you have select  the right database connection for the Execute SQL task.

Thanks,
Mani

 

Answer 7

Mani my problem  is after creating  the index  if i executed drop  index task  ..index is droping  ok fine.In my first time package  run first drop index task is executing..at that time  i got this error

 

Cannot drop the index 'SLS_STNT_DTL_STGNG.IND1_TRAN_DT', because it does not exist or you do not have permission.

 

For suppose first i executed created  index task after that if i executed drop index task..every thing fine.

 

Thanks

CMK

 

Answer 8

CMK, Got any clue on this issue ???

 

 

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter