March 9, 2011 at 10:12 am
Hi
I'm working on a simple SSIS package that runs contains and Execute SQL Task, which executes a direct input T-SQL command to execute a stored procedure. The stored procedure contains 2 input parameters ( both dates) to create data warehouse time dimension records.
The issue I have is that the stored procedure executes correctly, but SSIS reports that the task failed??
(I know it succeeds because a) the records are generated and b) using SQL Profiler, the error result is '0 - OK'.
FYI:
I've defined the Execute SQL Task to use parameters, these are of a data type a 'DATE'. The direct Input command that I have is:
EXECUTE [etl].[up_time_dim_build] @p_start_date = ? ,@p_end_date = ?
Does anyone have any pointers as to why it would be saiying that it has failed?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 9, 2011 at 12:28 pm
Are there any errors?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2011 at 2:38 am
no. SSIS reports
Progress: Executing query "EXECUTE [etl].[up_time_dim_build] @p_start_date =". - 100 percent complete
Task SQLT - etl up_time_dim_build failed
.
thats it. The stored procedure however, was executed and added records to the db table, that I expected it to add.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 10, 2011 at 2:51 am
So the task turns red and there is no message in the output window that starts with "Error:"?
Did you configure anything for a resultset?
Are there properties set for the task that state the task must fail?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2011 at 3:22 am
no - nothing starting Error:
I haven't configured a result set, as nothing should be returned and I dont believe i set the task to fail regarldless of result, but I will go back and check
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 10, 2011 at 6:19 am
(Doh! School boy error)
Keon, Well done! It appears I had the Force Execution result set to failure! I can only assume it's something I've done in testing a while back. Thanks for the help :w00t:
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 10, 2011 at 6:21 am
dave-dj (3/10/2011)
(Doh! School boy error)Keon, Well done! It appears I had the Force Execution result set to failure! I can only assume it's something I've done in testing a while back. Thanks for the help :w00t:
Great! Glad you figured it out.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply