Configure FTP in Foreach loop

I need to put a FTP task inside a Foreach Loop Container to upload data files to many different FTP servers. The container holds a variable with object data type that includes necessory FTP info (FTP server address, login, password and remote path). How do I configure FTP task so that I can pass the variables to it?

Thanks,

Jia

I get a little work around with command line FTP -

Inside the loop, I create a batch file with the variables passed from the container. Then use xp_cmsshell to call the file. It works although looks a little cumbersome. I still hope there is a better way to do it.

conditionally executing sql statement

I need to write .sql file in a dynamic way.
inside the file i need to decide at run time (of the sql script) whether or
not to run a SQL statement that I don't know it's content while dynamically
creating the sql script, this sql statement might include a GO statement
what makes a problem putting it inside an if begin end block.
for example
in my script a runtime check
select @.runStatement = configVal from database at script runtime execution
if @.runStatement =1
begin
-- here comes an unknown sql statement at the time of creating the sql
script that might include a GO command which will break the syntax of the
entire block
-- go <- this here makes a TSQL error for the end command since it
breaks the begin / end block.
end
can someone recomend of an approach for how to solve this?
execute sql is not an option here since the internal SQL statement might be
larget then 4000 nvarchar characters and I can not declarae a @.ntext local
variable
TIA.>> inside the file i need to decide at run time (of the sql script) whether
Under normal circumstances, this is a poor way to write SQL code. The kludgy
workaround is to assign the SQL statement to a variable, replace the tokens
that are not needed and use EXEC or sp_ExecuteSQL to execute it.
The right way can be suggested only if you can explain the overall
situation. Why do you have to resort to such complex approach? Is there a
3rd party tool involved?
Anith|||martin (news.microsoft.com) writes:
> I need to write .sql file in a dynamic way.
> inside the file i need to decide at run time (of the sql script) whether
> or not to run a SQL statement that I don't know it's content while
> dynamically creating the sql script, this sql statement might include a
> GO statement what makes a problem putting it inside an if begin end
> block.
> for example
> in my script a runtime check
> select @.runStatement = configVal from database at script runtime execution
> if @.runStatement =1
> begin
> -- here comes an unknown sql statement at the time of creating the
> sql script that might include a GO command which will break the syntax
> of the entire block
> -- go <- this here makes a TSQL error for the end command since it
> breaks the begin / end block.
> end
>
> can someone recomend of an approach for how to solve this?
> execute sql is not an option here since the internal SQL statement might
> be larget then 4000 nvarchar characters and I can not declarae a @.ntext
> local variable
Are you on SQL 2000 or SQL 2005?
If you are on SQL 2000, I would srtongly recommend that you run the
control loop from a client. It could be very difficult to sort out
from SQL only. It could be a little easier on SQL 2005, since there
you can work with nvarchar(MAX) and you could do the batch splitting
in CLR code.
I echoes Aniths suggestion that you could be better served by telling
us the full story. This could give you better suggestions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||it should support database modifications / upgrades.
the commands are not known at the time of designing the tool that will
execute the statements.
i understand that running each script from a client tool like a VB.NET app
is a good option but is it not possible to run it from sql script file using
some goto label....?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eedlU0VaGHA.1020@.TK2MSFTNGP02.phx.gbl...
> Under normal circumstances, this is a poor way to write SQL code. The
> kludgy workaround is to assign the SQL statement to a variable, replace
> the tokens that are not needed and use EXEC or sp_ExecuteSQL to execute
> it.
>
> The right way can be suggested only if you can explain the overall
> situation. Why do you have to resort to such complex approach? Is there a
> 3rd party tool involved?
> --
> Anith
>|||martin (news.microsoft.com) writes:
> it should support database modifications / upgrades.
> the commands are not known at the time of designing the tool that will
> execute the statements.
> i understand that running each script from a client tool like a VB.NET
> app is a good option but is it not possible to run it from sql script
> file using some goto label....?
Possible and possible. With severe kludges maybe. And it depends on the SQL
Server version.
If the purpose of the tool is run scripts for database changes, I strongly
recommend using a control part in a client language.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

conditional syntax in functions

