Solved

ssis - create files and move it to another server?

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
In this article I will describe the Backup & Restore 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.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

628 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