Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

ssis - create files and move it to another server?

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
25112
Asked:
25112
3 Solutions
 
TempDBACommented:
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
 
lcohanDatabase AnalystCommented:
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
 
Alpesh PatelAssistant ConsultantCommented:
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
 
25112Author Commented:
thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now