Solved

ssis - create files and move it to another server?

Posted on 2013-05-14
4
408 Views
Last Modified: 2016-02-10
Using SSIS, can you run 2 queries against a database and create 2 csv files as the output of the SELECT statement from SQL Server A, and then move it to a folder in another server (ServerB)?

if yes, what specific tasks in ssis will help for each of the above?

right now  I am thinking of using net use   & COPY DOS commands to copy the files, but if SSIS makes it more efficient, i should consider it.

thanks.
0
Comment
Question by:25112
4 Comments
 
LVL 25

Accepted Solution

by:
TempDBA earned 167 total points
ID: 39167165
You can use the ones you stated. You can even do one more thing. Directly dump the files into ServerB's folder without creating one in ServerA. This will save both processing and IO.
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 167 total points
ID: 39168395
Not sure what you mean by "...if SSIS makes it more efficient..." however here are some facts to help you decide:

SSIS data export task will make it easier to code and run pretty fast but a xp_cmdshell (if you have it enabled) with BCP may be even faster than SSIS. You could pipe the results directly to a shared folder between ServerA and ServerB to avoid the copy (or much better robocopy) at Windows level command.

If servers are in different location then you could use SSIS Data Export task, put the file in a folder on that ServerA, then use a SSIS FTP task to send it to ServerB

If you have xp_cmdshell enabled on ServerA here's how you can do it from T-SQL code assuming the user that runs it has sufficient Windows folder/path permissions.

http://msdn.microsoft.com/en-us/library/ms162773.aspx

exec master..xp_cmdshell N'sqlcmd -E -Q"set nocount on;select * from TheArchive.archive.tt9446_cctype_fix" -h-1 -s"," -W -o"c:\outputTEST.csv"'--, no_output


--for TEXT fields use only BCP!!!
http://msdn.microsoft.com/en-us/library/aa174646(SQL.80).aspx

exec master..xp_cmdshell N'bcp "set nocount on; SELECT top 10 ClientID,MerchantID,Name,Company,Email,Address1,City,State,Country from mc..clients with (nolock);" queryout "c:\outputTEST.txt" -N -q -T >nul'--, no_output


You couls copy the exported file by a simple command like below executed on ServerA but again - the user running it MUST have supfficient permisions on the destination folder/path:


exec xp_cmdshell 'copy C:\outputTEST.csv \\ServerB\FolderName\outputTEST.csv' -- no output

You can run for instance all T-SQL in a SQL Scheduled job where you can select under the Advanced step properties Run As and selec a NT user with sufficient rights to do all these.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 166 total points
ID: 39171209
To do that take

Data flow 1

for CSV 1
OLEDB source
Flatfile destination (to create csv) -- Use Server B's UNC path for flatfile connection

Data flow 2
for CSV 2
OLEDB source
Flatfile destination (to create csv) -- Use Server B's UNC path for flatfile connection
0
 
LVL 5

Author Comment

by:25112
ID: 39205392
thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question