Saturday, March 16, 2013 2:16 PM
Here is the output of SSIS Execute SQL task.
Package Validation Error (Package Validation Error)
Error at Data Flow Task [ADO NET Destination ]: 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.
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]GOWhen executing from SSMS it is working fine.
- Changed Type SSISJoostMicrosoft Community Contributor, Moderator Saturday, March 16, 2013 2:28 PM more appropriate type
Saturday, March 16, 2013 2:25 PMCheck 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
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
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?
Saturday, March 16, 2013 2:57 PM
Any Idea Guys, I stuck in here.
Saturday, March 16, 2013 3:16 PM
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.
Sunday, March 17, 2013 12:11 AM
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
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