如何使用ssis将具有不同列的多个文本文件导入数据库？ [英] How to import multiple textfile with different column into database using ssis?

问题描述

您好，



在此路径文件夹中D：\ megh我有4个平面文件



第一个平面文件有ename列

第二个平面文件有ename，sal

第三个平面文件ha ename，sal，job

第四个平面文件有ename，sal，job，dept



我想将这些文件数据导入sql table tbl_emp（目标表）



它有列名ename，sal，job，dept





所以请帮助我如何做到这一切将数据存入一个表格





任何答案都将不胜感激



我尝试了什么：



现在我已用于数据流任务中的每个循环容器

在数据流平面文件和ole-db destination.It工作，如果所有文件具有相同的列..

Hello,

In this path folder D:\megh i have 4 flat file

First flat file has ename column
second flat file has ename,sal
third flat file ha ename,sal,job
fourth flat file has ename,sal,job,dept

I want to import these file data to sql table tbl_emp (destination table)

It is having column names ename,sal,job,dept


So kindly help me how to do that all files data into one table


Any answer would be greatly appreciated

What I have tried:

Now i have used for each loop container inside that data flow task
in data flow flat file and ole-db destination.It works if all files having same columns..

推荐答案

如果相同的员工出现在所有文件中然后忽略文件1 ，2,3，只是获取数据m文件4.



但假设每个文件中存在不同的员工，那么您可以单独加载所有文件，然后添加一个步骤将它们组合到单个表中像这样

If the same employees appear in all files then ignore files 1,2,3 and just get the data from file 4.

But assuming that different employees exist in each file then you could load up all of the files individually then add a step to combine them into the single table like this

select ename, sal, job, dept 
into tbl_emp
from file4 
union
select ename, sal, job, null from File3
union
select ename, sal, null, null from File2
union
select ename, null, null, null from File1

添加最后一步以摆脱你用于每个文件的临时表。



注意我开始使用包含其中所有列的文件（文件4）而不是文件1 - 这意味着当我最初创建表时，我没有必要为空列定义列类型。

如果表 tbl_emp 已经存在然后订单是什么并且你需要像这样移动插件

Add a final step to get rid of the "temporary" tables that you used for each file.

Note I started with the file that had all of the columns in it (File 4) rather than with File 1 - this means I didn't have to define a column type for the null columns when I was initially creating the table.
If the table tbl_emp already exists then it doesn't matter what the order is and you need to move the insert like this

insert into tbl_emp
select ename, sal, job, dept 
from file4 
union
select ename, sal, job, null from File3
union
select ename, sal, null, null from File2
union
select ename, null, null, null from File1

这篇关于如何使用ssis将具有不同列的多个文本文件导入数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！