Sending query results with the SSIS FTP task.

[jWhytis]

Hello All,

I was wondering it it is possible to ftp the results of a SQL Query without
first saving those results to a file.

Thanks

[Todd C]

FTP = "File Transfer Protocol"
How do you intend to do this operation if you don't have a file to transfer?
--
Todd C
MCTS SQL Server 2005

Post by jWhytis
Hello All,
I was wondering it it is possible to ftp the results of a SQL Query without
first saving those results to a file.
Thanks

[jWhytis]

Todd,


Thanks for the reply. Its all about level of abstraction and realizing the
file is just a string of bits.


Here is whats happening as the user understands it:


A query produces results
The results are saved to a file
An FTP Client is started
The File to be transferred is specified in ftp client
The file is sent.


Heres is what is happening as a developer understands it:
A query produces results
The results are stored in short term memory
The results are saved to file
a filestream object is created and the results in memory are transferred to
long term storage via the filestream object
An FTP client is started
A set of objects is instantiated that support user interface and ftp
specific functions
The file to be transferred is specified in the ftp client
a filestream object is opened and the file is loaded into short term memory
in the filestream object
File is sent
the filestream object is passed to the ftp objects which negotiates the
transfer


I just want to skip the middle man and pass the query results as a
filestream object to the FTP process.


I did something similar with AS2 file transfer last year where instead of
receiving and storing xml files on disc then importing them into the database
we setup a web service to act as an AS2 server and sent the files received
directly to database tables. I used nSoftwares AS2 API and could catch the
file in the filestream phase and send it to the database instead of disc. I'd
like to implement the same thing here just going the other way.

Post by Todd C
FTP = "File Transfer Protocol"
How do you intend to do this operation if you don't have a file to transfer?
--
Todd C
MCTS SQL Server 2005

Post by jWhytis
Hello All,
I was wondering it it is possible to ftp the results of a SQL Query without
first saving those results to a file.
Thanks

[Todd C]

I like your explanation of what is going on under the covers. But I think a
lot of the SSIS 'stock' tasks rely on those 'hard' objects like files to do
their thing.

I suppose you could always roll your own SSIS task (never done it but know
it can be done) to accomplish what you want?

If you don't mind my asking, why cut out the file system middleman? Space
constraints? Speed of execution? Just curious.
--
Todd C
MCTS SQL Server 2005

Post by jWhytis
Todd,
Thanks for the reply. Its all about level of abstraction and realizing the
file is just a string of bits.
A query produces results
The results are saved to a file
An FTP Client is started
The File to be transferred is specified in ftp client
The file is sent.
A query produces results
The results are stored in short term memory
The results are saved to file
a filestream object is created and the results in memory are transferred to
long term storage via the filestream object
An FTP client is started
A set of objects is instantiated that support user interface and ftp
specific functions
The file to be transferred is specified in the ftp client
a filestream object is opened and the file is loaded into short term memory
in the filestream object
File is sent
the filestream object is passed to the ftp objects which negotiates the
transfer
I just want to skip the middle man and pass the query results as a
filestream object to the FTP process.
I did something similar with AS2 file transfer last year where instead of
receiving and storing xml files on disc then importing them into the database
we setup a web service to act as an AS2 server and sent the files received
directly to database tables. I used nSoftwares AS2 API and could catch the
file in the filestream phase and send it to the database instead of disc. I'd
like to implement the same thing here just going the other way.

Post by Todd C
FTP = "File Transfer Protocol"
How do you intend to do this operation if you don't have a file to transfer?
--
Todd C
MCTS SQL Server 2005

Post by jWhytis
Hello All,
I was wondering it it is possible to ftp the results of a SQL Query without
first saving those results to a file.
Thanks

[jWhytis]

Todd,

Sorry for the late reply, I actually replied last week but the post didn't
seem to take. I have headed down the path of custom coding and will post
back if warranted. To answer your other question I am trying to keep the
results in memory to meet PCI/Privacy requirements. Data stored on disc is
held to higher standards I'd just as soon avoid.

Thanks.

Post by Todd C
I like your explanation of what is going on under the covers. But I think a
lot of the SSIS 'stock' tasks rely on those 'hard' objects like files to do
their thing.
I suppose you could always roll your own SSIS task (never done it but know
it can be done) to accomplish what you want?
If you don't mind my asking, why cut out the file system middleman? Space
constraints? Speed of execution? Just curious.
--
Todd C
MCTS SQL Server 2005

Post by jWhytis
Todd,
Thanks for the reply. Its all about level of abstraction and realizing the
file is just a string of bits.
A query produces results
The results are saved to a file
An FTP Client is started
The File to be transferred is specified in ftp client
The file is sent.
A query produces results
The results are stored in short term memory
The results are saved to file
a filestream object is created and the results in memory are transferred to
long term storage via the filestream object
An FTP client is started
A set of objects is instantiated that support user interface and ftp
specific functions
The file to be transferred is specified in the ftp client
a filestream object is opened and the file is loaded into short term memory
in the filestream object
File is sent
the filestream object is passed to the ftp objects which negotiates the
transfer
I just want to skip the middle man and pass the query results as a
filestream object to the FTP process.
I did something similar with AS2 file transfer last year where instead of
receiving and storing xml files on disc then importing them into the database
we setup a web service to act as an AS2 server and sent the files received
directly to database tables. I used nSoftwares AS2 API and could catch the
file in the filestream phase and send it to the database instead of disc. I'd
like to implement the same thing here just going the other way.

Post by Todd C
FTP = "File Transfer Protocol"
How do you intend to do this operation if you don't have a file to transfer?
--
Todd C
MCTS SQL Server 2005

Post by jWhytis
Hello All,
I was wondering it it is possible to ftp the results of a SQL Query without
first saving those results to a file.
Thanks

[jWhytis]

Todd,

Sorry for the late reply, I actually replied last week but the post didn't
seem to take. I have headed down the path of custom coding and will post
back if warranted. To answer your other question I am trying to keep the
results in memory to meet PCI/Privacy requirements. Data stored on disc is
held to higher standards I'd just as soon avoid.

Thanks.

Post by Todd C
I like your explanation of what is going on under the covers. But I think a
lot of the SSIS 'stock' tasks rely on those 'hard' objects like files to do
their thing.
I suppose you could always roll your own SSIS task (never done it but know
it can be done) to accomplish what you want?
If you don't mind my asking, why cut out the file system middleman? Space
constraints? Speed of execution? Just curious.
--
Todd C
MCTS SQL Server 2005

Post by jWhytis
Todd,
Thanks for the reply. Its all about level of abstraction and realizing the
file is just a string of bits.
A query produces results
The results are saved to a file
An FTP Client is started
The File to be transferred is specified in ftp client
The file is sent.
A query produces results
The results are stored in short term memory
The results are saved to file
a filestream object is created and the results in memory are transferred to
long term storage via the filestream object
An FTP client is started
A set of objects is instantiated that support user interface and ftp
specific functions
The file to be transferred is specified in the ftp client
a filestream object is opened and the file is loaded into short term memory
in the filestream object
File is sent
the filestream object is passed to the ftp objects which negotiates the
transfer
I just want to skip the middle man and pass the query results as a
filestream object to the FTP process.
I did something similar with AS2 file transfer last year where instead of
receiving and storing xml files on disc then importing them into the database
we setup a web service to act as an AS2 server and sent the files received
directly to database tables. I used nSoftwares AS2 API and could catch the
file in the filestream phase and send it to the database instead of disc. I'd
like to implement the same thing here just going the other way.

Post by Todd C
FTP = "File Transfer Protocol"
How do you intend to do this operation if you don't have a file to transfer?
--
Todd C
MCTS SQL Server 2005

Post by jWhytis
Hello All,
I was wondering it it is possible to ftp the results of a SQL Query without
first saving those results to a file.
Thanks