Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Sql to file bulk export best practice?


Good day everyone,

I have a question regarding the best way to export bulk data from Sql tables
or stored procedures into flat files. This question stems from our
infrastructure which I think is quite typical:

1. Database server (Sql 2005) has only SS, SSAS and SSIS installed and is
behind a firewall which currently only allows 1433 and 1434 traffic. Many
various databases are hosted here.

2. Front-end web application server (IIS 6, .NET 3) hosts many various web
applications. SSRS is installed here as well for Reporting.

3. Servers are Windows Server Standard 2003. Database server is 64-bit.
Neither are on a domain (although this is a possibility if it will solve our
problems).

Understanding the various options of BCP, SSIS, etc., we need to be able to
generate flat files from the database and have them available for secure
download on the web application server.

This means either we generate the files using BCP or a custom .NET solution
on the web app server, or we use SSIS on the database server to generate the
file, but then it must be transferred to the web app server somehow (UNC
path, FTP).

We would like to not have to create Windows users for each application that
need this approach and setting up FTP virtual directories isn't ideal either.
Also, creating these users means adding users (same user / password) to both
machines, and opening ports on our Sql firewall (which isn't a problem but we
want the most secure solution).

Ideally our application developers can manage this entirely given a Sql user
- and create the necessary app code / SSIS packages needed with instructions
from us - which we deploy for them.

We find BCP is too limiting in terms of the types of data we need to export
and formatting required. Also, once in a while we have a stored proc with a
PIVOT or something which requires dynamic columns.

Any suggestions or references would be greatly appreciated. Thanks and have
a super day!

Cheers,
Rich

On May 29, 11:56 am, Rich <R@discussions.microsoft.com> wrote:

> Good day everyone,

> I have a question regarding the best way to export bulk data from Sql tables
> or stored procedures into flat files. This question stems from our
> infrastructure which I think is quite typical:

> 1. Database server (Sql 2005) has only SS, SSAS and SSIS installed and is
> behind a firewall which currently only allows 1433 and 1434 traffic. Many
> various databases are hosted here.

> 2. Front-end web application server (IIS 6, .NET 3) hosts many various web
> applications. SSRS is installed here as well for Reporting.

Installing SSIS here (on the web server) would be the best solution.
It could load packages stored on the database server,  but execute
them on the web server, passing SQL commands and results over 1433 as
usual.

I am not totally familiar with the licensing or the file requirements
for installing SSIS -- I am still using SQL2000/DTS -- but with DTS,
the package can either be executed from a command shell using DTSRUN,
or instantiated as a COM object, giving you some integration
flexibility.

-----------------------------------------------Reply-----------------------------------------------

On May 29, 1:07 pm, rpresser <rpres@gmail.com> wrote:

Looks like I'm way off base -- SSIS is no longer considered a client
tool, but requires a SQL Server license. The MS giveth and the MS
taketh away.

Well .. here are a few more kludgy ideas.

1) You could use SSIS on the server, and upload to the web server
using an HTTP upload solution. You mentioned .NET so I can assume you
are using ASP.NET on your web server -- the GPL package NeatUpload
provides a robust way to accept large HTTP uploads, and can be used
with a commandline upload tool like curl on the SQL server machine to
perform the transfer.

The advantage of the HTTP transfer is that it would probably eliminate
the need for virtual directories, specialized users, etc.

2) You could use bcp on the web server, which still *is* a client
tool, and it could connect to the SQL server on port 1433.

3) You could get hold of the DTS component from SQL 2000, and use that
as a client tool on the web server to do execute packages stored on
the SQL server. But DTS is deprecated and perhaps no longer well
supported.

4) You did mention the possibility of a custom .NET solution.  You
could roll your own sort-of-DTS package.  Your clients could provide
code, either as a DLL or just plain C#, that your tool could load as a
dynamic assembly and execute.

-----------------------------------------------Reply-----------------------------------------------

Thanks alot!

I think we'll look at a hybrid of SSIS on the DB server and custom ASP.NET
solution on the web server for now... its not ideal but it'll work!

Have a great day!

Cheers,
Rich

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc