There are multiple csv files in a folder named parent with data in it and those files have the same format. I need to extract data in the csv file and insert them into a sql server table named log.once data are inserted, the csv files should move into a folder named archive folder. if there is any file in the parent folder where the extension is not csv, that file should move to another folder named error folder.
Table structure of sql server is as follows:
name varchar (50),
email varchar(100),
address varchar(50)
Table structure in all the csv files are as follows:
name| email| address
What I have tried:
First of all I created a folder called files and inside that I created 3 folders as parent where all the csv files and files with other extensions are included, archive folder and the error folder. In the visual studio ssdt, I used a
foreach loop container in the
control flow and added a data flow task inside the foreach loop container.Created a string variable named filename without assigning any value to it.
I edited the foreach loop container collection with these:
folder path as E:\files\parent
Files as *.*
Enumerator : Foreach File enumerator
and variable mapping in the editing window,I used the created variable "FileName" as the variable with 0 index.
in the data flow I used flat file source and a OLE DB destination. In order to map details I used OLEDB connection manager and mapped the log table to it and in the flat file connection manager I selected one file from the parent folder since all the csv files has the same format.
In the properties of flat file connection manager, I gave a connection string in the
expressions property and set it to the created variable "FileName".
It is as follows : Propert :Connection String , Expression :@[User::FileName].
By executing this solution data is loaded from flat file sources to the log table.
Submit an article or tip