Solved

ssis - create files and move it to another server?

Posted on 2013-05-14
4
417 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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.
My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 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