Need a help, I have a business requirement to genrate a CSV file with following name INDUS_CUST_RM_UPLOAD_20120416_163238.CSV where 20120416 is date in yyyymmdd format and 163238 is time stamp in hhmmss format.
Now the challange is not in generating this file which I am managing quite easily using a BCP command as mentioned below
DECLARE @FileName varchar(100),
SET @FileName ='D:\TalismaImport\Live\WMSRelatedExport\INDUS_CUST_RM_UPLOAD_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','_')+'.CSV'
SET @bcpCommand = 'bcp "select CustID,ECN from tlstagingdb.dbo.TempGK_C1FlagRMreport" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -t ","'
EXEC master..xp_cmdshell @bcpCommand
Now the challange comes in I have to FTP this file to a AIX box.
I am open to using any technology be it ssis or simple windows ftp. Folder containing this file also contains backdataed files.
I tried giving varaible in ssis file connection manager to ftp the file using ftp manager but was unsucessful.
Gave a thought to this and a solution what i have in mind is to insert file name and location in a table while genrating the file (BCP) and then ssis should query that table pickup the file name and path and ftp it to the required location.
Any ideas are welcomed here.