SSIS Repointing

Applies to

• SQL Server

• Azure Synapse Analytics

Description

SnowConvert's SSIS Repointing process updates SQL Server Integration Services (SSIS) packages to work with Snowflake. The process examines SSIS package files (.dtsx) and changes their connection settings to use Snowflake instead of SQL Server.

When repointing begins, the tool reads through each SSIS package (.dtsx) and connection manager (.conmgr) file to find all SQL Server connections. It then modifies these connections and updates any components that reference them to work with Snowflake. The main work happens when SnowConvert updates the connection managers. It changes them to use Snowflake's ODBC driver and rewrites the connection strings with the right Snowflake settings.

The tool keeps the original names of connections to help developers understand the changes. Teams can customize how the repointing works. They can specify which connections should map to which Snowflake databases and provide their Snowflake account information. This lets organizations handle different types of SSIS packages and various database setups. As it works, SnowConvert keeps track of what it's doing and creates reports showing what changed. It logs any errors or warnings it finds, which helps teams fix problems quickly. This automated approach saves time compared to manually updating SSIS packages.

Organizations can keep using their existing SSIS workflows while moving to Snowflake. The process balances making necessary technical changes while keeping packages working as expected.

SSIS Repointing currently supports only OLE DB and ODBC connection managers to SQL Server databases.

How to use it

Step 1: Provide SSIS Project Path

Locate your SSIS projects folder and provide the path to the SSIS projects in the application. The tool will scan for .dtsx package files and .conmgr files within the specified directory structure.

Step 2: Configure Target Snowflake Account Information

In the next screen, add the general information for your target Snowflake account:

• Snowflake Account: Your Snowflake account URL (e.g., mycompany.snowflakecomputing.com)

• Warehouse: The Snowflake warehouse to be used for computations

• Schema: The default schema for your migrated objects

• Username: Your Snowflake username for authentication

• Role: The Snowflake role to be used for accessing resources

Step 3: Set Up Individual Connection Managers

Review the connection manager names from your SSIS packages and customize the Snowflake connection values as desired:

Customize your connection manager

Review your repointing settings

Step 4: Review Output

Find the repointed SSIS packages in the repointing_output folder:

• Modified Packages: Updated .dtsx files with Snowflake connection configurations

• Connection Manager Files: Updated .conmgr files with Snowflake-specific parameters

Step 5: Connection Manager Transformation Example

Here's how a connection manager looks before and after repointing:

Before repointing

After repointing

Notice how the OLEDB connection manager is transformed from SQL Server to Snowflake ODBC-specific parameters while maintaining the same connection manager and its references.

All repointed connections require you to input your Snowflake account password.

Next Steps

1. Review the ETLAndBiRepointing.csv file to check the status of your SSIS repointing

2. Review the SSIS queries (Execute SQL Task or Source component Sql Commands) for correctness. If there are errors, warnings or issues, make sure to review the Assessment reports to learn more about them and possible fixes

3. Update the password for each connection manager in your repointed packages

4. Open the repointed packages in Visual Studio and refresh components to update metadata

5. Install the latest Snowflake ODBC driver on any machines that will run the repointed packages

6. Some components may need to be opened and saved again after repointing to work correctly, including connection managers

7. Deploy the repointed packages to your SSIS environment

8. Ensure your Snowflake account has the necessary permissions and objects on the target databases, schemas and warehouses

9. Test the packages in a development environment before production deployment

10. Update any hardcoded connection strings in custom scripts or expressions within the packages to use the correct Snowflake connection string format

Troubleshooting

When repointing SSIS packages with SnowConvert, here are some common issues and solutions:

• If connections fail after repointing, verify that the Snowflake connection parameters (account, username, warehouse, etc.) are correctly specified in the connection mappings. Missing or incorrect connection details will prevent packages from executing successfully.

• Some SQL Server-specific components and tasks may not have direct equivalents in Snowflake. These will be flagged during repointing and may require manual updates or architectural changes to work with Snowflake.

• If packages fail with permission errors, verify that the Snowflake user accounts have the necessary privileges on the target databases, schemas and warehouses. The access rights model differs between SQL Server and Snowflake.

• For packages using variables or expressions in connection strings, ensure these are updated to use the correct Snowflake connection string format and parameters after repointing.

• If you encounter issues with specific packages, check the repointing logs and reports generated by SnowConvert. These contain detailed information about changes made and potential problems that need attention.

• The first time you open the repointed package, you may see a warning about the package being corrupted. This is normal and can be ignored.