To Find the SQL SERVER DATABASE SIZES AND LOCATIONS

select d.name,round(sum(mf.size) * 8 /1024,0) from sys.master_files mf
inner join sys.databases d
on d.database_id = mf.database_id
where d.database_id > 4
group by d.name
order by d.name


SELECT name, physical_name AS current_file_location
FROM sys.master_files

Transfer Multiple Files from or to FTP remote path to local path - SSIS

This is a common mistake which SSIS developer's thinks that they need Foreach Loop for transfer multiple files from ftp to local or from local to ftp.
I had a post about using masks in FTP task for transfer multiple files, but it seems that I should clarify this more than before.

Note that you don't need any Loop structure for transfer multiple files from remote path to local or from local path to remote. you just need to use MASK.
I explain in details with this scenario:
you have a remote ftp directory which you want to transfer all files in this folder to a local folder ( from FTP address to local) . and then copy all files from this local folder to another remote path ( from local to FTP address ).

Solution:

you have two steps:
1- receive files from remote ftp address:
add a FTP task, set ftp connection, then go to file transfer tab,
set Operation as "Receive files"
then set localPath to an existing folder as you want, for example:
D:\MyTempFolder
then in RemotePath set address to remote directory as you want, BUT NOTE THAT YOU SHOULD ADD MASK AFTER IT.
for example like this:
/myftpurl.com/wwwroot/myRemoteFolder/*.*
note that "*.*" means that you want to receive all files from this remote directory.


mid step: create new variable of string datatype in package scope, let's name it as LocalPath
and set it with your local address but with MASK, for example in our case it will be:
D:\MyTempFolder\*.*


2- send files from local directory to remote ftp address:
add another ftp task, set ftp connection, then in file transfer tab,
set Operation as "Send files"
set IsLocalPathVariable as True
then set LocalVariable with User::LocalPath
( the reason of using variable as localpath is that you can not set localpath directly with MASK, SSIS will raise compilation error. but if you put a variable there and set mask in variable value, SSIS will try to solve it at run time and everything will work fine)
then set remotePath to the remote folder which is your destination, you don't need to add mask here, for example it can be:
/mysecondftpurl.com/wwwroot/DestinationFolder

That's all. execute it now.