January 21, 2015 at 7:47 am
Hi All,
I have to download the files from SFTP server, for which i am using WINSCP and i am able to successfully automate the process to download the files. But it is downloading all the files instead of only current day's files. Ihave searched for WINSCP documentation for time query paramter to pass to the get command. But its not working.
My source file name contains Datefield as "Filenameexample_150120_N001.txt".
Here 150120 mean Jan 20 2015.
Winscp has no functionality to query the files to parse using datefield. Can any one help me on this how to look for files which are from today's date.
Currently i am downloading files which contain *.* (meaning all files).
Thanks in advance.
January 21, 2015 at 8:07 am
It has nothing to with T-SQL.
You need to build the file name you want using whatever script language you are using.
If for any razy reason you doing it from within SQLServer, T-SQL to format date would be something like:
SELECT 'Filenameexample_' + RIGHT(CONVERT(VARCHAR(10),GETDATE(),112),6) + '_N001.txt'
January 21, 2015 at 10:13 am
I installed Winscp and I am using a batch file using WINSCP commandlets as shown below
example:
"C:\Program Files (x86)\WinSCP\winscp.com" /command "open sftp://testsftp:testsftp@localhost" "lcd C:\Destination" "get *.* "
Is there any other way to achieve in SQL server 2008 R2?
January 21, 2015 at 10:34 am
muthyala_51 (1/21/2015)
I installed Winscp and I am using a batch file using WINSCP commandlets as shown belowexample:
"C:\Program Files (x86)\WinSCP\winscp.com" /command "open sftp://testsftp:testsftp@localhost" "lcd C:\Destination" "get *.* "
Is there any other way to achieve in SQL server 2008 R2?
Your problem has nothing to do with SQL Server and/or T-SQL.
You need to write your batch file so it dynamicaly builds execution command for winscp.com and instead of "get *.* " mask it needs to use the mask to contain the date you are after in the form you need it, for example:
"get *_15012_*.* "
that is sample how you can get the date format you want in the BAT file:
echo off
REM That is for locale which has default date format as DD/MM/YYYY
set today=%date%
echo %today%
set formattedday=%today:~8,2%%today:~3,2%%today:~0,2%
REM=%today:~0,4%-%today:~4,2%-%today:~6,5%
echo %formattedday%
pause
January 21, 2015 at 11:40 am
@Elutin - Thank you.
Can you also let me know without using WINSCP- SQL 2008 R2 using SSIS how can this be achieved as there is no SFTP Task available. If you have any thoughts that would help me a lot. Thanks again.
Eugene Elutin (1/21/2015)
muthyala_51 (1/21/2015)
I installed Winscp and I am using a batch file using WINSCP commandlets as shown belowexample:
"C:\Program Files (x86)\WinSCP\winscp.com" /command "open sftp://testsftp:testsftp@localhost" "lcd C:\Destination" "get *.* "
Is there any other way to achieve in SQL server 2008 R2?
Your problem has nothing to do with SQL Server and/or T-SQL.
You need to write your batch file so it dynamicaly builds execution command for winscp.com and instead of "get *.* " mask it needs to use the mask to contain the date you are after in the form you need it, for example:
"get *_15012_*.* "
that is sample how you can get the date format you want in the BAT file:
echo off
REM That is for locale which has default date format as DD/MM/YYYY
set today=%date%
echo %today%
set formattedday=%today:~8,2%%today:~3,2%%today:~0,2%
REM=%today:~0,4%-%today:~4,2%-%today:~6,5%
echo %formattedday%
pause
January 22, 2015 at 4:30 am
muthyala_51 (1/21/2015)
@Elutin - Thank you.Can you also let me know without using WINSCP- SQL 2008 R2 using SSIS how can this be achieved as there is no SFTP Task available. If you have any thoughts that would help me a lot. Thanks again.
There is at least one 3-rd party SSIS component for SFTP.
https://ssissftp.codeplex.com/releases/view/78697
However, I never used it myself.
Usually I would design it as:
1. Get files from SFTP (you can use whatever you like)
2. BCP-in or BULK INSERT into SQL Server database (raw-data staging area)
3. Any other data transformations - whatever needed
You can control the above flow from SSIS if you want
January 22, 2015 at 5:45 am
muthyala_51 (1/21/2015)
@Elutin - Thank you.Can you also let me know without using WINSCP- SQL 2008 R2 using SSIS how can this be achieved as there is no SFTP Task available. If you have any thoughts that would help me a lot. Thanks again.
Eugene Elutin (1/21/2015)
muthyala_51 (1/21/2015)
I installed Winscp and I am using a batch file using WINSCP commandlets as shown belowexample:
"C:\Program Files (x86)\WinSCP\winscp.com" /command "open sftp://testsftp:testsftp@localhost" "lcd C:\Destination" "get *.* "
Is there any other way to achieve in SQL server 2008 R2?
Your problem has nothing to do with SQL Server and/or T-SQL.
You need to write your batch file so it dynamicaly builds execution command for winscp.com and instead of "get *.* " mask it needs to use the mask to contain the date you are after in the form you need it, for example:
"get *_15012_*.* "
that is sample how you can get the date format you want in the BAT file:
echo off
REM That is for locale which has default date format as DD/MM/YYYY
set today=%date%
echo %today%
set formattedday=%today:~8,2%%today:~3,2%%today:~0,2%
REM=%today:~0,4%-%today:~4,2%-%today:~6,5%
echo %formattedday%
pause
I would definitely do it like Eugene suggested with a batch file. If you don't know all the filenames you need to get ahead of time, then you have no choice but to to a get with wildcards or an mget.
I do something similar with FTP, but I do know the predetermined filename I have to get every day. I build a DOS command in SQL that writes out a file of FTP commands to disk and executes the FTP command that calls the FTP file. It works great and very reliably and it's all driven by SQL - I don't even need SSIS on the server.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply