how to load specific column with multiple files in ssis
-
Hi I have one doubt in ssis
how to load only id and name columns with multipul files into sql server using ssis package.
here each files different structure but required columns are available in all files
and columns order are different between each files.
Source Files Folder is : D:\SourceFolder
Files are like below :
File1 Name : Emp_20190102
Emp_20190102 File data :
id,sal,deptno,name
1,100,10,h
File2 Name : Emp_20190102_1
Emp_20190102_1 File data :
id,name,sal
11,ac,101
File3 Name : Emp_20190102_2
Emp_20190102_2 FIle Data
id,sal,name,deptno,loc
3,200,y,30,che
File4 Name : Emp20190102
Emp20190102 FIle Data:
id,sal,deptno,dname,flag,name
10,400,40,hr,1,un
Here I want load only id and name column information in the sql server table
and sql table structure :
CREATE TABLE [dbo].[Emp](
[id] [int] NULL,
[name] [varchar](50) NULL
)
based on above 4 files I want load data in emp table
id |Name
1 |h
11 |ac
3 |y
10 |un
I tried like below
tep1 : created 2 variable for foldername and filename
filelocation : D:\SourceFolder
FileName : Emp_20190102_1.txt
then drag and drop foreachloop container and select typeofenumerator : foreach fileenumerator
and variable mapping : filename variable
and inside drag and drop flat file source and configure source file
after that crated dynamic connection for flatfile connection
then drag and drop oledb destination and congigure it
after execute the package resutl getting incorrect
here we should use only one dataflow task to load all files
can you please tell me how to implete package to achive this task in ssis
-
I'm really struggling to read what your asking here, as there is code not inside code markup in the middle of statements; making a somewhat unreadable mess I'm afraid.
You say you have a doubt about SSIS; what specifically do you think it is SSIS can't do here? I see you said something about that the definition of the file changes? If so, yes that'll be a problem. SSIS needs your files to be well defined. If the definition of your data keeps changing, SSIS won't be able to read it reliably; at least not with basic Source component.
If it's just the order of the columns (and the names are consistent), you'll likely need to use a Script Component to find the order of the columns and then appropriately read those values into the correct column in the buffer.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply