Hi,
I need to insert a separator after each value but I don't want to mess the db with irrelevant characters. The best separator would be a semicolon.
I got 5 columns in my db and export them via SQL Server Agent further to my FTP Server. The easiest way would be through the Server Agent job but I don't get it...
Anybody got an idea or should I insert an extra column which only contains the separator between the columns?
Thank you
March 27, 2023 at 2:51 pm
SELECT CONCAT(Col1,';',Col2,';',etc etc)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
SELECT CONCAT(Col1,';',Col2,';',etc etc)
Better yet:
SELECT CONCAT_WS(';', col1, col2, col3, ...)With that said - since this data needs to be uploaded to an FTP site, I would build an SSIS project that outputs the data into a local file with appropriate delimiters and then FTP the file from the package. If the FTP server is actually sFTP - then download WinSCP and implement their .NET code in a script task.
If SSIS is not an option then I would use Powershell 7 and Export-CSV (you can define the delimiter to other than a comma) to create the file and then use WinSCP .NET provider to upload. Someone has also built a wrapper to WinSCP for Powershell - not sure how well that works but it is something to consider.
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
March 28, 2023 at 8:25 am
Thanks Jeffrey, I'd forgotten that CONCAT_WS() even existed.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply