Tag: Microsoft | Page 6

How to Create SSIS Package in Visual Studio 2017

This posting will show you how to create an SSIS package using the legacy Package Deployment Model in Visual Studio 2017.

Please be sure before you do anything that you have SQL Server Data Tools for Visual Studio 2017 installed. You can find the SSDT download link below. Install it within the instance of your Visual Studio instead of creating a new one. With SSDT, you will be able to design and deploy any integration services package with the same ease as you would develop an application in Visual Studio.

From the top menu, go to File, and select New and Project.
Under New Project window, select Business Intelligence, Integration Services and Integration Services Project.Enter the name of your project (e.g., SSIS-ITNota) and select a folder where you want to save the project (e.g., C:\Users\ITNota) and click OK.
As we are using a package deployment model, we need to convert the project before we’re doing anything else. Under Solution Explorer, right-click on the project and click on Convert to Package Deployment Model.

You will see a dialog box with the message below. Just click OK to proceed.

        This will change the project to use the package deployment model. You
        cannot perform this conversion if you are using any features unique to
        the project deployment model, such as parameters.
 
        Before conversion, all packages in this project will be checked for
        compatibility with the package deployment model. This may take
        several minutes if the project contains many packages.
 
        Click OK to proceed.

SSIS Convert to Package Deployment Model dialog box

If you do the conversion in the beginning of your project (recommended), you should see your project and package passed the conversion. Click OK to complete the conversion.

SSIS Convert to Package Deployment Model Passed

You should see that after the project’s name there’s an extra note that indicates it’s a package deployment model after the conversion is complete.

Here are the highlights of the differences between Project Deployment Model and Package Deployment Model side-by-side.
Rename Packages.dtsx to something more meaningful for your project.

This should be enough to get you started with creating an SSIS package with Visual Studio 2017.

If you need to deploy your SSIS package to a different version of SQL Server, make sure you set the TargetServerVersion to the SQL Server version on your host server.

And check this post to avoid one of the most common mistake that developers make when deploying the package to production.

For a more elaborate explanation, Microsoft has designed a great Integration Services tutorials on how to create an ETL package and deploy it, which you can find at the bottom of this post.

Download

SSDT Download for Visual Studio 2017

How to Build SSIS Package for Different SQL Server Version with Visual Studio 2017 and SSDT

After deploying an SSIS package built with Visual Studio 2017, we encountered a surprise when running it via a Command Prompt:

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.
 
C:\Windows\system32>"D:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /File "D:\PROG\bin\SSIS_Extract.dtsx" /Conf "D:\PROG\bin\SSIS_Extract.dtsConfig"
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.2100.60 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
 
Started:  5:04:56 PM
Error: 2018-11-26 17:04:56.20
   Code: 0xC001700A
   Source: SSIS_Extract
   Description: The version number in the package is not valid. The version number cannot be greater than current version number.
End Error
Error: 2018-11-26 17:04:56.20
   Code: 0xC0016020
   Source: SSIS_Extract
   Description: Package migration from version 8 to version 6 failed with error
0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.".
End Error
Error: 2018-11-26 17:04:56.21
   Code: 0xC0010018
   Source: SSIS_Extract
   Description: Error loading value "<DTS:Property xmlns:DTS="www.microsoft.com/
SqlServer/Dts" DTS:Name="PackageFormatVersion">8</DTS:Property>" from node "DTS:
Property".
End Error
Could not load package "D:\PROG\bin\SSIS_Extract.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Source: SSIS_Extract
Started:  5:04:56 PM
Finished: 5:04:56 PM
Elapsed:  0.203 seconds
 
C:\Windows\system32>

Pay attention to this section of error message:

Description: Package migration from version 8 to version 6 failed with error
0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.".

What’s the real problem? It turns out that the SQL Server version in the production server is older than the version of the tool. The package was built with SSIS designer for Visual Studio 2017 (version 8), however the target database is SQL Server 2012 (version 6). So what do we need to do?

In the past, we needed to install an older version of Business Intelligence Development Studio that matches the SQL Server version we’re targeting. Because of this, many developers still have the habit of having several versions of Visual Studio installed on their machines just to handle every version of SSIS package. Fortunately with the newer of Visual Studio with SSDT, we can target the latest SQL Server (2017) down to 2012.

How do we target an older version of SQL Server?

Before we begin with the steps, make sure your Visual Studio 2017 has the SQL Server Data Tools installed. Otherwise, check the SSDT download at the bottom of this post.

How to target a specific version of SQL Server in SSIS

In your project (e.g., Temp1), right-click on the project name and click Properties (or press Alt-Enter).
Once the Property Page is opened, click on the Configuration Properties and check the TargetServerVersion.
Click on the drop-down list and select the correct SQL Server version on the machine where you want to deploy your SSIS package and click OK.
Rebuild your SSIS package and re-deploy the package.

This time you will be able to run the package without any problems.

Update 2 May 2019: Since the posting of this article, apparently Microsoft removed support for SQL Server 2012 in SSDT version 15.8.1, but added it back in version 15.9.0. Check your SSDT version if you don’t see an option to target SQL Server 2012.

Make sure you pay attention to the notes marked as Important. For example, before installing SSDT for Visual Studio 2017 (15.9.1), you need to uninstall Analysis Services Projects and Reporting Services Projects from Tools, Extensions and Updates and close all Visual Studio instances.

Download

SSDT for VS 2017 Installer

How to Troubleshoot IIS 500 Error for ASP Websites

You try to load your classic ASP (Active Server Pages) website and all you can see in your browser is the all too familiar display of 500 Internal Server Error.

This page isn't working

WEBSITE is currently unable to handle this request

HTTP ERROR 500

What is 500 Internal Server Error

First of all, the 500 Internal Server Error is a very general HTTP status code that indicates something has gone wrong on the web server without any specifics on the exact problem.

This instruction is limited to solving a 500 error on IIS server for classic ASP websites, however some other instructions that’s related to IIS in general will help for troubleshooting IIS server for .NET applications as well.

As 500 Server Error message is very often vague, the task now is to find a more specific error message that will help us further analyze and troubleshoot the issue.

Enable Debugging and Send Errors to Browser

If the website is not in a production environment, the easiest way to do it is to enable the ASP Debugging Properties and send the errors to the browser by following these steps:

Launch Internet Information Services (IIS) Manager and on the left pane, select your ASP website and click on the ASP icon (Configure properties for ASP applications).
Under the ASP window, expand the Debugging Properties and set Enable Client-side Debugging, Enable Server-side Debugging, and Send Errors To Browser to True and click Apply.

Now, when you refresh your ASP website, you will see a more descriptive error message on your browser such as the following example:

System.Xml error '80131509'

There is an error in the XML document.

/itnota/failed-page.asp, line 289

With this information, you can right away open the file (e.g., itnota/failed-page.asp) and check line number 289. Again, this option is not recommended for a production website that’s still actively used and available for public since it exposes too much information publicly. The ideal way to do it is to replicate your production environment on a staging and try to reproduce the error on a lower environment.

Check IIS Log

Another way to check 500 Internal Server Error for your ASP website is by viewing IIS error logs. This is especially true if your ASP website is a public website.

Where to find the IIS Logs?

By default, the log for your website is stored on %SystemDrive%\inetpub\logs\LogFile, but you should not use this settings for a production environment. Always set a custom path for your logs so your websites are easier to maintain in the long run. If you have a different physical hard drive for logs, even better. Make sure it’s set to Enabled.

For the sake of example, the log files are located in folder D:\Logs\ITNota.com:

From the IIS log example below, the easiest way is to look for a pipe character (“|”) on the date and time when the error happens.

The error message can be broken down into two parts:

/itnota/failed-page.asp id=2087216&catId=749272
|289|80131509|There_is_an_error_in_the_XML_document.

The first part indicates the problematic file with its parameters which can be reconstructed by replacing the white space with a question mark (“?”) like so:

  /itnota/failed-page.asp?id=2087216&catId=749272

The second part which starts with a pipe character (“|”) is the line number that caused the error, the error code, and the error description:

  |289|80131509|There_is_an_error_in_the_XML_document.

From the above information, we are looking at the following information:

Item	Description
Filename	/itnota/failed-page.asp
Line number	289
Error code	80131509
Error description	There_is_an_error_in_the_XML_document.

Check the affected file by opening it in Visual Studio Code or any IDE.

If you use Visual Studio Code to read and write ASP or VBScript, click here for a way how to colorize your ASP code.

How to Enable TLS 1.2 as the Default Security Protocol on Windows Servers

Transport Layer Security (TLS) are cryptographic protocols designed to provide communications security over a computer network, typically between a website and a browser.

TLS 1.0 and its deprecated predecessor, SSL are vulnerable to some well-known security issues such as POODLE and BEAST attacks. According to NIST, these vulnerabilities cannot be fixed or patched, therefore all companies, especially banks and other financial institutions who are notoriously slow in upgrading theirs systems, need to upgrade to a secure alternative as soon as possible, and disable any fallback to both SSL and the older TLS 1.0.

As of 30 June 2018, SSL and TLS 1.0 should be disabled and more secure encryption protocol such as TLS 1.2 (or at the minimum TLS 1.1) is required to meet the PCI Data Security Standard (PCI DSS) for safeguarding payment data.

The next question then how on do we enable TLS 1.2 on Windows Servers? Especially on older servers such as Windows Server 2008 as many companies are not on the latest and greatest operating systems?

This post will address what to look for and how to enable TLS 1.2 as the default protocol for Windows Server 2012 R2 or older.

IMPORTANT: As always and it’s worth repeating, you need to backup your current registry settings before attempting any of these changes on your servers.

Enable TLS 1.2 on Windows Servers 2008 SP2 or later

The blanket statement to enable your TLS 1.2 on your server from Windows Server 2008 SP2 or later. Microsoft provided an update to add support for TLS 1.1 and TLS 1.2 for Windows Server 2008, but it requires Windows Server 2008 SP2 installed.

So just to state the obvious, TLS 1.1 and TLS 1.2 are not supported for 32-bit Windows Server 2008 SP1.

Launch regedit.exe.

In registry, go to:

  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols

Create a new DWORD entry with a name TLS 1.2 and create another subkey Client and Server.
Under the subkey Server, create another DWORD Enabled with a value of 1.
Still under the subkey Server, create a DWORD DisabledByDefault with a value of 0.
You must create a subkey DisabledByDefault entry in the appropriate subkey (Client, Server) and set the DWORD value to 0 since this entry is set to 1 by default.
Reboot the server and test.

Enable TLS 1.2 on .NET Framework 3.5 (including 2.0)

.NET Framework 3.5 or earlier did not originally provide support of applications to use TLS System Default Versions as a cryptographic protocol. However, for Windows Server 2012 R2, check if KB3154520 is installed (or KB3154519 for Windows Server 2012; KB3154518 for Windows Server 2008 R2; KB3154517 for Windows Server 2008 SP2).

How to check the KB updates

Right-click on the Windows button and select Programs and Features.
On Programs and Features window, click onthe View installed updates on the left pane.
You will see a list of the updates that you can narrow down or do a very specific search by using the Search Installed Updates box. You can type in the KB number (i.e., “KB3154520”).
If the corresponding KB is already installed, we just need to enable it via registry change. Otherwise, you need to install the patch from either of the links for Windows Server 2012 R2 (or use the same corresponding links above for earlier versions of Windows Server).

Registry Change

Launch regedit.exe.

Go to:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727

Create a new entry SystemDefaultTlsVersions with a DWORD value set to 1.
Create a new entry SchUseStrongCrypto with a DWORD value set to 1.

Go to:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319

Create a new entry SystemDefaultTlsVersions with a DWORD value set to 1.
Create a new entry SchUseStrongCrypto with a DWORD value set to 1.

For 64-bit OS, the same changes also needed for the following locations:

  HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v2.0.50727

Create a new entry SystemDefaultTlsVersions with a DWORD value set to 1.
Create a new entry SchUseStrongCrypto with a DWORD value set to 1.

Go to:

  HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319

Create a new entry SystemDefaultTlsVersions with a DWORD value set to 1.
Create a new entry SchUseStrongCrypto with a DWORD value set to 1.
Test.

Enable TLS 1.2 as default for WinHTTP

This may be applicable for any Classic ASP or VB6 applications that use WinHTTP. Prior to Windows 10 and Windows Server 2016, TLS 1.1 or 1.2 is not enabled by default for client-server communications through WinHTTP.

To set TLS 1.2 by default, do the following:

Create a registry entry DefaultSecureProtocols on the following location:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\WinHttp

Set the DWORD value to 800 for TLS 1.2.

For 64-bit OS, repeat step 1 and 2 on the following location:

  HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Internet Settings\WinHttp

Reboot the server and test.

Windows 10 and Windows Server 2016/2019 support TLS 1.2 for client-server communications by using WinHTTP.

Download

Solving the TLS 1.0 Problem (MS Word document)

How to Fix SMTP 550 5.7.1 Unable to Relay Error on Windows Server 2012 R2

Here is one way of fixing an SMTP error on Windows Server 2012 when you see an error message similar to the one below:

Error Message

System.Exception: SMTP error sending: Client Service Setup Error on SERVERNAME ---> System.Web.HttpException: The server rejected one or more recipient addresses. The server response was: 550 5.7.1 Unable to relay for [email protected]    ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException: The server rejected one or more recipient addresses. The server response was: 550 5.7.1 Unable to  relay for [email protected]       --- End of inner exception stack trace ---     at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)      at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)     at System.Web.Mail.SmtpMail.LateBoundAccessHelper.CallMethod(Object  obj, String methodName, Object[] args)     --- End of inner exception stack trace ---     at System.Web.Mail.SmtpMail.LateBoundAccessHelper.CallMethod(Object obj, String methodName, Object[] args)     at System.Web.Mail.SmtpMail.CdoSysHelper.Send(MailMessage  message)     at System.Web.Mail.SmtpMail.Send(MailMessage message)     at ClientServiceSetup.CommonFunctions.SendMail(MailMessage objMail)     --- End of inner exception stack trace ---

Steps to Add Localhost to the SMTP Relay

Launch IIS 6.0 Manager.
Right-click on the STMP Server (on this example it’s named default, but it could be named something else) and select Properties.
Click on the Access tab and at the very bottom under Relay restrictions, click on Relay button.
Select Only the list below, click on Add… button and under Single computer and IP Address, type in 127.0.0.1 and click OK.
Check Allow all computers which successfully authenticate to relay, regardless of the list above. and click OK, and OK one more time to get out the Properties window.
Re-test and that should be it.

How to Create SSIS Package in Visual Studio 2017

Download

Further Reading

How to Build SSIS Package for Different SQL Server Version with Visual Studio 2017 and SSDT

How to target a specific version of SQL Server in SSIS

Download

Further Reading

How to Troubleshoot IIS 500 Error for ASP Websites

What is 500 Internal Server Error

Enable Debugging and Send Errors to Browser

Check IIS Log

Where to find the IIS Logs?

How to Enable TLS 1.2 as the Default Security Protocol on Windows Servers

Enable TLS 1.2 on Windows Servers 2008 SP2 or later

Enable TLS 1.2 on .NET Framework 3.5 (including 2.0)

How to check the KB updates

Registry Change

Enable TLS 1.2 as default for WinHTTP

Further Reading

Download

How to Fix SMTP 550 5.7.1 Unable to Relay Error on Windows Server 2012 R2

Error Message

Steps to Add Localhost to the SMTP Relay

Further Reading

Recent Posts

Download

Further Reading

How to target a specific version of SQL Server in SSIS

Download

Further Reading

What is 500 Internal Server Error

Enable Debugging and Send Errors to Browser

Check IIS Log

Where to find the IIS Logs?

Enable TLS 1.2 on Windows Servers 2008 SP2 or later

Enable TLS 1.2 on .NET Framework 3.5 (including 2.0)

How to check the KB updates

Registry Change

Enable TLS 1.2 as default for WinHTTP

Further Reading

Download

Error Message

Steps to Add Localhost to the SMTP Relay

Further Reading

Recent Posts

Tags