Click here to Skip to main content
15,881,967 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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..
Posted
Updated 19-Apr-16 11:42am
v2

1 solution

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
SQL
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

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
SQL
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
 
Share this answer
 
Comments
Member 11337367 20-Apr-16 3:41am    
Thanks for answering my question but i don't want to do in sql everything by ssis..Is it possible to do that?
CHill60 20-Apr-16 3:47am    
It's been some time since I used SSIS but I thought you could add sql steps into the flow, possibly as a stored procedure. I'll have another look at it
CHill60 20-Apr-16 4:20am    
I've checked - put an Execute SQL Task after your Foreach Loop Container to do the tidying up.
I found an example: Loop through Flat Files in SQL Server Integration Services[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900