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