Home » MS Office

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 =









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?



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.




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









Answer 6


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


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



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.





Answer 8

CMK, Got any clue on this issue ???




When we handle SSIS events using IDTSEvents callbacks as described here, will it in any way affect the event handlers already in that package? Eg will it override the existing package?
I am using SSIS Package to Load Daily a file of 8GBs.  If the free Disk Space is more than 100GBs.. Everything goes fine. Problem Starts when we have less free Disk Space. I tried to Load a 8GB file with 60 GB Free Disk Space it Inserted 1658 records, Then I deleted SQL Logs and made space more than 100GBs free. For same file it inserted 60,000 records. I am not able to Understand how it happened and It have happened many times..

Hi All,


I have an SSIS package and the whole objective is to get it to connect to multiple database servers, get resports and transfer it to a central repository database. I have managed to do this quite well, the problem is that for some reports that i need. It means that the query has to first write the results to some kind of table on the target database server and then the dynamic connections allows for reading of that table and transferring it to the central DB server.

The problem that i am faced with is that, SSIS does not allow me to create the tables on the fly and read from it on the fly as well. I really dont want to leave traces of report tables on production database servers, if i have a check to see if the table exists, then drop it, then SELECT into it. the package fails.

If the table already exists on the database, the package works. Is there a way around this problem ?. Its a shame that one cannot use temp tables in SSIS packages. The end goal is not to have these tables lying on the database, i want them created on the fly, worked on and after dropped.


Any ideas ?



I've been working throught the SSIS tutorial but have a problem with the package configuration lesson.  After exporting the required object to the file the completion step shows:

 Configuration 1

 Configuration File

New configuration file will be created.

File name:
 C:\Documents and Settings\Andrew\My Documents\Visual Studio 2005\Projects\SSIS Tutorial\SSIS Tutorial\SSISTutorial.dtsConfig


If I then try and edit the configuration I get a message:

Some configurations from the file "C:\Documents and Settings\Andrew\My Documents\Visual Studio 2005\Projects\SSIS Tutorial\SSIS Tutorial\SSISTutorial.dtsConfig" could not be loaded for the following reasons:
\Package\Foreach Loop Container.ForEachEnumerator.Properties[Directory]: The configuration refers to an object that does not exist in the package. Package path of the object: '\Package\Foreach Loop Container.ForEachEnumerator.Properties[Directory]'.

The loop container is called "Foreach Loop Container" not "Foreach File in Folder" because I didn't rename it.

Does anyone know if this is a bug or have I missed something?





I created an SSIS package dynamically using my C# code. Package is created successfully. I use the SQLNativeClient10.1 as my connection manager. The syntax I use during creation of the package is

Data Source=INGBTCPIC5DT59B;Initial Catalog=Target;Provider=SQLNCLI10.1;


Integrated Security=SSPI;Persist Security Info=True;Auto Translate=False;

But the package is throwing strange error if I run the package immediately after creation. But if I open the package once and refresh all the components manually by opening it once and then save it. It executes fine.

The error which i get is

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: Ox80004005. An OLE DB record is available. Source:"Microsoft OLE DB Provider for Native Client". Hresult: Ox80004005 Description: "Incorrect syntax near 'True'".

Can any body help me out on this?

Thanks in advance



I have a ssis package which has 2 tasks, first it builds an excel sheet with data on the server from where I am running the ssis pkg and the second task (a sql process task), runs a bat. the bat file that basically copies the  excel sheet created from the first step to another server. Problem is that the ssis pkg runs succesful when i run it from BIDS, bust when I run is as ajon, it gets hung. It completes the first step, it is the sescond step that is get hung. I noticed that when i run from BIDs, it does come up with a window open file - security warning and says the publisher could not be verified, are you sure you want to run this software. why does it do that ??? the file I am asking to run is a .bat file. it should automatically use the cmd.exe app to run the bat file. Please help !!!

Thanks in advance.


Hello friends

I'm trying to run my SSIS packages as a step inside a sql agent job.

This runs normally when the package uses the SAME database as used to create my job.

The problem occurs if I create a job to run the package and this package must execute queries in another database. Probably authentication problem. So how to authenticate my package and the job runs normally ?

thanks in advance.

Hello Gurus!

We try to execute our SSIS packages from a .NET application. It works fine until we use “Expressions ” inside Data Flow’s components like “ADO NET Source” (e.g., for building SQL statements)

I use the ADO NET Source Control and want to set the SqlCommand property with an expression holding a SQL statement.

When executing the SSIS package in Visual Studio (in debug mode) I get no problem, everything works fine. When executing the package from within my .NET application it crashes the app completely, only creating a generic Win32 Exception with no relevant information.

My steps:

1.       I created new SSIS package.

2.       Then added the Data Flow component.

3.       Inside Data Flow I added “ADO NET Source”. I use this control in order to get data from Webtrends (a third party data provider). It uses an ODBC driver provided by Webtrends to access the data. SSIS allows me to access ODBC only via ADO NET provider, so that’s why I use ADO NET Source. The problem is not related to the Webtrends driver, we managed to reproduce the error with a connection to a SQL Server.

4.       I would like to have only one SSIS package which can get all the data I need from Webtrends. I just want to set some variables of the SSIS package from within C# (which works fine). When I set variables from C#, I use expressions to generate new SQL statements for the ADO.NET source using the values from the given variables. Unfortunately that does not work. I get an error and the application crashes.

5.       If I do not use Expressions, (by setting the SqlCommand directly in the package) then everything works fine and I can execute package from .Net, but even if I only want to set a hardcoded SQL statement within Expression I get an error….

If I understand correctly there is only one “DTS” framework which is used from SQL Server, Visual Studio and even from .Net/C#.  So I guess that it should work also from .Net without any problem, but probably it messes some parameters which I should set in C# for the application object before package executing.

If someone has already experienced this problem and has a solution please provide me with information on how to fix it.

Our environment:

VS 2008

SQL Server 2008 R2


Microsoft.SqlServer.DTSRuntimeWrap (version

Microsoft.SQLServer.ManagedDTS (version

Microsoft.SqlServer.DTSPipelineWrap (version


Here is my c# code:

  dtsApplication = new dts.Application ();

using (dts.Package dtsPackage = dtsApplication.LoadPackage(packagePath, null ))


packageResult = dtsPackage.Execute();


Here are some screenshots showing my packages:

You can see it’s just so simple…

Control Flow:



Data Flow:



ADO NET Source:






I have a Script task that starts a staging package by declaring some local defined user variables (in the master package I have master defined user variables and they work just fine) however in the staging package there seems to be a problem I don't understand. The script task wont accept the execution and gives me this error message:

"This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there."

please help me





Hi All,

I am facing a problem in custom logging in SSIS. I am using SQL Server 2005 version.

I have a master package which will execute several child packages. The master package has the following eventhandlers - (OnInformation, OnError, OnWarning, OnPostExecute). In each event handler I have a custom code (.net custom task) to read the packagename, taskname and the message and insert into a custom log table.

The child packages does not have any event handler.

When I am executing the master package it's executing all the required child packages. I can see all the messages are logged into the log table. But there are some problems here.

1) The package name in the log table is coming as the master package name for all the messages even though they logged from a child package.
2) If I put the event hanlder for child packages then all the messages are getting logged twice (one with the master package name and one with the child package name)

I have a requirement to get the child package name in the log table so that the front end application can filter those records based on the package name.

Can any one tell me the way to solve the above probelm?

Thanks in advance


DTS Package in 2000 converted in SSIS package when I open I get the following error

-Error loading x.dtsx: Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.   

Similar error is for SQLPassword property.

Please respond in case you know the solution.

I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.

Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.

When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.

I've tried giving administrative permissions to the ASPNET worker process user to no avail.

I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.

I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.

Anybody got any idea on how to go about this?



I am interested in Passing value from a child Package variable to the Parent package that calls it in ssis.

I am able to call the Child package using the execute package task and use Configurations to pass values from the parent variable to the child, but I am not able to pass the value from the child to the parent.

I have a variable called datasetId in both the parent and child. it gets computed in the child and needs to be passed to the parent...

Any suggestions?

Thanks for any help in advance..




My package has DelayValidation = True, but the package get validated at "Design Time" and "Run Time" which takes more time. Is there any better approach to make this faster?

I have seen this thread:  http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/3d4a0b1d-a05a-49eb-a014-9f9a664cabf2 and as this is very old thread and may be we have another approach now?



I am trying to convert SQL 2005 SSIS packages to SQL 2008R2 SSIS packages, I am doing the following way to convert:

From the BIDS of SQL 2008 R2, right click on SSIS packages, add the package from SQL server 2005 server.

Please let me know what is the best way of converting the SSIS packages from SQL 2005 to SQL Server 2008R2 packages. Thanks. 



it is possible to start ssis packages with dtexec and set variables as package configuration like this:

dtexec.exe /SQL "\mart1\import_fact_calls" /SERVER "R08" /SET \package.variables[FullImport].Value;True

Now I'm trying to start a package via script task with this code:

Public Sub Main()
    Dim pkg As New Package
    Dim app As New Application
    Dim pkgResults As DTSExecResult

    If Dts.Variables("package").Value <> "" Then
      pkg = app.LoadFromSqlServer(Dts.Variables("package").Value, "R08", "USER", "PASS", Nothing)
      pkgResults = pkg.Execute()
    End If

    Dts.TaskResult = ScriptResults.Success
End Sub

How can I add a package configuration like the one with dtexec to the script code?

I like any ideas, links, docus ... Thanks!


I have developed a dtsx package to load data from xml load table into a dozen of flat tables. I found the SSIS is running slow (almost 10 times slower) under Execute Package Utility than running under visual studio IDE.

Does anyone have same experience?




I'm brand new to SSIS, so please bear with my limited knowledge and experience. I'm working through a tutorial found at http://accelebrate.com/sql_training/ssis_2008_tutorial.htm, and I can't save my package in the package store. Here's the error I get:



The storage location for the folder "File System\" cannot be accessed. (Microsoft Visual Studio)



Program Location:


   at Microsoft.SqlServer.Dts.Runtime.Application.ExistsOnDtsServer(String sPackagePath, String sServerName)

   at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)

   at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

All I'm trying to do is save the package in the package store before I execute it. Any help will be greatly appreciated!



I have some 10 ssis 2005 packages. source Db and destination Db for these packages are common. I am calling these child packages in a master package.

I am using only one XML config file for this master.

Here by using the parent package variable(in Master package) i am setting the values like DB connection settings etc.. in the child packages.By this way i avoided the config files for child packages.

Its working fine.

Now the issue is that user wants to execute the individual package also(not from master) and user doesn't want to use more config files.

Please suggest me the best approach to have one config file for all the ssis packages.







I made a installer program in C# that deploy ssis package on a SQL server 2005. I have like 50 package that contain only dataflow to update a datawarehouse and create fact tables.

I made also one package that call all the other packages. When I try to deploy the packages on the SQL server with the command SaveToDtsServer, I receive an error BUT only on the package calling all the other SSIS package ?? All the other SSIS packages are deployed but the package that call other packages failed to deploy. Whats strange is that when I use the domain controler to deploy the package (I use Impersonation in my C# code) its all working fine, but if I use another user (like the computer admin account), I got the error only on the package calling other packages ???

Can anyone help me on that ? I really don't know whats happening ?

The error that i have is : The file can't be found. But like I said, when using the domain controler user for impersonation, its working ???



    imp = new Impersonation(session["DBMASTER"], session["DBMASTER_PWD"]);


    DirectoryInfo di = new DirectoryInfo(session["INSTALLLOCATION"] + @"Sources");

    FileInfo[] rgFiles = di.GetFiles("*.dtsx");

    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

    foreach (FileInfo fi in rgFiles)


      string pkg = session["INSTALLLOCATION"] + @"Sources\" + fi.Name + "";

      fileName = fi.Name;

      Package p = app.LoadPackage(pkg, null);

      p.Connections["cnnGestion_Intervention"].ConnectionString = "Data Source=" + session["DBSERVER"] + ";Initial Catalog=" + session["DBNAME"] + ";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

      p.Connections["cnnGestion_InterventionDW"].ConnectionString = "Data Source=" + session["DWSERVER"] + ";Initial Catalog=Gestion_InterventionDW;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

      p.Connections["cnnGestion_InterventionCUBE"].ConnectionString = "Data Source=" + session["SSASSERVER"] + ";Initial Catalog=SPU_CUBE_SSAS;Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;";

      if (!app.FolderExistsOnDtsServer(@"MSDB\SPU_CUBE_SSIS", session["SSISSERVER"]))


       app.CreateFolderOnDtsServer("MSDB", "SPU_CUBE_SSIS", session["SSISSERVER"]);


      if (!app.ExistsOnDtsServer(@"MSDB\SPU_CUBE_SSIS\" + p.Name + "", session["SSISSERVER"]))


       app.SaveToDtsServer(p, null, @"MSDB\SPU_CUBE_SSIS\" + p.Name + "", session["SSISSERVER"]);



   catch (Exception ex)


    MessageBox.Show("Error :\n" + ex.Message + " : " + fileName + " : " + ex.StackTrace.ToString());

    return ActionResult.Failure;






   return ActionResult.Success;

Richard Martin Web Developer / BI Developer

<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure