I am writing an SSIS package to collect sql agent job execution history to load into a staging table in a data warehouse. I am a newbie, so I may just need a different approach. I have finished collecting the actual job information like name, job_id etc. I am working on a data flow to load the job information into the staging table I have in my data warehouse. I have a foreach container that loops over the dataset of jobs for the remote server. In the foreach I have a dataflow that sets the sql command for the 'ado net data source' using an expression to pull the job_id out of a variable scoped at my foreach container. At first I tried using parameter resulting in utter failure. "exec msdb.dbo.sp_help_jobhistory @job_id = '"+ @[User::job_id] +"', @Mode = 'Full'" I ended up turning off Validate External Metadata, which helped with getting past the validation phase, but now I receive the following 2 errors. [SSIS.Pipeline] Error: component "DS Job Exec History" (602) failed the pre-execute phase and returned error code 0xC0202005. [DS Job Exec History [602]] Error: Column "operator_netsentname" cannot be found at the datasource. I will say that writing a Data Flow for each job is just out of the question. There are too many and I want to use this to monitor people creating new jobs as well as the failures of jobs across our systems. Any help is appreciated. Dougdweh01

I created a package that executed the sp_help_jobhistory stored procedure successfully. Here are the details: - A single data flow with a ADO NET Source and OLE DB Destination. - The ADO NET Source connects to the msdb database, and runs the following SQL command. SET FMTONLY OFF DECLARE @job_id nvarchar(128) exec msdb.dbo.sp_help_jobhistory @job_id = @job_id, @Mode = 'Full' SET FMTONLY OFF statement is necessary; otherwise the package fails with "Error: Column "operator_netsentname" cannot be found at the datasource". Also, I declared the SSIS variable in the T-SQL to avoid validation errors. - Configured the OLE DB Destination to create a new table. - In the CREATE TABLE statement (OLE DB Destination), changed the step_name column length to 255. Otherwise, the package failed due to truncation error for this column.Carla Sabotta