April 16, 2012 at 5:17 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 17, 2012 at 11:32 am
If you're open to using SSIS to FTP the file then are you open to using SSIS to also get the data into the file in the first place? This would remove a use of xp_cmdshell from your environment, a plus in my book. Also, moving the data extract into SSIS will also centralize all code for your process in one place, which is a benefit in my book when troubleshooting and debugging.
If you're happy with xp_cmdshell and bcp then consider using xp_cmdshell and the Windows FTP client to upload the file to gain the same code centralization benefit you could have moving everything into SSIS.
For SSIS, these two videos will give you the basics you need to program the FTP Task so it is dynamic. If you move the extract into SSIS as well you'd be able to use the same variable in the file-extract and FTP steps, an added efficiency:
SQL Share > SSIS Expression Language Basics
SQL Share > Using Variables in a SSIS Package
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 17, 2012 at 11:54 am
I'll second the motion for moving the whole process into SSIS.
I have a package that outputs three files every night, dynamically names them, encrypts them using GnuPG, and then FTPs them to a third-party server. Does the whole thing in one place, works beautifully, and is easy to debug/refactor as needed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2012 at 12:08 pm
Make that a third for doing it all in SSIS. We built several processes for exporting and transfering data to third party vendors using SSIS at a previous employer. They work well and have very few, if any, problems.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply