June 17, 2008 at 12:21 pm
Hi,
I am trying to create DTS package (Windows 2003, SQL 2005 Ent) to FTP file and upload onto database. Do I need Windows FTP service component on my database server? Or just FTP file server (where file locates)?
Many thanks.
Chris
June 25, 2008 at 10:07 pm
I'm wondering the same thing. There seems to be precious little written on the subject as far as how to accomplish it!
June 25, 2008 at 10:28 pm
Hi Sharon,
There is no need to install FTP service on SQL box. The reason I could not FTP and copy file was that I had extra folder at end FTP site in DTS FTP task. I had something like:
xxxxx.xxx.com/pub
It started to work after removing /pub
Chris
June 25, 2008 at 10:35 pm
Thanks for replying, Chris.
My problem is slightly different, but I'm making progress.
I'm just gobsmacked by the lack of info I can find on FTP-ing in regard to SQL Server in general. I haven't done that before from a SQL Server, and didn't even know where to start, so had to just mess with things until I figured it out. Very hit and miss.
June 25, 2008 at 10:43 pm
The one I had was first set SQL folder for FTP file, FTP task, upload file into staging table, SQL task to execute procedure (copy staging to permanent table) and finally move the file to archive folder. If DTS component screen shot would help you start then I could send you one.
June 25, 2008 at 11:34 pm
Thanks for the offer. I think I'm OK now.
When I FTP files from a remote site, I need to grab them according to date, which is part of the filename on the remote server.
I know there was an article on here somewhere on how to set directory names by date, but I need to create a variable that can represent a filename with a date that changes every day. If you know how to do that, I'd love some help. Otherwise, I'll just figure it out if I tinker with it long enough!
July 10, 2008 at 8:18 pm
I am sorry to get in on this discussion late, but the fact you were talking about DTS and FTP caught my attention.
I inherited a SQL Server 2000 database running on a Windows 2003 server.
I am a novice with regard to DTS and know nothing about FTP from SS.
We are trying to set up a process modeled after something I saw in another company. One SQL Server would export a series of flat files and dump them on a remote server. Our SS database would look for the files in a specific folder, compare the file dates with files it had already imported, then import the new data from the latest flat file.
Is it feasible to do something like what I described using DTS and FTP in SS2K?
If so, could you point me to a webpage that would help me learn how to implement it in our system?
Many thanks! 🙂
July 10, 2008 at 9:35 pm
Hopefully one of the other posters will get back to you.
We upgraded from 2000 to 2005 about a year ago, and prior to that I tried to avoid DTS like the plague.
But once we had upgraded I was faced with a few situations as you describe where I needed to dump files on their server and pickup other files from their sever, import them to ours and process them.
DTS is now part of Business Intelligence Studio in 2005, and so the prospect of the interface was the daunting part for me... very graphical with SOOO many options and properties you can configure, and precious little written about how to do most of the things in there.
However, with the help of this website and a lot of searching around for examples on the web, I've just had this explosion of development using "SQL Server Integration Services (SSIS... and formerly DTS)! Once you get how it works, it's easy! One of the helps in this little learning exercise was a .Net programmer here. Since they use Business Intelligence for other tasks, he was familiar with the FEEL of the screens so could help me figure out what was what.
I don't know what's possible in 2000 in this regard, but in 2005, in addition to easy imports/exports and lots of configurable FTP task options, you also have file system options and the like where you can do formerly windows tasks from within your SQL Server. Very convenient for renaming and archiving files at the same time everynight from a SQL job!
If there's any chance your company is looking to upgrade to 2005 in the near future, I highly suggest that you do it sooner rather than later, as SSIS will lend to these new tasks you're doing, and my guess is that you'd have to rewrite half of them if you coded them in 2000 first and then upgraded them!
When we upgraded, there was some bug in the copy program in 2005 that lets you copy the 2000 database to 2005, so I simply did a backup, created a new DB of the same name on the SQL2005 server and restored that backup, and it worked fine. We had NO issues with our data between the two versions. NONE.
Anyway, I know that doesn't help you with your current situation, but wanted to let you know that despite my reluctance when we did so, upgrading to 2005 turned out to be a VERY good thing. Possibly 2008 might be even better, as there are some useful new data types being brought in, especially with dates and hierarchies.
Good luck with your problem. If you upgrade to 2005 and have any questions about SSIS after that, feel free to contact me! I'm learning fast!
July 11, 2008 at 8:40 pm
That is encouraging to know that 2005 makes it easier. It is like pulling teeth to get our customer to upgrade. That is why I am stuck with DTS. I have had success doing a number of things in DTS thanks to SQLDTS.com, but never anything with FTP.
The customer (a federal agency) is really tightening up security and I have to export between to LANs, however, they favor "sneaker-net" transfer between the two. I did get them to concede that if I did a "one-way" (only) FTP transfer of files, that would pass muster and we could do our job.
Thanks for the info!
July 13, 2008 at 6:55 am
If it helps add any fuel to the case for upgrading to 2005, I heard there is a site called SSIS.com or something which I think is the sister site to the one you mentioned.
Don't get me wrong, you'll still be using DTS in 2005, but from an interface that actually allows you to do things graphically when you want, and makes a little more sense!
For FTP-ing you creat a connection through the connection manager, where you specify all the details on the FTP Server, like your username and password, and I'm sure you could find some bullet-proof ways to secure that info. Then, it's as easy as using it as any other sort of connection would be.
Good luck with it all. I was a big fan of 2000, but 2005 is a nice improvement. It sounds to me like 2008 will even be more so, if there aren't too many bugs with all the new things they've put in it!
July 14, 2008 at 8:26 pm
Thanks for the info 😀
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply