根据表和目标中的每个SQL语句导出平面文件 [英] Export Flat File based on the each SQL statement in the table and destination

问题描述

我还是SSIS的新手，

现在，我可以根据此

您可以检查我的

解决方案

重要说明

从安全角度考虑，不建议在数据库中存储SQL命令和文件位置，并在动态数据导入过程中使用它们.

TLDR

您必须添加一个数据流任务，该任务包含配置为从SSIS变量读取的OLE DB源和平面文件目标.

详细信息

1. 首先，请确保您使用了for-each循环容器中的变量映射来检索有关源(SQL命令)和目标(平面文件路径和配置)的所有信息.

• 使用ForEach遍历结果集循环

2. 接下来，您应该添加一个平面文件连接管理器(用于目标)并配置连接字符串以从 [文件位置] 和 [文件名] 中读取变量.

• SSIS:动态文件名平面文件目标
• 动态平面SQL Server Integration Services中的文件连接

3. 第三步是在Foreach循环容器中添加数据流任务，您应该在其中添加OLE DB源和平面文件目标.

4. 设置OLE DB源数据访问模式"；到 SQL Command from variable 中，然后选择已映射到 SQL Command 列的变量.

5. 配置平面文件目标以使用之前创建的平面文件连接管理器.

---

旁注

检查以下详细文章.它解释了一个非常相似的情况:

• 实施ForeachSSIS中的循环逻辑

I'm still new in SSIS,

Now I can read the SQLStatement, FileName, and FileLocation based on this video

But I'm facing the problems when export the flat file base on the File Location in SQL Table.

You can check my sample package. Besides, the following screenshot shows the SQL table structure where information is stored:

Different FileName, Different File Location and different SQL Command

解决方案

Important Note

Storing SQL Commands and file locations within databases and using them within a dynamic data import process is not recommended from a security perspective.

TLDR

You must add a Data Flow Task that contains an OLE DB Source and Flat File Destination configured to read from the SSIS variables.

Details

1. First, make sure that you used the variables mapping in the for-each loop container to retrieve all information about the source (SQL command) and destination (Flat file path and configuration).

• Looping Through a ResultSet with the ForEach Loop

2. Next, you should add a Flat File connection manager (for the destination) and configure the connection string to read from the [File Location] and [File Name] variables.

• SSIS: Dynamic File Name for Flat File Destination
• Dynamic Flat File Connections in SQL Server Integration Services

3. Third step is to add a Data Flow Task within the Foreach loop container, where you should add an OLE DB Source and a Flat File Destination.

4. Set the OLE DB Source "Data Access Mode" to SQL Command from variable and select the variable you have mapped to the SQL Command column.

5. Configure the Flat File Destination to use the Flat File Connection Manager created previously.

---

Side Note

Check the following detailed article. It explains a very similar case:

• Implementing Foreach Looping Logic in SSIS

这篇关于根据表和目标中的每个SQL语句导出平面文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！