Thursday, November 22, 2012

Return a NULL DT_STR in a conditional statement

Return a NULL DT_STR in a conditional statement

I have a requirement to convert all blank strings in a data flow into NULLs.  Here is the expression I tried first:
TRIM( [Some Column] ) == “” ? NULL( DT_STR, [length], 1252) : [Some Column]
This threw an error:
Error at Master DataFlow [Derived Column Task]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations.  The expression “TRIM( [Some Column] ) == “” ? NULL( DT_STR, [length], 1252) : [Some Column]” has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation.  To perform this operation, the operand needs to be explicitily cast with a cast operator.
After much messing around, I foud that if I cast the NULL statement as a DT_STR, the expression compiler was happy:
TRIM( [Some Column] ) == “” ? (DT_STR, 8, 1252)NULL(DT_STR, [length], 1252) : [Some Column]
Is this an expected behavior of Integraion Services?
I guess I would have expected that since I specified the type in the NULL statement that I would have to cast it as a DT_STR afterwords.
Any thoughts?

Tuesday, June 05, 2012

Simple delete files for X number of days.

FORFILES /p ".\Directory" /s /m *.* /d -5 /c "CMD /C del /Q @PATH"

Wednesday, April 11, 2012

The Setup program prompts you for the Office 2000 CD-ROM when you upgrade to Office XP

When you are prompted to insert the Office 2000 CD-ROM, type the following in the Use feature from box, and then click OK:
msiexec /i {Office 2000 product code} REMOVE=NotInstalled REINSTALLMODE=m /qb
To locate your Office 2000 product code, follow these steps:
  1. Click Start, and then click Run.
  2. In the Open box, type regedit, and then click OK.
  3. Locate, and then select the following registry key:
  4. With the
    registry key selected, click Find on the Edit menu.
  5. In the Find what box, type Microsoft Office 2000, and then click Find Next.
  6. DisplayName will be selected in the right-side of Registry Editor.
On the left-side of Registry Editor, note the folder that is open, and then write down the folder name. For example, the folder name should be a long series of letters and numbers that is similar to the following format:

Thursday, March 01, 2012

The secret core FTP command line switches cheat sheet

 I  am always looking for the below.

