April 16, 2012 at 5:19 am
Hi Folks,
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),
@bcpCommand varchar(2000)
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.
Regards,
Shrey Sheth
April 16, 2012 at 7:27 am
If you are doing this as a regular part of your production environment I would recommend using a dedicated service to handle FTP requests. The service actually doing the FTP should be as robust and flexible as possible: (i.e capable of multi-threading and with good error handling and retry capabilities). Trying to do this from within SQL server using xp_cmdshell (or even CLR) is not really a good idea for obvious reasons.
It is easy to build this yourself (in .NET), it can run against a database table as you have suggested, or it could use service broker message queues. Either way it is not rocket science, but there are probably turn-key commercial (or free-ware) products out there if you don't want to roll your own.
The probability of survival is inversely proportional to the angle of arrival.
April 16, 2012 at 9:22 pm
Hi sturner
Can you suggest any products. or any way to use this logic in ssis?:-)
April 17, 2012 at 6:38 am
You would have to do some Googling, I don't know any off the top of my head. An FTP (sending) service of which I was referring would be stand alone and have no requirement for SSIS. You would just queue up your requests to this service via a table or message queue insert. None of that requires SSIS or negates the use of SSIS.
The probability of survival is inversely proportional to the angle of arrival.
April 18, 2012 at 2:47 pm
For a simple process like this you can do everything in SSIS using a variable to build the file name and then use that variable in both your Data Flow that extracts the data to a file on disk, as well as in the FTP Task that will send the file to the AIX box. There are lots of examples and pointers on the web (and on this site ;-)) on how to do that.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply