基于SSIS栈，如何定时从XMLA Endpoint抽取数据至本地SQL Server数据仓库？

方案1：基于SSIS + ADOMD.NET连接XMLA端点 #

既然你能通过VS/SSAS项目访问XMLA端点，可按以下步骤整合进现有SSIS技术栈：

• 在VS的SSAS项目中连接Power BI XMLA端点，编写MDX或DAX查询将多维数据集转换为二维结果集（比如用NON EMPTY、FLATTENED关键字处理结构），测试确保能返回所需数据。
• 在SSIS包中添加「执行SQL任务」，配置ADOMD.NET连接管理器（选择Microsoft.AnalysisServices.AdomdClient驱动），指向Power BI XMLA端点。
• 将测试通过的MDX/DAX查询填入执行SQL任务，设置结果集为「完整结果集」，通过「数据转换任务」或「OLE DB目标」将数据写入本地SQL Server 2016数据仓库。
• 用SQL Server Agent创建作业，定时执行该SSIS包，实现自动化同步。

方案2：SSMS XMLA脚本 + SQL Server Agent定时执行 #

利用SSMS 18+可访问的特性，封装成定时作业：

• 在SSMS中连接Power BI XMLA端点，生成用于抽取数据的XMLA脚本（比如使用<Discover>命令执行元数据查询，或<Execute>命令运行MDX查询），将查询结果输出为XML或表格格式。
• 使用SSAS自带的Invoke-ASCmd命令行工具执行该XMLA脚本，将输出结果转换为可导入SQL Server的格式（比如用PowerShell处理XML，或直接导出为CSV）。
• 在SQL Server Agent中创建作业，添加「PowerShell」或「操作系统命令」步骤，调用Invoke-ASCmd脚本并完成数据导入逻辑，设置定时触发器实现自动执行。

方案3：Azure Data Factory（ADF）跨环境同步 #

针对你的场景，ADF完全适用，可作为SSIS的替代或补充方案：

• 在ADF中配置自托管集成运行时，确保能访问本地SQL Server 2016数据仓库。
• 添加数据源：选择「Power BI数据集」作为源，配置XMLA端点连接信息；选择「SQL Server」作为目标，指向本地数据仓库。
• 创建复制活动或数据流动，映射源数据集与目标表的字段，设置数据转换规则（ADF支持基础转换，复杂逻辑可结合SQL脚本）。
• 配置时间触发器，实现定时同步。此方案无需依赖本地VS/SSDT环境，适合跨云-本地的数据同步场景。

关键注意事项 #

• 权限配置：确保执行任务的服务账号（SQL Server Agent账号、ADF集成运行时账号）同时拥有Power BI XMLA端点的读取权限，以及本地SQL Server的写入权限。
• 性能优化：针对大数据集，采用分批抽取策略（比如按日期范围拆分MDX查询），避免一次性拉取导致的性能问题。
• 错误处理：在SSIS包或ADF活动中添加日志记录、重试机制，确保同步失败时可追溯问题并自动恢复。

内容的提问来源于stack exchange，提问作者AhmedHuq