Sql Server Programming :: Sql to file bulk export best practice?


	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: - Hide quoted text - > 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. 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 - Hide quoted text - "rpresser" wrote: > On May 29, 1:07 pm, rpresser <rpres@gmail.com> wrote: > > 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. > 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.