Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Execute SQL task fail for Droping a table if exists?

Answered Execute SQL task fail for Droping a table if exists?

  • Saturday, March 16, 2013 2:16 PM
     
      Has Code
    Sign In to Vote
    0

    Here is the output of SSIS Execute SQL task.

    ===================================

    Package Validation Error (Package Validation Error)

    ===================================

    Error at Data Flow Task [ADO NET Destination [2619]]: Failed to get properties of external columns. The table name you entered may not exist, or you do not have SELECT permission on the table object and an alternative attempt to get column properties through connection has failed. Detailed error messages are:
    Invalid object name 'dbo.Cam_Dim'.
    No column information is found for table "dbo"."Cam_Dim" by querying System.Data.SqlClient.SqlConnection.

    Error at Data Flow Task [SSIS.Pipeline]: "component "ADO NET Destination" (2619)" failed validation and returned validation status "VS_ISCORRUPT".

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

     (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------
    Program Location:

       at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
       at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)
       at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)
       at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
       at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

    Any Idea how to deal with this.

    This is the sql command

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Cam_Dim]')and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Cam_Dim]GO
    When executing from SSMS it is working fine.

     

All Replies

  • Saturday, March 16, 2013 2:25 PM
     
     
    Sign In to Vote
    0
    Check the permissions of the SQL Agent runtime account.  The task is likely working in SSMS because your account has permissions, but when running through a SQL agent job, it will inherit the permissions of the SQL Agent unless a proxy account is specified or a different username is specified in the connection string.
     
  • Saturday, March 16, 2013 2:28 PM
     
     
    Sign In to Vote
    0

    Thanks for the mail.

    I'm running it from SSIS interface using the local user in connection which is the same I'm using for SSMS.

     
  • Saturday, March 16, 2013 2:32 PM
     
     
    Sign In to Vote
    0

    I created the table manually using ssms and now the task is running fine. I don't think it is with permissions.

    Can you tell me how to delete a table if exists using SSIS Execute SQL Task?

    Thanks

     
  • Saturday, March 16, 2013 2:57 PM
     
     
    Sign In to Vote
    0

    Any Idea Guys, I stuck in here.

    Thanks

     
  • Saturday, March 16, 2013 3:16 PM
     
     Answered
    Sign In to Vote
    0

    In execute SQL Task you can use same code like in SSMS, just use "DROP TABLE".

    You can also script in SSMS "If EXISTS ... DROP ..." statment and use in in the task.

    If you create table in SSIS execute SQL Task, by default validation for all tasks is executed first, before any task is executed.

    You need to set delay validation for the data flow.


    • Edited by Piotr Palka Saturday, March 16, 2013 3:17 PM
    • Marked As Answer by webdev1986 Sunday, March 17, 2013 6:40 AM
    •  
     
  • Sunday, March 17, 2013 12:11 AM
     
     
    Sign In to Vote
    0

    Would you mind performing the below test in a new SSIS package and let us know if you encounter any errors:

    1) Add connection to database you are working with

    2) Add Execute SQL Task to Control flow.

    - Edit the Execute SQL Task, selecting the appropriate connection, then add a simple CREATE TABLE statement for the SQL query.

    3) Add a second Execute SQL Task.  Drag connection from first Execute SQL task to new task.

    - Edit new task, select appropriate connection and add a drop statement for the table just created.

    Execute the package and let us know if there are any errors.

     
  • Sunday, March 17, 2013 6:37 AM
     
     Answered
    Sign In to Vote
    0

    There is no errors. I figured out a complex way to do this.

    I created task that creates table. On failure of this task drop table and then on sucess of the second task then I did the create table task again. This seems awkward but it is working fine for me.

    • Marked As Answer by webdev1986 Sunday, March 17, 2013 6:37 AM
    •