MYSQL Linked server in SQL Server

-> Download the installation file of MySQL Connector/ODBC 5.3 from https://dev.mysql.com/downloads/connector/odbc/5.3.html.

Blog77_1Blog77_2Blog77_3Blog77_4Blog77_5

-> Once installed, create a System DSN in ODBC Data Sources (64-bit) as below,

Blog77_6Blog77_7Blog77_8Blog77_9

-> Select the required database and test the connection to ensure it works.

-> Once the datasource is created. Create a linked server as below,

Blog77_10Blog77_11

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Creating DTSX files from existing SQL Services Integration packages

-> In one of my project I had to migrate SQL Server Integration Services packages from SQL Server 2008 R2 to SQL Server 2016.

-> I planned to export the existing SQL Server Integration Packages from SQL Server 2008 R2 as DTSX files and then import it into SSDT.

-> But I had close to 250 + Packages and exporting each one will take more time.

-> I used below script and executed in msdb database.


set nocount on
select foldername as FolderName,
[name] as PackageName
into #PackageNames
from msdb.dbo.sysssispackages pkg
join msdb.dbo.sysssispackagefolders fld
on pkg.folderid = fld.folderid
order by FolderName, PackageName
select 'dtutil /SQL '
+ case
when len(FolderName) = 0 then '"'
else '"'+FolderName + '\'
end
+ PackageName + '"'+
+ ' /COPY FILE;'+'"'+'C:\temp\SSIS_Packages\'
+ case
when len(FolderName) = 0 then ''
else FolderName + '\'
end
+ PackageName
+ '.dtsx' + '"'
+ ' /QUIET' --suppresses prompt to delete if an older file with same name exists
from #PackageNames
drop table #PackageNames

-> Copy the output and execute it in command prompt. You can also save it as a batch file and execute it. But be sure to check the output to make sure there are no errors.

-> I received below message for some packages,

The package SSISpackagename can not be saved to the file system since its protection level is server storage. Use the encrypt action to change the protection level.

-> I had to manually export it and select the protection level as appropriate. In my case I just had 3 packages, so manual method for those packages were best fit for me.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

SQL Server Reporting Services – The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.

-> On one of my SQL Server Reporting Services server, I could see below warnings in Event Viewer.

Blog76_1.png

Blog76_2.png

Log Name: Application
Source: Report Server Windows Service
Date: 18/09/2019 9:44:16 AM
Event ID: 110
Task Category: Management
Level: Warning
Keywords: Classic
User: N/A
Computer:
Description:
The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”&gt;
<System>
<Provider Name=”Report Server Windows Service” />
<EventID Qualifiers=”0″>110</EventID>
<Level>3</Level>
<Task>5</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime=”2019-09-17T23:44:16.691430500Z” />
<EventRecordID>6489</EventRecordID>
<Channel>Application</Channel>
<Computer></Computer>
<Security />
</System>
<EventData>
<Data>UrlRoot</Data>
<Data>RSReportServer.config</Data>
</EventData>
</Event>

-> Edit file RSReportServer.config from location D:\Program Files\Microsoft SQL Server\MSRS<Version>.<Instance>\Reporting Services\ReportServer folder.

-> Search for <UrlRoot> in that file. In my case, it was blank,

Blog76_3.png

-> Open Reporting Services Configuration Manager. Navigate to “Web Portal URL” or “Report Manager URL”. Copy the entry in “URLs:” and paste it in RSReportServer.config file between <UrlRoot> </UrlRoot> as shown below,

Blog76_4.png

-> Restarting reporting service after the above change stopped the warning.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.