February 1, 2022 at 6:03 pm
Hi, all - I'm trying to automate a script to run a query against a local server group and output the results to a .csv file and then push that .csv over a sftp connection. I've looked in a lot of places and I'm finding varying suggestions to no avail. Anyone have any ideas or similar experience they can share?
February 1, 2022 at 7:09 pm
Hi, all - I'm trying to automate a script to run a query against a local server group and output the results to a .csv file and then push that .csv over a sftp connection. I've looked in a lot of places and I'm finding varying suggestions to no avail. Anyone have any ideas or similar experience they can share?
Hello,
What kind of script are you trying to automate (t-sql/ powershell/ shell/batch script)?
To ensure that someone is not giving you the same ideas that you previously did not like, can you please provide more details on what all you have already checked and did not like?
Regards.
February 1, 2022 at 7:10 pm
I’d be looking at PowerShell for that sort of thing.
Couple of modules to look at DBATools primarily for the SQL pieces, connecting running queries etc.
Then PoshSSH for the SFTP stuff.
You can command line to WinSCP if you want as an alternative.
https://adamtheautomator.com/powershell-sftp/
https://docs.dbatools.io/Get-DbaRegServer
https://docs.dbatools.io/Invoke-DbaQuery
Get-DbaRegServer -SqlInstance [SERVERNAME] -Group [GROUPNAME] | Invoke-DbaQuery -Query 'SELECT foo FROM bar' | Export-Csv -Path c:\temp\output.csv -append
Set-SFTPItem -SessionId $SFTPSession.SessionId -Path C:\Temp\output.csv -Destination /tmp
There’s more obviously to the SFTP command to ensure the session is open and connected first, but a few lines of code and the task is complete.
February 1, 2022 at 8:51 pm
I would do this in SSIS - but then again, I have already done this in SSIS so I already have a template available to build a new project. It is the initial development that takes time, but once built - it can be as little as a few minutes to create/test and validate a new SFTP process.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 1, 2022 at 8:59 pm
Thanks, indefinitely thought of SSIS but, dating myself for sure, I could have easily done this in DTS but my SSIS skills are pretty much non-existent. Willing and need to learn SSIS though so might give it a shot.
February 1, 2022 at 9:01 pm
So, I guess the short answer is whatever will work best and get the job done. I’m open to whatever and any examples would be greatly appreciated.
February 2, 2022 at 6:21 am
It will all depend on how YOU want to support the solution. What are you comfortable in? PowerShell? SSIS? T-SQL? C#? VB.NET? Python?
From a PowerShell perspective, I have already provided a solution which will work for you. You just need to adapt it to query your CMS to get the group of servers, the query you wish to run, and then configure the SFTP part.
For SSIS, you’re going to need some form of loop to connect to each SQL server then to again break out to a command task to SFTP as this isn’t native to SSIS, these two links will give examples on how to do both these pieces.
https://www.sanssql.com/2011/08/looping-through-sql-servers-using-ssis.html?m=1
https://winscp.net/eng/docs/guide_ssis
February 2, 2022 at 4:53 pm
For SSIS - you don't need an execute process tasks if you use WinSCP. There is a .NET component available that can be used in a script task and a good example of how to implement that in SSIS on their website.
For any solution, you need to loop over the list of servers, extract the data and upload the file to the SFTP site. One way to define that list is to use a Central Management Server. Create a CMS and define a folder/group - you can then query the CMS to get the list of servers defined in the group - or for PS you can navigate to the CMS and use standard PS commands (Set-Location, Get-ChildItem, etc.).
Many ways to implement the solution...you could do it all in SQL Server and use xp_cmdshell to call out to any command-line SFTP client. That client will need to be installed or available to the server (some can be installed as a portable installation which could reside on a network share) - or installed/available from the system where you run your solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply