Edit

SSIS migration assessment rules

  • Article

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

When planning a migration of on-premises SSIS to SSIS in Azure Data Factory (ADF) or Synapse Pipelines, assessment will help identify issues with the source SSIS packages that would prevent a successful migration.

Data Migration Assistant (DMA) for Integration Services can perform assessment of your project, and below are full list of potential issues, known as DMA rules as well.

[1001]Connection with host name may fail

Impact

Connection that contains host name may fail, typically because the Azure virtual network requires the correct configuration to support DNS name resolution.

Recommendation

You can use below options for SSIS Integration runtime to access these resources:

[1002]Connection with absolute or UNC path might not be accessible

Impact

Connection that contains absolute or UNC path may fail

Recommendation

You can use below options for SSIS Integration runtime to access these resources:

[1003]Connection with Windows authentication may fail

Impact

If a connection string uses Windows authentication, it may fail. Windows authentication requires additional configuration steps in Azure.

Recommendation

There are four methods to access data stores Windows authentication in Azure SSIS integration runtime:

  • Set up an activity-level execution context
  • Set up a catalog-level execution context
  • Persist credentials via cmdkey command
  • Mount drives at package execution time (non-persistent)

[1004]Connection with non built-in provider or driver may fail

Impact

Azure-SSIS IR only includes built-in providers or drivers by default. Without customization to install the provider or driver, the connection may fail.

Recommendation

Customize Azure-SSIS integration runtime to install non built-in provider or driver.

[1005]Analysis Services Connection Manager cannot use an account with MFA enabled

Impact

If you use SSIS in Azure Data Factory (ADF) and want to connect to Azure Analysis Services (AAS) instance, you cannot use an account with Multi-Factor Authentication (MFA) enabled.

Recommendation

Use an account that doesn't require any interactivity/MFA or a service principal instead.

AdditionalInformation

Configuration of the Analysis Services Connection Manager

[1006]Windows environment variable in Connection Manager is discovered

Impact

Connection Manager using Windows environment variable is discovered.

Recommendation

You can use below methods to have Windows environment variables working in SSIS Integration runtime:

[1007]SQL Server Native Client (SNAC) OLE DB driver is deprecated

Recommendation

Use latest Microsoft OLE DB Driver

[2001]Component only supported in enterprise edition

Impact

The component is only supported in Azure SSIS integration runtime enterprise edition.

Recommendation

Configure Azure SSIS integration runtime to enterprise edition.

[2002]ORC and Parquet file format aren't by default enabled

Impact

ORC and Parquet file format need JRE, which isn't by default installed in Azure SSIS integration runtime.

Recommendation

Install compatible JRE by customize setup for the Azure-SSIS integration runtime.

[2003]Third party component isn't by default enabled

Impact

Azure SSIS Integration Runtime isn't by default enabled with third party components. Third party component may fail.

Recommendation

  • Contact the third party to get an SSIS Integration runtime compatible version.

  • For in-house or open source component, customize Azure-SSIS integration runtime to install necessary SQL Server 2017 compatible components.

[2004]Azure Blob source and destination is discovered

Recommendation

Recommend to use Flexible File source or destination, which has more advanced functions than Azure Blob.

[2005]Non built-in log provider may not be installed by default

Impact

Azure SSIS integration time is provisioned with built-in log providers by default only, customize log provider may fail.

Recommendation

Customize Azure-SSIS integration runtime to install non built-in provider or driver.

[3001]Absolute or UNC path is discovered in Execute Process Task

Impact

Azure-SSIS Integration Runtime might not be able to launch your executable(s) with absolute or UNC path.

Recommendation

You can use below options for SSIS Integration runtime to launch your executable(s):

[4001]Absolute or UNC configuration path is discovered in package configuration

Impact

Package with absolute or UNC configuration path may fail in Azure SSIS Integration Runtime.

Recommendation

You can use below options for SSIS Integration runtime to access these resources:

[4002]Registry entry is discovered in package configuration

Impact

Registry entry in package configuration may fail in Azure SSIS Integration Runtime.

Recommendation

Use other package configuration types. XML configuration file is recommended.

Additional Information

Package Configurations

[4003]Package encrypted with user key isn't supported

Impact

Package encrypted with user key isn't supported in Azure SSIS Integration Runtime.

Recommendation

You can use below options:

  • Change the package protection level to "Encrypt All With Password" or "Encrypt Sensitive With Password".
  • Keep or change the package protection level to "Encrypt Sensitive With User Key", override connection manager property during package execution

Additional Information

Access Control for Sensitive Data in Packages