Command Line
Download a file via a FTP URL (see FTP URL's for more information).

From the command line, type:

drive:\(coreftp path)\coreftp.exe -d


drive:\(coreftp path)\coreftp.exe

This will download the file into the default download directory specified in options, or to the last local directory that was used while in the program.

If you don't want the GUI to pop up, add the -s (silent) option to your command line parameters.

To use a different destination directory, include: -p drive:\directory

drive:\(coreftp path)\coreftp.exe -d -p drive:\path\

Wildcard support was added as of build 1250.

drive:\(coreftp path)\coreftp.exe -d*.jpg -p drive:\path\

To rename the destination filename, use -FN

Upload a file:

From the command line, type:

drive:\(coreftp path)\coreftp.exe -u c:\path\yourfileorpath\filename

drive:\(coreftp path)\coreftp.exe -u c:\path\yourfileorpath\*.jpg

This will upload the file or path specified into the directory specified by the URL. Make sure the URL has all appropriate userid/password/permissions for the remote FTP server.

To use a different desination directory, use the -p /directory/: (example with site profile).

drive:\(coreftp path)\coreftp.exe -site mysite -u c:\path\yourfile\file.dat -p /remotedir/

Blocking Transfers:

By default Core FTP command line works asynchronously.

To block transfers during processing, use the 'corecmd.exe' utility with the normal command line parameters.

SSL Options:

-ssl-- AUTH SSL

-tls-- AUTH TLS

-ssldirect-- Direct SSL (implicit)

-winssl-- Use Windows security layer (OpenSSL by default)

SSH/SFTP Options:

-SSH or sftp:// in the URL...

Log screen output to a file:


Log directory information to a file:


Process a template:

drive:\(coreftp path\coreftp.exe -t templatename

Add -refresh to reload template file sizes.

Transferring using a site profile:

If you need to use special settings like SSL or SSH, create a site profile in the Site Manager and then use the following command line parameter.

Use: -site profilename


Download: drive:\(coreftp path)\coreftp.exe -site mysite -d /directory/yourfile.dat -p c:\download\

Upload: drive:\(coreftp path)\coreftp.exe -site mysite -u c:\myfile.dat -p /public_htm/mydirectory/

Overwrite and resume options:

Use:-O-- Overwrite if file exists

-OA-- Overwrite *all* if transfering a folder or directory of files.

-ON-- Overwrite files that are newer than existing files.

-OG-- Overwrite destination file if source file size is greater.

-OL-- Overwrite destination file if source file size is less (smaller).

-OS-- Skip file if exists

-R -- Resume if file exists

-RA-- Resume *all* files that exist.

Transfer oldest/newest files

-newest -- Transfer the newest file in a directory.

-oldest -- Transfer the newest file in a directory.

ie: c:\(coreftp path)\corecmd.exe -s -oldest -site mysite -d /remote_path/*.txt -p c:\localdir\ To download the oldest file in a directory.

Compress and upload a file (Pro only):

drive:\(coreftp path)\coreftp.exe -O -ZIP -AUTONAME -u c:\IBM -site siteprofile -p /public_html/uploadpath/

-AUTONAME automatically names the file. Without this parameter the file will be named Use -FN to specify destination.

Local compressing(zip) and decompressing(unzip) of files (Pro only):


ie: coreftp.exe -ZIPONLY password c:\path\ c:\source\*.dat


ie: coreftp.exe -UNZIPONLY password c:\path\ c:\desination\

Override Ascii or Binary mode for transfers:

-A -- for ascii mode

-B-- for binary mode

By default, files are always transfered in binary mode unless specifed in the ascii extensions.

Silent Mode:

Use:-s -- Will transfer file with no GUI except for a transfer dialog and overwrite prompts. In the Pro version, there is no splash screen.

Delete Source File:

Use:-delsrc-- Delete the source file (upload or download) after successful transfer.

-del filespec-- Delete files. Command used alone (no transfers) with site profile or FTP url.

-purge filespec days-- Purge files of x days old. Common used alone with site profile or FTP url.

-rmdir -- To delete a directory

Rename Destination File:

Use:-FN newfilename-- use this to transfer a file to a different filename, used in conjunction with the -p parameter.

-- ie: coreftp.exe -u c:\myfile -p /path/ -FN newfilename

Miscellaneous Commands:

Use:-hide-- Hide the transfer window when not in silent mode.

-show-- Show the transfer window when not in silent mode.

-dir -- Dump directory information into a file. -dir

-dira -- Dump directory information into a file forcing ascii format.

-refresh-- Refresh file sizes in gui if size has changed.

-zip password-- Compress files into destination file with optional password.

-unzip password-- Decompress files into desination directory with optional password.

-ignorenotfound-- Returns no error if a file is not found.

-flash-- Run from an external drive.

-chmod -- Change permissions on a file.

-pasv-- Set transfer channel to passive mode.

-active-- Set transfer channel to active mode.

-nocase -- Ignore case

-ignorenotfound-- Ignores no files found problems on some servers

-recurse-- Recurse subdirectories.

-remotemove /oldpath/*.ext /newpath/ -- Move files remotely from one folder/directory to another

-exit-- Exit override

ie: "c:\program files\coreftp\coreftp.exe" -O -zip -u c:\IBM\*.dat -site siteprofile -p /remote_directory/ -fn

Script Options:

-scbl -- Run script from file before logon

-scal -- Run script from file after logon

-scbt -- Run script from file before transfer

-scat -- Run script from file after transfer

Install Options:

Use:-lic -- To install license file automatically

-Import -- To import a file of sites (previously exported from within the site manager)

-ImportGlobal -- To import a file of sites to be useable by all users (and not editable or deletable).

Output File:

The output file can be used for batch or external program monitoring of transfers.

Use-output \path\filename-- Will output a log file for each transfer.

Format: transfer type (1-download, 2-upload,...), transfer status(1-success, 2-failure, 30-file not found), start time, end time, dest, source.

Environment Variables:

In batch files, the environment variable will contain a transfer's return value in %ERRORLEVEL% (0 or 1, or 30 for file not found). This value is only reliable within a .bat file.