February 1, 2016 at 11:40 pm
How can make this query run in SSIS OleDbSource
and the results go to Oledbdestination with some audit columns added in between.When I hit preview in the OleDBSource it shows me the attached error in the screenshot.
This is my query:
create table #tempTable(
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)
INSERT INTO #tempTable
EXEC msdb.dbo.sp_get_sqlagent_properties
select @@SERVERNAME as ServerName,* from #tempTable
Attached is the screenshot of the error.
I will be running this query against many servers in SSIS(inside a foreachloop container) so I also need to drop the #tempTable.
Thanks
February 2, 2016 at 6:21 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply