FTP & File System Task

I'm just learning 2005, so apologies in advance for the newb questions.

I am facing the following situation: Each day I have to upload an assortment of .csv files with variable names (eg. FileOneYYYYMMDD.csv, FileTwoYYYYMMDD.csv, etc.) to an FTP site, from the following directory structure:

Directory1

SubDirectoryA

SubDirectoryB

SubDirectoryC

I have accomplished this by setting up a package with three sequence containers (one for each subdirectory), each of which holds a Foreach loop (with a file enumerator configured as *.csv), each of which holds an FTP Task. This may not be the best way to do it, but it works. But if there's a better way I'd like to know!

Anyway, the wall I've run into is trying to move the .csv files to an archive directory after they've been uploaded. The wildcard variable doesn't seem to work with the File System Task, so I'm having a hard time figuring out how to move a bunch of variably named .csv files at different depths of a directory structure to an archive directory.

I've searched but can't seem to find a solution.

Why not add a file system task after the FTP task (hook them up together with a precedence constraint) to move the file after the FTP task completes?|||

Nah, that sounds too easy.

On a more serious note, it seems I had to also set DelayValidation = True on the File System Task.

|||

I have a similar process.

I'm grabbing csv's that contain a date in the name from an ftp site and dropping them locally in the "Processing" folder. Once the file processes completely I move the file to the "Processed" folder. If it errors then I move it to the "Error" folder and have appended to the name of the file the date & time it errored.

In addition, before I kick off this whole process I use a script component to check if a file exists either in the "Processed" or "Processing" folder before bothering to ftp it.

As far as the errored files go, since I make the name unique with the timestamp i can attempt the uploads infinite number of times.

Also, I'm using a For Look container with Package level variables so I can access the variables at any time throughout the process.

from XML file to sqlserver table

Hello; I'm a newby with openxml; I'm sorry in advance for my question. I've
found a lot of documentation about openxml but I've still a doubt...
In this simple example I'm able to load in a table an extract of a xml file:
here you are:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a INSERT statement that uses the OPENXML rowset provider.
INSERT INTO CUSTOMER
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
The question is very simple, probably; how can I manage the file without
"embed it" in to the code between the ' ' (set @.doc='...')?
In fact my file is potentially large and I would like to open the file and
insert the record dinamically without paste it in the procedure code....
Thanks in advance.
Bruno Stefanutti
The best way is to put the expression into a stored procedure and pass the
value as a paramater (you can use TEXT or NTEXT as the parameter type).
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
> Hello; I'm a newby with openxml; I'm sorry in advance for my question.
> I've
> found a lot of documentation about openxml but I've still a doubt...
> In this simple example I'm able to load in a table an extract of a xml
> file:
> here you are:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order CustomerID="VINET" EmployeeID="5"
> OrderDate="1996-07-04T00:00:00">
> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order CustomerID="LILAS" EmployeeID="3"
> OrderDate="1996-08-16T00:00:00">
> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a INSERT statement that uses the OPENXML rowset provider.
> INSERT INTO CUSTOMER
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> The question is very simple, probably; how can I manage the file without
> "embed it" in to the code between the ' ' (set @.doc='...')?
> In fact my file is potentially large and I would like to open the file
> and
> insert the record dinamically without paste it in the procedure code....
> Thanks in advance.
> Bruno Stefanutti
>
>
|||...When you say "expression" this means I have to pass the path of my xml
file as a parameter? Or you intend every "slice" of the file? Can you explain
a little example?
When I receive the file xml to import, I don't know his dimension.
Thanks in advance.
Bruno Stefanutti
"Michael Rys [MSFT]" wrote:

> The best way is to put the expression into a stored procedure and pass the
> value as a paramater (you can use TEXT or NTEXT as the parameter type).
> Best regards
> Michael
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
>
>
|||No I mean the actual content of the XML.
Here is an example:
create procedure example @.x NTEXT
as
declare @.h int
exec sp_xml_preparedocument @.h OUTPUT, @.x
SELECT *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName')
SELECT *
FROM OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
CustomerID varchar(5) '../@.ID',
OrderID varchar(5),
OrderDate datetime)
select *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName') C
JOIN OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
OrderID varchar(5),
OrderDate datetime) O
ON C.ID=O.ParentID
exec sp_xml_removedocument @.h
go
-- The following is a TSQL call to the stored proc. Otherwise you use your
favorite client provider to call the procedure with the parameter
exec example N'<root>
<Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="8000"
Country="Switzerland" Phone="555-555555" Fax="555-555555">
<Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="29.46" />
<Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="149.46" />
</Customer>
</root>'
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...[vbcol=seagreen]
> ...When you say "expression" this means I have to pass the path of my xml
> file as a parameter? Or you intend every "slice" of the file? Can you
> explain
> a little example?
> When I receive the file xml to import, I don't know his dimension.
> Thanks in advance.
> Bruno Stefanutti
> "Michael Rys [MSFT]" wrote:
|||I tried using the text type before. It doesnt' allow it. How is it working
for you?
"Michael Rys [MSFT]" wrote:

> No I mean the actual content of the XML.
> Here is an example:
> create procedure example @.x NTEXT
> as
> declare @.h int
> exec sp_xml_preparedocument @.h OUTPUT, @.x
> SELECT *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName')
> SELECT *
> FROM OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> CustomerID varchar(5) '../@.ID',
> OrderID varchar(5),
> OrderDate datetime)
> select *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName') C
> JOIN OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> OrderID varchar(5),
> OrderDate datetime) O
> ON C.ID=O.ParentID
> exec sp_xml_removedocument @.h
> go
> -- The following is a TSQL call to the stored proc. Otherwise you use your
> favorite client provider to call the procedure with the parameter
> exec example N'<root>
> <Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
> ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="8000"
> Country="Switzerland" Phone="555-555555" Fax="555-555555">
> <Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
> RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="29.46" />
> <Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
> RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="149.46" />
> </Customer>
> </root>'
>
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
>
>
|||Did you try the example below? Note that the text type can only be used as a
parameter and not a variable in SQL Server 2000 (this will work with SQL
Server 2005).
Best regards
Michael
"Reese77" <Reese77@.discussions.microsoft.com> wrote in message
news:3D38697A-2E7A-4BC2-AABD-6CC8017E32A3@.microsoft.com...[vbcol=seagreen]
>I tried using the text type before. It doesnt' allow it. How is it
>working
> for you?
> "Michael Rys [MSFT]" wrote:

from XML file to sqlserver table

Hello; I'm a newby with openxml; I'm sorry in advance for my question. I've
found a lot of documentation about openxml but I've still a doubt...
In this simple example I'm able to load in a table an extract of a xml file:
here you are:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a INSERT statement that uses the OPENXML rowset provider.
INSERT INTO CUSTOMER
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
The question is very simple, probably; how can I manage the file without
"embed it" in to the code between the ' ' (set @.doc='...')?
In fact my file is potentially large and I would like to open the file and
insert the record dinamically without paste it in the procedure code....
Thanks in advance.
Bruno StefanuttiThe best way is to put the expression into a stored procedure and pass the
value as a paramater (you can use TEXT or NTEXT as the parameter type).
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
> Hello; I'm a newby with openxml; I'm sorry in advance for my question.
> I've
> found a lot of documentation about openxml but I've still a doubt...
> In this simple example I'm able to load in a table an extract of a xml
> file:
> here you are:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order CustomerID="VINET" EmployeeID="5"
> OrderDate="1996-07-04T00:00:00">
> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order CustomerID="LILAS" EmployeeID="3"
> OrderDate="1996-08-16T00:00:00">
> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a INSERT statement that uses the OPENXML rowset provider.
> INSERT INTO CUSTOMER
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> The question is very simple, probably; how can I manage the file without
> "embed it" in to the code between the ' ' (set @.doc='...')?
> In fact my file is potentially large and I would like to open the file
> and
> insert the record dinamically without paste it in the procedure code....
> Thanks in advance.
> Bruno Stefanutti
>
>|||...When you say "expression" this means I have to pass the path of my xml
file as a parameter? Or you intend every "slice" of the file? Can you explai
n
a little example?
When I receive the file xml to import, I don't know his dimension.
Thanks in advance.
Bruno Stefanutti
"Michael Rys [MSFT]" wrote:

> The best way is to put the expression into a stored procedure and pass the
> value as a paramater (you can use TEXT or NTEXT as the parameter type).
> Best regards
> Michael
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
>
>|||No I mean the actual content of the XML.
Here is an example:
create procedure example @.x NTEXT
as
declare @.h int
exec sp_xml_preparedocument @.h OUTPUT, @.x
SELECT *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName')
SELECT *
FROM OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
CustomerID varchar(5) '../@.ID',
OrderID varchar(5),
OrderDate datetime)
select *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName') C
JOIN OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
OrderID varchar(5),
OrderDate datetime) O
ON C.ID=O.ParentID
exec sp_xml_removedocument @.h
go
-- The following is a TSQL call to the stored proc. Otherwise you use your
favorite client provider to call the procedure with the parameter
exec example N'<root>
<Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="8000"
Country="Switzerland" Phone="555-555555" Fax="555-555555">
<Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="29.46" />
<Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="149.46" />
</Customer>
</root>'
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
> ...When you say "expression" this means I have to pass the path of my xml
> file as a parameter? Or you intend every "slice" of the file? Can you
> explain
> a little example?
> When I receive the file xml to import, I don't know his dimension.
> Thanks in advance.
> Bruno Stefanutti
> "Michael Rys [MSFT]" wrote:
>|||I tried using the text type before. It doesnt' allow it. How is it working
for you?
"Michael Rys [MSFT]" wrote:

> No I mean the actual content of the XML.
> Here is an example:
> create procedure example @.x NTEXT
> as
> declare @.h int
> exec sp_xml_preparedocument @.h OUTPUT, @.x
> SELECT *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName')
> SELECT *
> FROM OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> CustomerID varchar(5) '../@.ID',
> OrderID varchar(5),
> OrderDate datetime)
> select *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName') C
> JOIN OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> OrderID varchar(5),
> OrderDate datetime) O
> ON C.ID=O.ParentID
> exec sp_xml_removedocument @.h
> go
> -- The following is a TSQL call to the stored proc. Otherwise you use your
> favorite client provider to call the procedure with the parameter
> exec example N'<root>
> <Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
> ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="800
0"
> Country="Switzerland" Phone="555-555555" Fax="555-555555">
> <Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
> RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="29.46" />
> <Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
> RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="149.46" />
> </Customer>
> </root>'
>
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
>
>|||Did you try the example below? Note that the text type can only be used as a
parameter and not a variable in SQL Server 2000 (this will work with SQL
Server 2005).
Best regards
Michael
"Reese77" <Reese77@.discussions.microsoft.com> wrote in message
news:3D38697A-2E7A-4BC2-AABD-6CC8017E32A3@.microsoft.com...
>I tried using the text type before. It doesnt' allow it. How is it
>working
> for you?
> "Michael Rys [MSFT]" wrote:
>