Category Archives: SSIS

SSIS – How to create a new line character (CRLF) column as Derived Column

Use the following expression in order to create a derived column (DT_STR,2,1252)"\r\n" By default Data Type of string “\r\n” is DT_WSTR so here I am casting the data type to DT_STR Vishal Monpara Vishal Monpara is a full stack Solution Developer/Architect with 12 years of experience primarily using Microsoft stack. He is currently working in… Read More »

Execute Package Task fails even though the package executed successfully

We have parent and child SSIS packages. Both the packages are using environment variable configuration to get the confuguration database and sql configuration to get the further configuration property. Both the packages have sql authentication and package protection level is “EncryptSensitiveWithUserKey”. Both the packages run as expected in development environment but when moved to QA,… Read More »

Improve Attunity driver performance when extracting millions of rows from Oracle

Attunity driver for extracting data from Oracle is faster but if you are extracting millions of rows from Oracle and you think it is slower than anticipated, make sure you change the following advanced settings Right click on Oracle Data Source and click on “Show Advanced Editor…”. Go to “Component Properties” tab and under “Custom… Read More »

Stop DTS Package Execution Conditionally Without Failure

There is no straight way in DTS to stop the package on a certain condition without having it fail. Suppose in DTS package is suppose to be executed everyday at 8:00AM. This package is looking for a file c:\abc.txt. If the file has not arrived, it should gracefully stop without reporting any error. If the… Read More »

DTS SQL Statement / Stored procedure / Function to check for existence of file.

CREATE FUNCTION [dbo].[fn_FileExists] (@FileName nvarchar(255)) RETURNS bit AS BEGIN DECLARE @FS int DECLARE @FileID int DECLARE @OLEResult int DECLARE@Exists bit EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT IF @OLEResult <> 0 BEGIN RETURN 0 END EXECUTE @OLEResult = sp_OAMethod @FS, 'FileExists', @Exists OUT, @FileName IF @OLEResult <> 0 BEGIN return 0 END EXECUTE @OLEResult =… Read More »