June 7, 2011 at 2:00 pm
Hello all,
My companies ERP has been modified to import XML files we receive from a customer. The erp itself only does the import.
I need a way automate a couple of processes.
When the customer sends the XML files they will be sending them via FTP. Once the XML file is in the ftp directory I need a way to pull the file down then have the file emailed to several recipients so they can load the xml file.
I have never used SSIS before but know it has such tasks which may be able to automate these processes.
Can anyone point me in the right direction or give me some guidance on how on can accomplish such automation? Thanks.
June 7, 2011 at 2:30 pm
SSIS has a built-in FTP task operator. It won't work with SFTP ("Secure" FTP), but if it's vanilla FTP, it should be able to do what you need. Have you played with that at all?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 2:42 pm
GSquared (6/7/2011)
SSIS has a built-in FTP task operator. It won't work with SFTP ("Secure" FTP), but if it's vanilla FTP, it should be able to do what you need. Have you played with that at all?
I started to look at the ftp task editor but am not sure what I need to set in File Transfer settings/Local and Remote parameters
Then what will expressions do and can I use that to pull specific file names?
June 8, 2011 at 2:30 am
why not try with hardcoded values and get it working that way? The move onto the expressions to make it dynamic? 😉
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 8, 2011 at 6:47 am
kwoznica (6/7/2011)
GSquared (6/7/2011)
SSIS has a built-in FTP task operator. It won't work with SFTP ("Secure" FTP), but if it's vanilla FTP, it should be able to do what you need. Have you played with that at all?I started to look at the ftp task editor but am not sure what I need to set in File Transfer settings/Local and Remote parameters
Then what will expressions do and can I use that to pull specific file names?
You can use expressions to pull specific file names.
How familiar are you with the FTP standard? The SSIS FTP task is pretty standard on those points.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 8, 2011 at 7:45 am
GSquared (6/8/2011)
You can use expressions to pull specific file names.How familiar are you with the FTP standard? The SSIS FTP task is pretty standard on those points.
I am familiar with it just intimidated a bit by SSIS. I have a need to use it now but have little experience using it.
Crispin Proctor (6/8/2011)
why not try with hardcoded values and get it working that way? The move onto the expressions to make it dynamic? 😉
I think I will do just that. Start simple and try to build on it.
June 8, 2011 at 7:48 am
That's pretty much how I learned SSIS. Build something that can just barely do a "hello world" version of what I'm trying to accomplish, then move on to adding the pieces that make it really productive.
Try getting one file from an FTP site. You can even set up an FTP site locally on your own workstation for proof-of-concept work, so that if you mess something up, you aren't losing any files that matter to anyone.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 8, 2011 at 9:05 am
before you pull your hair out, on the FTP thing and password, if you want to save the password you have to set a variable to be the password and then using an expression on the FTP connection, pick-up the value from the variable.
First steps though...:-)
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 28, 2011 at 2:00 pm
Ok, so I got the ftp task to work and am able to move files from the ftp site to a shared directory inside the network. Then I want to execute a sendmail task to attach the files I just copied down. If I run the ftp task seperate from the sendmail task it works however with the precedence constraint in place and when running the entire package it fails because the send mail task does not see the files at the directory yet.
It doesn't make sense to me because if the ftp task runs first the files would be in the directory. It almost seems as if the sendmail task is running first though.
What could I possibly be missing?
Then I may have situations where multiple files are downloaded and each have a different filename. The sendmail task is only accepting one file name as the attachment. I need to add multiple file attachments to the email since the xml files are purchase orders such as PO1234.xml, po1235.xml, po1236.xml....etc
How can I handle various filenames?
Any suggestions are welcomed. Thanks.
June 29, 2011 at 4:29 pm
build a script task that generates the file names and then updates the SSIS variables. This is what i'm doing currently.
Joe.
June 29, 2011 at 4:32 pm
GSquared (6/7/2011)
SSIS has a built-in FTP task operator. It won't work with SFTP ("Secure" FTP), but if it's vanilla FTP, it should be able to do what you need. Have you played with that at all?
For SFTP, we just use a 3rd party app and call it from SSIS cmd.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 29, 2011 at 4:35 pm
kwoznica (6/28/2011)
Ok, so I got the ftp task to work and am able to move files from the ftp site to a shared directory inside the network. Then I want to execute a sendmail task to attach the files I just copied down. If I run the ftp task seperate from the sendmail task it works however with the precedence constraint in place and when running the entire package it fails because the send mail task does not see the files at the directory yet.It doesn't make sense to me because if the ftp task runs first the files would be in the directory. It almost seems as if the sendmail task is running first though.
What could I possibly be missing?
Then I may have situations where multiple files are downloaded and each have a different filename. The sendmail task is only accepting one file name as the attachment. I need to add multiple file attachments to the email since the xml files are purchase orders such as PO1234.xml, po1235.xml, po1236.xml....etc
How can I handle various filenames?
Any suggestions are welcomed. Thanks.
Rather than email those XML files all over the place, would it be acceptable to just send out an email notification to the necessary people and have them retrieve the files from the location where you just downloaded them to?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply