?
Solved

SSIS import multiple CSVs into associated tables

Posted on 2016-11-22
3
Medium Priority
?
104 Views
Last Modified: 2016-11-23
Hi

We currently have 3 files that are uploaded through SFTP to us on a daily bases.  Filename examples:

 - Invoices_20161122.csv
 - Agencies_20161122.csv
-  Sales_20161122.csv

The file names will change the date stamp every day when they are uploaded to the SFTP site

The files need to be uploaded in their associated tables

- Invoices
- Agencies
- Sales

The tables would need to be dropped and recreated when the files are imported.  The first row in the files contains the column names.

How can I create an SSIS package to:

- Drop the tables
- Recreate the tables when importing the CSV file and name them appropriately
- Move the old files an archive folder


Thank you for all of your help
0
Comment
Question by:thomasm1948
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41898350
Have you tried anything? With which part you need help ?
0
 
LVL 13

Accepted Solution

by:
Arifhusen Ansari earned 2000 total points
ID: 41898541
Hi,

Based on your explanation. I can give you below solution.
You can create a single package to achieve this. But rather i would suggest, to create three packages. So that it would be easy to maintain.

1) Rather than drop a table and recreate the same. you can truncate that table.

For this you can use Execute script task and in that write a script to truncate table.

2) If you only have one file per type per day. You can use Data flow task directly to load the file in the table.

  e.g Invoices_20161122.csv will be only one for that day.

3) You have to use Flat File Connection as your file is of type csv.
4) when you create the package initially use the static connection to csv file. Develop the package if it's loaded perfectly.
5) You need to create the dynamic connection so that from next time it will automatically pick a file and load it.

For this you have to declare 3 variable.
1) FolderPath: path for your folder.
e.g. "E:\\FlatFileLoadOrCSV\\
2) FileName: this will be generated based on expression as we have to dynamically pick a file.
e.g. Expression would be like:  "Invoices_"+(DT_WSTR,4)  year(getdate())+(DT_WSTR,2)  month(getdate())+(DT_WSTR,2)  day(getdate())+".csv"

Evaluated value: Invoices_20161123.csv

3) FilePath: This is exact file path from where package will pick a file.
e.g. Expression is @[User::FolderPath]+@[User::FileName]

Evaluated value: E:\FlatFileLoadOrCSV\Invoices_20161123.csv

4) After this you just need to configure you connection manager to pick a file dynamically.

Click on File Connection go to property. Go to expression option click on ellipsis button and set the connection string property.

Refer below screenshot.

2016-11-23_10-30-05.png

Hope it will help you.

I have attached the ssis package as well. I have changed the extension to .txt. Download the file and just change it to .dtsx.

Hope it will help you.
FlatFileLoadOrCSV.txt
0
 

Author Closing Comment

by:thomasm1948
ID: 41899069
Thank this works out perfectly
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question