SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - FTP TASK - DYNAMIC FILENAME


SSIS - FTP TASK - DYNAMIC FILENAME

Author
Message
Jafar
Jafar
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 414
Sorry guys, I'm really new to this. Can somebody tell me the easiest way to use the FTP task with a dynamic local path. I just want the FTP task to send whatever file is in the directory I specify.

I'm creating a package that extracts data from a table to a pipe-delimited file. I then use an external process (since using Expressions for it was a nightmare) to rename the file so that it'll reflect the previous month (e.g. April2008_report.txt). Then, I have to FTP that file. That's where my problem is. It seems I need to specify the exact filename on the local path of the FTP task, and since the filename will change each month... well, you see my problem.

Is there a way to tell the FTP task to just send *.txt, or something to that effect?

Jafar
Michael Earl-395764
Michael Earl-395764
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17475 Visits: 23078
If the file is alone in the directory, you could use a ForEach loop and loop through the files (of which there will be one).

I am not sure why you would have had to use an external process to rename the file to the current month / current year. If you were doing this with an expression variable and a file system task, your variable could be used in the ftp task.


The expression for the variable would be:

(MONTH(GETDATE()) == 1 ? "January" : MONTH(GETDATE()) == 2 ? "February" : MONTH(GETDATE()) == 3 ? "March" :
MONTH(GETDATE()) == 4 ? "April" : MONTH(GETDATE()) == 5 ? "May" : MONTH(GETDATE()) == 6 ? "June" :
MONTH(GETDATE()) == 7 ? "July" : MONTH(GETDATE()) == 8 ? "August" : MONTH(GETDATE()) == 9 ? "September" :
MONTH(GETDATE()) == 10 ? "October" : MONTH(GETDATE()) == 11 ? "November" :
MONTH(GETDATE()) == 12 ? "December" : "InvalidMonth")
+ (DT_WSTR, 4) DATEPART("YYYY",GETDATE())
+ "_report.txt"

James Riehl
James Riehl
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 87
You can also use a script task to get your *.txt functionality

This will require writing a function opens an ftp connection.

This is ideal if you can't actually predict the filename beyond a reasonable doubt. If you need this, i'll Post asap.
Jafar
Jafar
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 414
Hey Guys,

Thanks a bunch for the replies.

I think I'm gonna go with the for each loop solution and just pass the path or filename as a variable to be accessed by the ftp task.

As for the use or not use of expression. The one you included is sooo cool, I never would've thought of that since i'm really still not familiar with the syntax used in the expression builder. However, I'm not sure how I can use that to display the previous month, not the current one. My report will contain data for the previous month, so it should reflect the same accordingly.

On a related note, is there a source (site, ebook, whatever), that teaches how to write expressions, it's syntax, etc.?

Thanks again for you patience Smile

Jafar
Michael Earl-395764
Michael Earl-395764
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17475 Visits: 23078
Previous month:

(MONTH((DATEADD("MONTH",-1,GETDATE()))) == 1 ? "January" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 2 ? "February" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 3 ? "March" :
MONTH((DATEADD("MONTH",-1,GETDATE()))) == 4 ? "April" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 5 ? "May" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 6 ? "June" :
MONTH((DATEADD("MONTH",-1,GETDATE()))) == 7 ? "July" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 8 ? "August" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 9 ? "September" :
MONTH((DATEADD("MONTH",-1,GETDATE()))) == 10 ? "October" : MONTH((DATEADD("MONTH",-1,GETDATE()))) == 11 ? "November" :
MONTH((DATEADD("MONTH",-1,GETDATE()))) == 12 ? "December" : "InvalidMonth")
+ (DT_WSTR, 4) DATEPART("YYYY",(DATEADD("MONTH",-1,GETDATE())))
+ "_report.txt"


I just replaced GETDATE() with (DATEADD("MONTH",-1,GETDATE()))

As far as a reference, I have not found a good one yet.
The documentation on expressions in SSIS is terrible, I tend to Google a lot.
Michael Earl-395764
Michael Earl-395764
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17475 Visits: 23078
BTW - I would go with variables and expressions before using the ForEach loop container. It will be a much cleaner solution.
Michael Earl-395764
Michael Earl-395764
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17475 Visits: 23078
Also, don't be afraid to post the "How do I?" on this site when you need expression help. There are some people that watch these forums that are really good with them.
Jafar
Jafar
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 414
Hi Michael,

Thanks for all your help! Smile

I'm trying your suggestions. I've removed the external process and configued my destination flat file to use a variable which points to the path directory of the file. I then used expressions to create the value (filename) of the connectionstring of my destination connection object. My data flow now works, and it creates the file with name I need. However, I'm still encountering a problem with my FTP task. I'm not sure if I'm doing it right, but I configured the localpath of my FTP task as a variable. I then used the same variable (as the one I used for my destination flat file). Again, I used expression builder to set the localpath property as the variable concatenated with the expression code you gave me. The error this time is:

Error: Failed to lock variable "c:\report\April2008_report.txt" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

I tried creating a new variable in case the error is because the other one is still being referenced by the data flow, but I still get the same result (error). Any ideas? I'm sure there's a simple explanation to this, just can't figure it out myself.

Jafar
michaelwelcome
michaelwelcome
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 45
Not sure on this, but it looks to me like you put the file path into the variable name rather than the variable value. It appears to be looking for a variable with the name of your file path. Does that make sense?Unsure
Michael Earl-395764
Michael Earl-395764
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17475 Visits: 23078
I think the last post hit it. I think you used expressions on your container and set the variable name to be the file path. I would suggest you use a variable set to evaluate as expression and use the expression to create file file path and name. Then you should not have to use expressions on your container at all, just select the variable in the editor.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search