Solved

Solution for FTP flat file to MS SQL

Posted on 2012-03-13
14
603 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
[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
  • 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 21

Accepted Solution

by:
Jason Yousef, MS 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:Jason Yousef, MS
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
 
LVL 21

Expert Comment

by:Jason Yousef, MS
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:Jason Yousef, MS
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:Jason Yousef, MS
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

756 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