I can't seem to get the nesting correct for an IF THEN condition inside a
function. My intent is to return the results (a table) of one of two
dfferent complex select statements. And, really, I am porting this SELECT
over from a working stored procedure. I wanted the convenience of being able
to use it in another select statement to further limit it down without
filters.
I keep getting "Incorrect syntax near 'BEGIN'"
To summarize:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS table
AS
BEGIN
If @.category=@.subcategory
RETURN (
SELECT ...... WHERE products.FK_category = @.category
)
ELSE
RETURN (
SELECT ...... WHERE products.FK_category = @.category
AND products.FK_subcategory = @.subcategory
)
END
GO
Am I doing this correctly?
Thanks
JulianYou are mixing inline table-valued functions (which are basically views that
accept parameters) and multi-statement table-valued functions (which allow
control-flow statement like IF..ELSE
Try the following to have an inline table-valued function:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS table
AS
RETURN (
SELECT ...... WHERE products.FK_category = @.category
AND products.FK_subcategory = CASE WHEN
@.category=@.subcategory
THEN products.FK_subcategory ELSE @.subcategory
END
)
END
GO
Jacco Schalkwijk
SQL Server MVP
"stjulian" <anonymous@.discussions.microsoft.com> wrote in message
news:eUH9CyHYFHA.3712@.TK2MSFTNGP09.phx.gbl...
>I can't seem to get the nesting correct for an IF THEN condition inside a
>function. My intent is to return the results (a table) of one of two
>dfferent complex select statements. And, really, I am porting this SELECT
>over from a working stored procedure. I wanted the convenience of being
>able to use it in another select statement to further limit it down without
>filters.
> I keep getting "Incorrect syntax near 'BEGIN'"
> To summarize:
> CREATE FUNCTION dbo.fnGetProducts
> ( @.category int = 1,
> @.subcategory int = 1,
> @.classification int = 0)
> RETURNS table
> AS
> BEGIN
> If @.category=@.subcategory
> RETURN (
> SELECT ...... WHERE products.FK_category = @.category
> )
> ELSE
> RETURN (
> SELECT ...... WHERE products.FK_category = @.category
> AND products.FK_subcategory = @.subcategory
> )
> END
> GO
>
>
> Am I doing this correctly?
>
> Thanks
> Julian
>|||If the in-line function would not work for you (because the two select
statements are completely different), you may want to use an
multi-statement function, i.e. something like this:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS @.result TABLE (
column1 int,
column2 varchar(50),
..
)
AS
BEGIN
IF @.category=@.subcategory BEGIN
INSERT INTO @.result (column1, column2, ...)
SELECT .... WHERE products.FK_category = @.category
END
ELSE BEGIN
INSERT INTO @.result (column1, column2, ...)
SELECT .... WHERE products.FK_category = @.category
AND products.FK_subcategory = @.subcategory
END
RETURN
END
GO
Of course, if the two SELECT statements are similar, it's easier (and
usually better) to write an in-line function, like Jacco suggested.
Razvan|||If you just have 2 select statement in your function, you can always write
it as an inline function. The two select statements must always return the
same columns when you have a multi-statement function, so you can always put
them in an inline function with a UNION. Inline functions have less overhead
and in general leas to better query plans.
Jacco Schalkwijk
SQL Server MVP
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116961197.756965.191640@.g49g2000cwa.googlegroups.com...
> If the in-line function would not work for you (because the two select
> statements are completely different), you may want to use an
> multi-statement function, i.e. something like this:
> CREATE FUNCTION dbo.fnGetProducts
> ( @.category int = 1,
> @.subcategory int = 1,
> @.classification int = 0)
> RETURNS @.result TABLE (
> column1 int,
> column2 varchar(50),
> ...
> )
> AS
> BEGIN
> IF @.category=@.subcategory BEGIN
> INSERT INTO @.result (column1, column2, ...)
> SELECT .... WHERE products.FK_category = @.category
> END
> ELSE BEGIN
> INSERT INTO @.result (column1, column2, ...)
> SELECT .... WHERE products.FK_category = @.category
> AND products.FK_subcategory = @.subcategory
> END
> RETURN
> END
> GO
> Of course, if the two SELECT statements are similar, it's easier (and
> usually better) to write an in-line function, like Jacco suggested.
> Razvan
>