SSIS - FTP TASK - DYNAMIC FILENAME

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS - FTP TASK - DYNAMIC FILENAME

40 posts, Page 1 of 4

1 2 3 4 »»»

SSIS - FTP TASK - DYNAMIC FILENAME

Rate Topic

Display Mode

Topic Options

Author

Message

Jafar

Posted Friday, May 09, 2008 4:22 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, February 23, 2013 9:07 AM
Points: 10, Visits: 407

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

Post #497714

Michael Earl-395764

Posted Friday, May 09, 2008 5:55 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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"

Post #497767

James Riehl

Posted Friday, May 09, 2008 7:02 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, December 17, 2009 4:03 PM
Points: 42, 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.

Post #497833

Jafar

Posted Friday, May 09, 2008 9:42 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, February 23, 2013 9:07 AM
Points: 10, Visits: 407

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 :)

Jafar

Post #498020

Michael Earl-395764

Posted Friday, May 09, 2008 9:55 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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.

Post #498039

Michael Earl-395764

Posted Friday, May 09, 2008 9:57 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

BTW - I would go with variables and expressions before using the ForEach loop container. It will be a much cleaner solution.

Post #498043

Michael Earl-395764

Posted Friday, May 09, 2008 9:59 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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.

Post #498045

Jafar

Posted Friday, May 09, 2008 7:59 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, February 23, 2013 9:07 AM
Points: 10, Visits: 407

Hi Michael,

Thanks for all your help! :)

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

Post #498313

michaelwelcome

Posted Friday, May 09, 2008 10:02 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 29, 2012 8:04 AM
Points: 17, 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

Post #498316

Michael Earl-395764

Posted Monday, May 12, 2008 6:02 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

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.

Post #498691

« Prev Topic | Next Topic »

40 posts, Page 1 of 4

1 2 3 4 »»»

Permissions