Category Archives: SSIS

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 »

How to split single record into multiple records using DTS?

There are many times a situation arises when we have to upload data from excel datasheet to SQL Server and the data in the sheet is not normalized. At that time, we need to make more than one record from a single record. Suppose you have a sheet in which each row in spreasheet shows… Read More »

Why do we need DTS (Data Transformation Service?)

Lets say you have data in one format(It can be a database/file/excel spreadsheet also). You want to convert it into another format. You will have a thought of writing a VB program and convert the source file into the format of destination file. But wait a minute. If you have lots of files in the… Read More »