Batch file to copy file from ftp location to your local folder

//Save this file  with .bat extension

@Echo Off
Set _FTPServerName=ftpservername
Set _UserName=username
Set _Password=password
Set _LocalFolder=C:\sample\demo  \\ path of localfolder
Set _RemoteFolder=In/demo1 \\ path of server folder
Set _Filename=*.gz
Set _ScriptFile=ftp
:: Create script
 >"%_ScriptFile%" Echo open %_FTPServerName%
>>"%_ScriptFile%" Echo %_UserName%
>>"%_ScriptFile%" Echo %_Password%
>>"%_ScriptFile%" Echo lcd %_LocalFolder%
>>"%_ScriptFile%" Echo cd %_RemoteFolder%
>>"%_ScriptFile%" Echo binary
>>"%_ScriptFile%" Echo prompt n
>>"%_ScriptFile%" Echo mget %_Filename%
>>"%_ScriptFile%" Echo bye
:: Run script
ftp -s:"%_ScriptFile%"

Expression used to set the date format like ‘ddmmyy-hhmm’ in SSIS

RIGHT("0" +(DT_STR,4,1252)DATEPART( "yy" , getdate() ),2) +

RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)+"_"+Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2)

+ Right("0" + (DT_STR,2,1252) DatePart("mi",getdate()),2)

Dynamically insert row into column

--I have referred AdventureWorks database to demonstrate this example

Suppose I have created a Demo1 table

Create
Table Demo1 (Address1 Varchar(50),Address2 Varchar(50),Address3 Varchar(50),Address4 Varchar(50),Address5 Varchar(50),Address6 Varchar(50),
Address7 Varchar(50),Address8 Varchar(50),Address9 Varchar(50),Address10 Varchar(50))\

 
Select * from Person.Address where AddressID<=10


--Then I want to refer AddressLine1 of Person.Contact table and insert this values into a single row dynamically


DECLARE
@TBLREPORT TABLE (ID INT IDENTITY(1,1),AddressID int,AddressLine1 NVARCHAR(MAX))

DECLARE
@SQL NVARCHAR(MAX)

DECLARE
@ROW_1 NVARCHAR(MAX)

SET
@ROW_1=''

Insert
Into @TBLREPORT

Select
AddressID,AddressLine1 from [AdventureWorks].[Person].[Address] where AddressID between '100' and '109'

SELECT
@ROW_1=@ROW_1+''''+AddressLine1+''' AS [Address_'+CAST(ID AS VARCHAR(2))+'],'FROM @TBLREPORT ORDER BY ID

SET
@SQL=''

SET
@SQL= 'INSERT INTO Demo1 SELECT '+ SUBSTRING(@ROW_1,1,LEN(@ROW_1)-1)

EXEC
(@SQL)

Select
* from Demo1

--Now you check  the output

Create Dynamic Table

DECLARE @TBLREPORT TABLE (ID INT IDENTITY(1,1),FirstName NVARCHAR(MAX),LastName NVARCHAR(MAX),EmailAddress NVARCHAR(MAX))

---- TBLREPORT has no physical existence in Database
Insert Into @TBLREPORTSelect FirstName,LastName,EmailAddress from [AdventureWorks].[Person].[Contact] where Title='Mr.'
Select * from @TBLREPORT