April 4, 2012 at 4:24 am
Hi
I am trying to create a FTP task in SSIS to pull data from a FTP location. The files are put in a folder with current date ( e.g 2012-04-04 ,2012-04-03 etc )
I need to pull the data from these files on daily basis.
Can some one help me with how to go about this?
April 4, 2012 at 4:47 am
you will need to create an expression on the remote path something like the following, changing it to suit your needs
(DT_WSTR, 4) YEAR( GETDATE() ) + right("0" + (DT_WSTR,2) DATEPART("mm", GETDATE() ), 2) + right("0" + (DT_WSTR, 2) DAY( GETDATE() ),2)
April 4, 2012 at 6:37 am
Thanks for prompt reply
/pub/Cell_Availability/2012-04-04
this is the path
so in remote path variable
/pub/Cell_Availability/ + (DT_WSTR, 4) YEAR( GETDATE() ) + right("0" + (DT_WSTR,2) DATEPART("mm", GETDATE() ), 2) + right("0" + (DT_WSTR, 2) DAY( GETDATE() ),2)
will this work?
April 4, 2012 at 6:41 am
no it wont work as you are not adding in the "-" inbetween the date parts
the below will only give you /pub/Cell_Availability/20120404
April 4, 2012 at 6:53 am
Sorry new to ssis
/pub/Cell_Availability/ + (DT_WSTR, 4) YEAR( GETDATE() ) + - + right("0" + (DT_WSTR,2) DATEPART("mm", GETDATE() ), 2) + - + right("0" + (DT_WSTR, 2) DAY( GETDATE() ),2)
is this fine?
April 4, 2012 at 6:56 am
yeah, looks right, just make sure you wrap stings with " " then hit evaluate expression to see if it does what you want it to do
"/pub/Cell_Availability/" + (DT_WSTR, 4) YEAR( GETDATE() ) + "-" + right("0" + (DT_WSTR,2) DATEPART("mm", GETDATE() ), 2) + "-" + right("0" + (DT_WSTR, 2) DAY( GETDATE() ),2)
April 4, 2012 at 7:26 am
Hi Thanks
@[User::Variable] = "/pub/Cell_Availability/" + (DT_WSTR, 4) YEAR( GETDATE() ) + "-" + right("0" + (DT_WSTR,2) DATEPART("mm", GETDATE() ), 2) + "-" + right("0" + (DT_WSTR, 2) DAY( GETDATE() ),2) +"/"
i validated and gives correct o/p
but when I run the package it give "Package Validation Error"
Failed to lock variable "/pub/Cell_Availability/2012-04-04" for read access with error
"The variable cannot be found. This occurs when an attempt tis made to retrieve a variable from variables collectionon a container during execution of package.and the variable is not there."
April 4, 2012 at 7:38 am
what happens if you tell it that the remote path is not a variable and then set the expression on the remotepath property of the actual FTP task
April 4, 2012 at 7:39 am
Great Stuff works Perfectly fine. Many thanks!!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy