Solved

Solution for FTP flat file to MS SQL

Posted on 2012-03-13
14
595 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:pskeens
  • 7
  • 5
  • 2
14 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37718917
SSIS Sql Server  Integration Services
http://msdn.microsoft.com/en-us/library/ms169917.aspx
0
 
LVL 2

Author Comment

by:pskeens
ID: 37719650
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.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37719921
Download of the Adventurworks sample db? http://msftdbprodsamples.codeplex.com/releases/view/55926
0
 
LVL 21

Accepted Solution

by:
huslayer earned 500 total points
ID: 37720003
Hi,
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.


Regards,
Jason
0
 
LVL 2

Author Comment

by:pskeens
ID: 37720730
Okay one more question.  To use SSIS I need to have BIDS correct?
0
 
LVL 21

Expert Comment

by:huslayer
ID: 37721027
Yes,
When you install SQL server, it installs BIDS for you (Business intelligence development studio) it's a subset of Visual Studio the full product.

http://msdn.microsoft.com/en-us/library/ms173767.aspx
0
 
LVL 2

Author Comment

by:pskeens
ID: 37723291
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 21

Expert Comment

by:huslayer
ID: 37723302
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, http://microsoft-ssis.blogspot.com/2012/01/custom-ssis-component-foreach-ftp-file.html

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
http://microsoft-ssis.blogspot.com/2012/01/custom-ssis-component-file-in-use-task.html
0
 
LVL 2

Author Comment

by:pskeens
ID: 37723308
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.
0
 
LVL 2

Author Comment

by:pskeens
ID: 37723573
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.
0
 
LVL 2

Author Comment

by:pskeens
ID: 37726697
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
0
 
LVL 21

Expert Comment

by:huslayer
ID: 37726868
Hi,
What upload you're talking about?  upload to FTP again?
First, did the download step complete before we move on?
0
 
LVL 2

Author Comment

by:pskeens
ID: 37727282
Yes the ftp download completed correctly.  I should refer to append to table that did not work in the next step.
0
 
LVL 21

Expert Comment

by:huslayer
ID: 37728935
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 http://asqlb.blogspot.com/2012/02/ssis-iterate-to-update-blob-column.html

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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now