Migrating ~100 SSIS Packages to Azure: Acceleration Strategies & ADF vs. Fabric Guidance
Hi everyone, Our team is currently planning to migrate approximately 100 SSIS packages (including several large and highly complex workflows) to Azure. We are evaluating two primary paths: a lift-and-shift using Azure Data Factory (ADF) SSIS Integration Runtime (SSIS-IR) vs. a full rewrite. Current Architecture / Tech Stack: Azure, VMs, Azure SQL Managed Instance (SQL MI) We would appreciate insights on the following: Accelerated Rewrite Options: If we decide to rewrite, are there automation tools, frameworks, or best practices to expedite the transition rather than manually rebuilding every activity in ADF? ADF vs. Microsoft Fabric: Given Microsoft’s strategic push toward Fabric, is ADF still the ideal migration target, or should we consider targeting Fabric Data Pipelines directly? Thank you in advance for any inputs, advice, or shared experiences!
How to Send-As a Distribution Group (via SSIS ScriptTask) via Exchange Online using Modern Auth?
We have a SQL Server Integration Services (SSIS) task that retrieves emails from a table in our Customer Relationship Management (CRM) system. The emails contain From:, To:, Cc, and Bcc: addresses, in addition to the body and other data. The SSIS task then reads the data, connects to a local on-premises Exchange 2016 server to send the emails, and allows the SQL Server IP address to log in anonymously and send emails as any user in the From field, provided the address is valid on the Exchange server. We are considering removing the local on-premises Exchange 2016 server and need to modify the SSIS task to connect to Exchange Online to send the emails. We are exploring the use of Modern Authentication, App Registration, and Microsoft Graph to send the emails. The From addresses in the CRM can be an address of a User Mailbox, Shared Mailbox, or Distribution Group. Based on my research, it appears there is no way to send emails as a Distribution Group via Microsoft Graph. Therefore, I am forced to use SmtpClient and logging in as a user with Send-As permissions on all of the groups, which has its own drawbacks. What is the proper way to programmatically send as a Distribution Group? We have about 40 Groups we send as.
Setting Up SCD Type 2 using "Variable to set date values"
I am creating an SCD type 2 dimension and using start and end dates to identify current and expired records. In my destination table, there are start date and end date columns. I am unable to select the variable to set date values in the SSIS package. When I click in the "Variable to set date values" box, I don't see a drop-down to select my system variables from. I want to use the System::ContainerStartTime. Do you think this is something I need to configure in the SSIS package? If so, how?
CSV file size from "Save Results As..." versus exported via SSIS Package / Export Wizard
When exporting query results from the grid using "Save Results As...", I'm consistently creating CSV files that are half the size of CSV files created by exporting via the Import/Export Wizard (and the resulting SSIS Package). Exact same query used for both, same results, same number of records etc. I believe this has to do with the file encoding used by each export process. I know this is not a precise science, but based on how Notepad interprets the resulting files, the encoding of the "Save Results As..." files is "UTF-8 with BOM", while the SSIS Package generates a "UTF-16 LE" file. I've tried a variety of approaches to try to get the Import / Export Wizard to mimic the "Save Results As" encoding without success. Changing the Code Page field on the "Choose a Destination" screen to "65001 (UTF-8)" results in the dreaded "DT_NTEXT not supported with ANSI files" error upon export due to nvarchar(max) data types in the source table. That can be resolved by checking the Unicode box on the "Choose a Destination" page, but checking Unicode also disables the Code Page dropdown. Even if you select "65001 (UTF-8)" in Code Page and then check Unicode, it still produces a UTF-16 encoded file. Two questions at this point: 1) How does the "Save Results As..." function in the query grid avoid the DT_NTEXT error and produce a UTF-8 encoded file? 2) If I tried to edit the package in SSIS Designer, would I have more control over the encoding and be able to mimic the "Save Results As..." file sizes? This would take a bit of effort (installing Visual Studio data tools, learning how to use it, etc) so if that's a dead end I'd rather not pursue it.
Trying to connect to ADO via oData in SSIS - getting 401 Unauthorized
I'm trying to pull some data from ADO online using the oData connector in SSIS (Visual Studio 2019 and SSDT - all on latest versions). Using this URL (redacted our site info), and it works without issue in the browser: https://analytics.dev.azure.com/*****/***/_odata/v4.0-preview/WorkItems But when I add an OData Source into an SSIS Data Flow no matter which authentication method I use I get 401 Unauthorized. Can someone give any insight on what I may be missing? Thanks.
Azure maps geocoding and SSIS
Can someone offer pointers on integrating Azure maps geocoding capabilities in SSIS? Is their a more streamlined way of leveraging this than creating a C# script component within SSIS? I've not used Data Factory before - but wondering if that would be an elegant way of handling this. End goal is attaching lat/longitude values to address information in SQL Server database. TIA
Data Migration from Sql DataBase to Azure Data Lake Using SSIS
Hi, how are you? I want to migrate data from SQL DataBase (on-premises) to Azure Data Lake (Cloud) Using SSIS(SQL Server Integration Services) how I can do this and please also guide me I have massive data so which one will best practice to migrate data from SQL DataBase to Azure Data Lake Using SSIS.SSIS DevOps Tools new version tasks in preview
SSIS DevOps Tools new enhancements: SSIS Build task version 1.* (Preview) Remove the dependency on Visual Studio and SSIS designer. Build task can run on Microsoft-hosted agent or self-hosted agent with Windows OS and .NET framework 4.6.2 or higher. No need of installing out-of-box components. Support protection level EncryptionWithPassword and EncryptionAllWithPassword. SSIS Deploy task version 1.* (Preview) Support protection level EncryptionWithPassword and EncryptionAllWithPassword.
