Solution for FTP flat file to MS SQL

SSIS Sql Server  Integration Services
http://msdn.microsoft.com/en-us/library/ms169917.aspx

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? http://msftdbprodsamples.codeplex.com/releases/view/55926

Okay one more question.  To use SSIS I need to have BIDS correct?

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

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

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

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.

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.

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

Hi,
What upload you're talking about?  upload to FTP again?
First, did the download step complete before we move on?

Yes the ftp download completed correctly.  I should refer to append to table that did not work in the next step.

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.