SSIS - 将多个 SQL Server 表导出到多个文本文件 [英] SSIS - Export multiple SQL Server tables to multiple text files

问题描述

我必须在两个 SQL Server 数据库之间移动数据.我的任务是将数据导出为文本 (.dat) 文件，移动文件并导入到目的地.我必须迁移 200 多个表.

I have to move data between two SQL Server DBs. My task is to export the data as text (.dat) files, move the files and import into the destination. I have to migrate over 200 tables.

这是我试过的

1) 我使用执行 SQL 任务来获取我的表.2) 使用 For each 循环遍历集合中的表名.3) 在 for each 循环中使用脚本任务来构建文本文件目标路径.4) 使用源 ole db 的变量中的表名和目标平面文件的变量中的路径名调用 DFT.

1) I used a Execute SQL task to fetch my tables. 2) Used a For each loop to loop through the table names from the collection. 3) Used a script task inside the for each loop to build the text file destination path. 4) Called a DFT with the table name in a variable for the source ole db and the path name in a variable for the destination flat file.

第一个表提取正常，但第二个表因同步错误而爆炸.我看到这是很多帖子，但找不到与我的场景相符的帖子.因此在这里发帖.

First table extracts fine but the second table bombs with a synchronization error. I see this is numerous posts but could not find one that matches my scenario. Hence posting here.

即使我让包与多个 DFT 一起工作，第二个 DFT 中的第二个表也不会导出列，因为平面文件连接管理器仍然记得第一个表列.有没有办法让它忘记列?

Even if I get the package to work with multiple DFTs, the second table from the second DFT does not export columns because the flat file connection manager still remembers the first table columns. Is there a way to get it to forget the columns?

关于如何使用动态源和目标变量使用一个 DFT 将多个表导出到多个文本文件的任何想法?

Any thoughts on how I can export multiple tables to multiple text files using one DFT using dynamic source and destination variable?

感谢并感谢您的帮助.

推荐答案

我想出了一个办法.如果有人遇到同样的情况，我想我会分享.

I figured out a way to do this. I thought I will share if anybody is stuck in the same situation.

所以，总而言之，我需要通过文件导出和导入数据.出于各种原因，我也想尽可能使用格式文件.

So, in summary, I needed to export and import data via files. I also wanted to use a format file if at all possible for various reasons.

我所做的是

1) 构建一个 DFT，它从我需要导出的数据库中获取一个表名列表.我使用oledb"作为源，使用记录集目标"作为目标，并将表名存储在对象变量中.

1) Construct a DFT which gets me a list of table names from the DB that I need to export. I used 'oledb' as a source and 'recordset destination' as target and stored the table names inside a object variable.

DFT 并不是真正必要的.你可以用任何其他方式做到这一点.此外，在我们的应用程序中，我们将表名存储在一个表中.

A DFT is not really necessary. You can do it any other way. Also, in our application, we store the table names in a table.

2) 添加一个带有For Each ADO Enumerator"的For each loop container"，它将我上一步中的对象变量带入集合中.

2) Add a 'For each loop container' with a 'For Each ADO Enumerator' which takes my object variable from the previous step into the collection.

3) 一一解析变量并在脚本任务中构建如下所示的 BCP 语句.根据需要创建变量.BCP 语句将存储在一个变量中.

3) Parse the variable one by one and construct BCP statements like below inside a Script task. Create variables as necessary. The BCP statement will be stored in a variable.

我遍历这些表并像这样构造多个 BCP 语句.

I loop through the tables and construct multiple BCP statements like this.

BCP "DBNAME.DBO.TABLENAME1" 输出 "PATH\FILENAME2.dat" -S SERVERNAME -T -t"|" -r$\n -f "PATH\filename.fmt"

BCP "DBNAME.DBO.TABLENAME1" out "PATH\FILENAME2.dat" -S SERVERNAME -T -t"|" -r$\n -f "PATH\filename.fmt"

BCP "DBNAME.DBO.TABLENAME1" 输出 "PATH\FILENAME2.dat" -S SERVERNAME -T -t"|" -r$\n -f "PATH\filename.fmt"

BCP "DBNAME.DBO.TABLENAME1" out "PATH\FILENAME2.dat" -S SERVERNAME -T -t"|" -r$\n -f "PATH\filename.fmt"

这些语句放在一个 .bat 文件中.这也是在脚本任务内部完成的.

The statements are put inside a .bat file. This is also done inside the script task.

4) 执行进程任务接下来将执行 .BAT 文件.我必须这样做，因为我没有选择在我的公司中使用master..xp_cmdShell"命令或BULK INSERT"命令.如果我可以选择执行 cmdshell，我可以直接从包中运行命令.

4) A execute process task will next execute the .BAT file. I had to do this because, I do not have the option to use the 'master..xp_cmdShell' command or the 'BULK INSERT' command in my company. If I had the option to execute cmdshell, I could have directly run the command from the package.

5) 再次添加一个带有For Each ADO Enumerator"的For each loop container"，它将我上一步中的对象变量带入集合中.

5) Again add a 'For each loop container' with a 'For Each ADO Enumerator' which takes my object variable from the previous step into the collection.

6) 一一解析变量并在脚本任务中构造这样的 BCP 语句.根据需要创建变量.BCP 语句将存储在一个变量中.

6) Parse the variable one by one and construct BCP statements like this inside a Script task. Create variables as necessary. The BCP statement will be stored in a variable.

我遍历这些表并像这样构造多个 BCP 语句.

I loop through the tables and construct multiple BCP statements like this.

BCP "DBNAME.DBO.TABLENAME1" in "PATH\FILENAME2.dat" -S SERVERNAME -T -t"|" -r$\n -b10000 -f "PATH\filename.fmt"

BCP "DBNAME.DBO.TABLENAME1" in "PATH\FILENAME2.dat" -S SERVERNAME -T -t"|" -r$\n -b10000 -f "PATH\filename.fmt"

BCP "DBNAME.DBO.TABLENAME1" in "PATH\FILENAME2.dat" -S SERVERNAME -T -t"|" -r$\n -b10000 -f "PATH\filename.fmt"

BCP "DBNAME.DBO.TABLENAME1" in "PATH\FILENAME2.dat" -S SERVERNAME -T -t"|" -r$\n -b10000 -f "PATH\filename.fmt"

这些语句放在一个 .bat 文件中.这也是在脚本任务内部完成的.

The statements are put inside a .bat file. This is also done inside the script task.

-b10000 已放置，以便我可以批量导入.如果没有这个，由于 tempdb 中的空间较少，我的许多大表都无法复制.

The -b10000 was put so I can import in batches. Without this many of my large tables could not be copied due to less space in the tempdb.

7) 运行 .bat 文件以再次导入文件.

7) Run the .bat file to import the file again.

我不确定这是否是最佳解决方案.我仍然认为我会分享满足我要求的内容.如果我的回答不清楚，如果您有任何问题，我很乐意解释.我们也可以优化这个解决方案.完全可以通过 VB 脚本完成相同的操作，但您必须编写一些代码才能做到这一点.

I am not sure if this is the best solution. I still thought I will share what satisfied my requirement. If my answer is not clear, I would be happy to explain if you have any questions. We can also optimize this solution. The same can be done purely via VB Scripts but you have to write some code to do that.

我还创建了一个包配置文件，我可以在其中动态更改数据库名称、服务器名称、数据和格式文件位置.

I also created a package configuration file where I can change the DB name, server name, the data and format file locations dynamically.

谢谢.

这篇关于SSIS - 将多个 SQL Server 表导出到多个文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！