Transform Multiple .txt files to respective sql tables (SSIS)

0
votes

As the question suggests. I want to transform daily dumps of data to a database with all the .txt files to be converted to tables. What way can I accomplish that via ssis packages ?

Step 1. Identify the current folder ( Folder name structure - Name_mmddyyyy ) Step 2. Convert all the .txt files in that folder to tables in a database (eg : a.txt,b.txt,c.txt converted to a,b,c sql tables )

I want to automate this ssis package to run every day at a certain time . ( I know how to schedule a task ) . The ssis package implementation part is a bit sketchy.

Thanks in advance for the help. Any links to tutorials or articles appreciated.

EDIT : I realize this is a broad question but with the research I've done so far ,I could only find for-each loop from folder transforms and loads all data to a single table. I want all separate txt files to make a separate table according to the text file name. That's where I can't seem to find anything.

1
sql-serverssisfile-conversion
What have you tried so far? This is a very broad question as it is and there are multiple ways of achieving it. - rvphx
Do those txt files have separators? Like a csv? Or just random strings? Do they all have the same structure? Assuming all the dumps have the same structure (i.e. 5 columns) you could use a foreach loop from SSIS and point to your folder. Then the next step would be to use a file from the folder as reference and to map the connections to a table on your server which has the same structure as the txt file. Just lookup "SSIS foreach loop from a folder", you will find what you are looking for. - CM2K
Research SSIS connections and SSIS data flow tasks. Build the target tables in SQL and then use SSIS to populate them. - David Rushton

1 Answers

0
votes

Look into using BiML Script, which uses meta data to build packages dynamically.