0

The SQL Server Best Practices Analyzer noted that my Integration Services' logging table is in a system database. It recommends that I move this to a non-system database. (See here)

How do I make this change? I wasn't able to find a setting for this. I'm using SQL Server 2012.

  • From the same link - If the table does not already exist, Integration Services creates this table for you when you select a SQL Server database as your logging destination. Does it help ? – Kin Shah Dec 2 '15 at 17:07
1

Blowing Kin's comment up to an answer...

Logging in the package deployment model (2005, 2008/2008R2 and potentially 2012/2014/2016 depending on how the project is defined) is done through an explicit request within each package.

A package looks something like this. At the bottom, you see 3 entries under Connection Managers: msdb, SO_34033143 and NotMsdb. The middle is a flat file connection manager, the other two are OLE DB connection managers. All a Connection Manager does is define the specifics of the entity the package is going to work with: this database on that server logging in as user123. A CSV named XYZ.csv, etc.

enter image description here

It's important to note that the name given to a Connection Manager may have no bearing on what it is actually connecting to. For example, the one named "msdb" is actually pointing to tempdb because I'm lazy.

If you right click in the grey background between the Data Flow Task and the Connection Managers, you should get a context sensitive menu and pick Logging

enter image description here

In the resulting screen, you should see a menu pop up that has on the right hand side "SSIS Log Provider for SQL Server" checked. The third column indicates the connection manager that is being used to log SSIS.

enter image description here

What you'll want to do and again, this is for every package, go in and change the connection manager from the thing that is pointing to msdb and either use an existing connection manager or add a dedicated connection manager into the packages.

enter image description here

Personally, in my pre-2012 environments, I advocated for an SSISConfig/SSIS_Config database. I used that for logging and configuration across a given tier/class of servers (dev/stage/prod).

If you're handy with scripting, you can programatically open every SSIS package and change the logging provider but it's not for the faint of heart. I don't believe I have that code online but if you're super desperate, ping me via comment and I'll see if I can find that hard drive.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.