Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

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.
0
pskeens
Asked:
pskeens
  • 7
  • 5
  • 2
1 Solution
 
jogosCommented:
SSIS Sql Server  Integration Services
http://msdn.microsoft.com/en-us/library/ms169917.aspx
0
 
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.
0
 
jogosCommented:
Download of the Adventurworks sample db? http://msftdbprodsamples.codeplex.com/releases/view/55926
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
pskeensAuthor Commented:
Okay one more question.  To use SSIS I need to have BIDS correct?
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
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
0
 
Jason Yousef, MSSr. 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, 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
 
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.
0
 
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.
0
 
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
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,
What upload you're talking about?  upload to FTP again?
First, did the download step complete before we move on?
0
 
pskeensAuthor Commented:
Yes the ftp download completed correctly.  I should refer to append to table that did not work in the next step.
0
 
Jason Yousef, MSSr. 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 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now