"Execute SQL Task" in BIDS - 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 2008 Forums

SSIS and Import/Export (2008)

"Execute SQL Task" in BIDS

Author

Topic

Zix
Starting Member

31 Posts

Posted - 2010-01-27 : 20:14:23

I'm creating a workflow where data is downloaded from an ftp server, extracted then imported into some staging tables. I have it all working except the data import part. I think I am using the "Execute SQL Task" incorrectly.

I created the new task, defined the connection (just one (local) db in this environment), and chose the "Direct Input" option. I then entered the following truncate statements.


truncate table [dbo].[GaMLS_Staging_Commercial_2009] 
go

truncate table [dbo].[GaMLS_Staging_Acreage_2009]
go

truncate table [dbo].[GaMLS_Staging_SingleFamily_2009]
go

truncate table [dbo].[GaMLS_Staging_RentComm_2009]
go

truncate table [dbo].[GaMLS_Staging_MultiFamily_2009]
go

truncate table [dbo].[GaMLS_Staging_LandLot_2009]
go

truncate table [dbo].[GaMLS_Staging_Condo_2009]
go

truncate table [dbo].[GaMLS_Staging_RentRes_2009]
go

I can run these statements directly against the database and they run correctly. What do I need to do special to get them to run in the Execute SQL Task tool?

Thanks,

Clay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 03:11:12

whats the error you're getting?

Zix
Starting Member

31 Posts

Posted - 2010-01-28 : 07:01:56

I don't get an error. The "box" just turns red.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 07:09:33

then you show see the error message in show progress tab of the package

Zix
Starting Member

31 Posts

Posted - 2010-01-28 : 20:25:51

[Execute SQL Task] Error: Executing the query "truncate table [dbo].[GaMLS_Staging_Commercial_200..." failed with the following error: "Cannot find the object "GaMLS_Staging_Commercial_2009" because it does not exist or you do not have permissions.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I can query the table directly and get a results set. It happens to be NULL but the table does exist.

select * from dbo.GaMLS_Staging_Commercial_2009

Thanks!

Zix
Starting Member

31 Posts

Posted - 2010-01-28 : 20:40:29

I populated the table just for giggles then ran the truncate statement (via the BI workflow) and get the same problem.

Note that I can run the TRUNC statement manually and it works. Could there be a problem with the connection? I created a data source to the (local) database. I also create a new data source with the explicit server and instance names. Unfortunately I get the same errors.

Am I supposed to define the connection in some other way?

Zix
Starting Member

31 Posts

Posted - 2010-01-28 : 21:06:22

Answered my own question...yes, I have to define the database connection at each step of the workflow. Dont know why but doing so makes it work.

Thanks for the assist!

cc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources