• There is an alternative (and better, because no temp tables are involved) way of doing this.

    In your OLEDB Source, enter the following in the SQL command text:

    EXEC msdb.dbo.sp_get_sqlagent_properties

    with result sets

    ((

    auto_startint,

    msx_server_name[varchar] (256),

    sqlagent_typeint,

    startup_account[varchar] (256),

    sqlserver_restartint,

    jobhistory_max_rowsint,

    jobhistory_max_rows_per_jobint,

    errorlog_file[varchar] (512),

    errorlogging_levelint,

    error_recipient[varchar] (256),

    monitor_autostartint,

    local_host_server[varchar] (256),

    job_shutdown_timeoutint,

    cmdexec_account[varchar] (256),

    regular_connectionsint,

    host_login_name[varchar] (256),

    host_login_password[varchar] (256),

    login_timeoutint,

    idle_cpu_percentint,

    idle_cpu_durationint,

    oem_errorlogint,

    sysadmin_only[varchar] (256),

    email_profile[varchar] (256),

    email_save_in_sent_folder[varchar] (256),

    cpu_poller_enabledint,

    alert_replace_runtime_tokens int))

    That gets you all of the data except for the server name, which you already have as a variable from your Foreach loop. Add it to the data flow as a derived column & map to your target table.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.