Solved

ssis - create files and move it to another server?

Posted on 2013-05-14
4
411 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 40

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

713 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