Solution for FTP flat file to MS SQL

I need a solution to take a file from the ftp server and process it to upload it into an MS SQL db table.  I have a rather clunky way of doing this now through several different apps and schedulers.  

What is the preferred way to handle this and the best way to automate it as much as possible?  Thanks in advance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SSIS Sql Server  Integration Services
pskeensAuthor Commented:
Thanks for the suggestion.  I can only find the download for SQL server 2005.  I am using 2008R2.  Is this the same download?   When I try to attach the DB's I get errors and failures.  I thought perhaps it was due to not being 2008r2 Compatible.
Download of the Adventurworks sample db?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jason YousefSr. BI  DeveloperCommented:
Your best option will be SSIS (Integration services)

You'll need to utilize 2 variables,

A-An OBJECT data type to hold the file names...
B-A string Variable to hold the filename, which will be used to insert into the SQL

1-put a "FTP task" to download the files to a staging location.
2-use a "For Each Loop Container"
3-put a "Execute SQL task" inside the container, to insert the files

I'm sure you'll need help, so feel free to ask me on any of the steps.

It also depends on if you know the file names or you'll have to list and pick, which FTP task doesn't allow natively, so that will be your second option which using a script task to run, pick the selected files, download and insert into SQL.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pskeensAuthor Commented:
Okay one more question.  To use SSIS I need to have BIDS correct?
Jason YousefSr. BI  DeveloperCommented:
When you install SQL server, it installs BIDS for you (Business intelligence development studio) it's a subset of Visual Studio the full product.
pskeensAuthor Commented:
Okay, I have the above requirements set up in the workspace.  Ftp connection works, this one is pretty self explanatory.  I have the other two however that I am kind of lost on.  How should these be configured exactly?  

to answer the question above, the file will not be the same every time.  Each file will have a name such as "Template" for example.  each file will also have a unique number at the trailing.  TEMPLATE_122300.csv, TEMPLATE_1223001.csv, TEMPLATE_1223002.csv, etc

Workspace Screnshot
Jason YousefSr. BI  DeveloperCommented:
Ok, that's a progress, anyway do it one task a time, remove the container and the execute sql task.

The first step is to download the files, right?

my question now, are you downloading all the files from the folder or you're selecting a specific files?

you might take a look at this free community custom component,

that will help you alot to do what you need to do...

to install it, download it and follow these steps at the end of this article
pskeensAuthor Commented:
I will be downloading all files that are put in the ftp folder and processing each file until they are complete.  There may be 1 an hour or 3 an hour.  The job should run one time every hour.
pskeensAuthor Commented:
I have the Package complete but cannot get the upload to work.  I get the FTP File over everytime but cannot get the upload to work.  This is the error I get everytime.  I have checked and checked and it is mapped correctly,.

Error: 0xC004706B at Load Data File, SSIS.Pipeline: "component "OLE DB Destination" (369)" failed validation and returned validation status "VS_ISBROKEN".
Error: 0xC004700C at Load Data File, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Load Data File: There were errors during task validation.
Warning: 0x80019002 at FileLoopingTest: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (18) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "FileLoopingTest.dtsx" finished: Failure.
pskeensAuthor Commented:
Upon further inspection it appears that the data conversion is not working.  It it trying to change data types on upload causing the errors.  I have worked and worked with this to eliminate this however unsuccesful.  I have changed DB's, tables, tried to create a new table and upload data to, all with no resolve.

Any ideas?  Thanks
Jason YousefSr. BI  DeveloperCommented:
What upload you're talking about?  upload to FTP again?
First, did the download step complete before we move on?
pskeensAuthor Commented:
Yes the ftp download completed correctly.  I should refer to append to table that did not work in the next step.
Jason YousefSr. BI  DeveloperCommented:
ok, so how is your table is setup? what's the data type for your column? are you using filestream or just varbinary columns?

you can refer to my post

it's the same concept, but in the post I'm doing extra expression to get a file name and update, you'll just INSERT instead of UPDATE.

Let me know if you need more help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.