MSSQLFUN - Explore and Experience SQL Server

Other Linked Profiles :-

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

System.ConsoleColor error while running powershell in SQL agent job step

December 4, 2017 8:02 am / Leave a Comment

Issue: While running powershell in SQL agent job step, I am getting below error: –

A job step received an error at line 3 in a PowerShell script. The corresponding line is ‘$space.ForegroundColor = $host.ui.rawui.ForegroundColor’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Exception setting "ForegroundColor": "Cannot convert null to type "System.ConsoleColor" due to invalid enumeration values. Specify one of the following enumeration values and try again. The possible enumeration values are "Black, DarkBlue, DarkGreen, DarkCyan, DarkRed, DarkMagenta, DarkYellow, Gray, DarkGray, Blue, Green, Cyan, Red, Magenta, Yellow, White"." ‘. Process Exit Code -1. The step failed.

Solution: This error occurs while you are using clear screen option in script part of SQL agent job step. Please check your script and remove cls or clear-host keywords.

I removed it and it start working fine.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Other Linked Profiles :-

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

SSISDB for SQL Server Integration Services

November 6, 2017 9:13 am / Leave a Comment

Microsoft introduced some major changes in SQL Server Integration Services with SQL Server 2012.

Brand new “SSISDB” database launched as SSIS catalog “A New repository database of Integration Services”. This new repository database brings a lot of abilities for developers, testers and administrators.

Prior to SQL 2012 all SSIS packages are stored either in MSDB or in file system. Many times it becomes critical decision to decide where should our package or config files store. This also makes Deployment & administration difficult.

With SSISDB, Packages can be directly deployed to SSISDB using SQL Server Data Tools for BI Studio. Packages keep a deployment history so you can even rollback some revisions of your package.

Creating SSISDB

SSISDB is not installed by default with installation of SQL Server 2012 or above. When you connect to SQL Server using SSMS, you will find new folder “Integration Services Catalogs”. The catalog is not set up by default, you will have to create it.

When you click on create catalog, below window will appear. You need to select enable CLR integration option to use SSIS catalog. Enter the password to protect the database master key that is used for encrypting the catalog data. This password is very important & sensitive, so make sure to remember this password or save it in a secure location. The catalog automatically encrypts the package data and sensitive values. The catalog also automatically decrypts the data when you retrieve it.

When you click “OK”, SSISDB catalog will be created along with SSISDB database. SSISDB created by restoring “<Installation Directory>\MSSQL\120\DTS\Binn\SSISDBBackup.bak”. SSISDBBackup.bak backup file is available in installation directory after successful installation of Integration services. Although SSISDB is created automatically while enabling SSISDB catalog but still it will be considered as User database not as System database,

SSISDB Catalog default reports

SSISDB catalog comes with 4 different reports along with dashboard.

SSISDB catalog Dashboard

Rename SSISDB catalog database

You should not rename SSISDB because it will cause SSISDB catalog inaccessible.

When you rename SSISDB it will delink all your SSIS catalogs & packages. After rename, you will not find any package under “Integration Services Catalogs”. The best part is, when you rename it back to SSISDB, all your catalogs and packages will be visible again without any additional efforts.

Catalogs and Packages unvisible under “Integration Services Catalogs” after rename

Catalogs and Packages visible under “Integration Services Catalogs” after renaming back to original

Manage Size of SSISDB catalog database

Microsoft introduced SSISDB catalog database to keep trace of each execution, project version and other details which is very typical activity prior to SQL Server 2012. But all this thing can make your SSISDB database very large.

You can change SSISDB database catalog configuration to change retention window and other parameters, impacting its size. Catalog properties effect SSISDB database size as below:-

· Clean logs periodically (set to True)

· Retention period (set to specific number of days –the larger the number of days the more prevalent the problem could be)

· Periodically remove old versions (set to true)

· Maximum number of versions per project

Check Catalog Properties using SSMS

Check Catalog Properties using T-SQL

SELECT * FROM SSISDB.CATALOG.CATALOG_PROPERTIES

You can change catalog properties in above SSMS window or like below by using T-SQL

EXEC SSISDB.CATALOG.CONFIGURE_CATALOG RETENTION_WINDOW, <NO.OF DAYS>

SSIS Server Maintenance job

To maintain SSISDB database size according to retention configured above, SQL Server creates “SSIS Server Maintenance job” when we enable SSISDB catalog feature.

The job is responsible of maintaining history as per the retention window and maintains a maximum number of versions per project. Ensure Job is enabled on server.

“SSIS Server Maintenance job” Job has 2 Steps:-

1) Remove operation records

2) Remove old versions per project on basis of set retention

Types of Logging for SSIS server through SSISDB catalog

Logging Level	Description
None	Logging is turned off. Only the package execution status is logged.
Basic	All events are logged, except custom and diagnostic events. This is the default value.
RuntimeLineage	Collects the data required to track lineage information in the data flow. You can parse this lineage information to map the lineage relationship between tasks. ISVs and developers can build custom lineage mapping tools with this information.
Performance	Only performance statistics, and OnError and OnWarning events, are logged. The Execution Performance report displays Active Time and Total Time for package data flow components.
Verbose	All events are logged, including custom and diagnostic events.

Source: According to MSDN https://msdn.microsoft.com/en-IN/library/hh231191.aspx

You can change logging as per requirements using SSMS. If user need some specific logging, Customized logging option is also available.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Other Linked Profiles :-

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

DB Mail error “File attachment or query results size exceeds allowable value of 1000000 bytes”

November 1, 2017 8:07 am / Leave a Comment

Issue : One email report is working fine from long time but suddenly it failed today with below error.

Error :

Msg 22051, Level 16, State 1, Line 0

File attachment or query results size exceeds allowable value of 1000000 bytes.

Solution : Email report is failing at send mail because file attachment size is more than set limit of 1MB. We need to reconfigure DB Mail to change file attachment size limit.

Query Used to send email report :

EXEC msdb..sp_send_dbmail

@recipients = ‘rohit.garg@mssqlfun.com’,

@subject = ‘DB Report’,

@file_attachments = ’F:\Script\DBReport.csv’,

@body = @Final_Html,

@body_format =’HTML’

· Go to DB Mail Configuration

· Select “View or Change system parameters”

· You can find Maximum file size is set to 1MB

· I have changed it to 5MB & reconfigure it.

· DB Mail reconfigured for 5MB attachment file size

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Other Linked Profiles :-

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

SSMS error – Unable to Connect to SSIS – The Specified Service Does Not Exist as an Installed Service

October 23, 2017 4:37 pm / Leave a Comment

Issue: SQL Server Integration Service (SSIS) 2016 unable to connect from SQL Server Management Studio (SSMS) 17.0.

Error:

Connecting to the Integration Services service on the computer "<Server Name>" failed with the following error: "The specified service does not exist as an installed service.”

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

Check & Findings:

1) SSIS services is running

2) I am using Newer SSMS version in compare to SSIS version.

Solution:

You will get this error message when you try to connect SSIS services from later version SSMS.

In my case, I am trying to connect SQL Server 2016 SSIS from SSMS of version 17. I have download version 16 SSMS and installed it. SSIS connectivity is working fine from version 16 SSMS.

Link to Download different SSMS versions (16.X & 17.X) – https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-changelog-ssms

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Other Linked Profiles :-