SQL agent job for SSISDB catalog packages

Question

Post reply

SQL agent job for SSISDB catalog packages

sqlfriend

SSC Guru

Points: 52794
More actions
June 14, 2016 at 12:19 pm

#400277

For configuring SQL agent job for SSISDB catalog job, I see there is a logging level drop down like basic etc.
where is the log file stored, where I can view it?
Also on the advanced tab, there is a SQL server integration services Package output file, and let you browse and fill out the file name, what is the output file?
Is it the basic log file mentioned above?
Thanks,

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Phil Parkin

SSC Guru

Points: 247180

June 14, 2016 at 12:23 pm

#1884999

Logs are stored in SSISDB itself.

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

So I guess can view the results from report or more advanced to query the SSIS db.

Then what is that output file on the advanced tab?

Also I guess the database SSISDB will get big soon for the every day logging.

is there a way that setup the retention for the db to purge old data?

Thanks

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 3

sqlfriends (6/14/2016)
So I guess can view the results from report or more advanced to query the SSIS db.
Then what is that output file on the advanced tab?
Also I guess the database SSISDB will get big soon for the every day logging.
is there a way that setup the retention for the db to purge old data?
Thanks

Yes you can see a retention period in days. See link below.

https://msdn.microsoft.com/en-ca/library/hh479588.aspx#Configuration

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

sqlfriends (6/14/2016)
So I guess can view the results from report or more advanced to query the SSIS db.
Then what is that output file on the advanced tab?
Also I guess the database SSISDB will get big soon for the every day logging.
is there a way that setup the retention for the db to purge old data?
Thanks

Never used the output file.

There is, by default, a SQL Agent job which runs every day to keep the size of SSISDB under control (it removes 'old' stuff). The job is called SSIS Server Maintenance Job. If you don't run this regularly and have a lot of packages running, things go south rapidly in terms of disk space and overall SSISDB responsiveness.

I also recommend reducing the default retention period (365 days, I think).

sqlfriend SSC Guru Points: 52794 More actions · Answer 5

Thanks, found the maintenance job, I will certainly change it to fewer than 365 days.

For logging, I see in package design view, you can still design a log file to output to file system.

That was I usually do when using package deploy mode.

for project deploy mode, since it is using the database to store the log file, I guess maybe I don't need to configure logging in SSDT design screen any more.

Thanks,