Feeds:
Posts
Comments

Archive for the ‘SSIS’ Category

This is an ongoing post that will be appended with new additions.

In a SQL Task the “Parameter Mapping” and the SQL executed must be in SYNC else SSIS cannot complete the task and it stays Waiting (yellow colour) and will NOT throw an error. For example if you are passing a parameter the query needs to have a a question mark.

 

Read Full Post »


--SSIS > Script Task > Message Box
------------------------------------
            string strMessage = Dts.Variables["User::strAlterCmd"].Value.ToString();
            MessageBox.Show(strMessage);

--ALTER TABLE CHECK
-----------------------

IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID

('dbo.usama_mdlDataLake_Behaviour')
				AND name in ('carBreakdown'))		-- UA:You will either 

have all or none so just check any one column

--Check if Index exists / Create
-------------------------------
CONSTRAINT PK_tbl_ComplaintExtracts_LoadID PRIMARY KEY CLUSTERED (LoadID)

IF NOT EXISTS (SELECT top 1 *  FROM sys.indexes  WHERE name = 'ix_churn1')

--TempDB:
	IF NOT EXISTS (SELECT top 1 * FROM tempdb.sys.indexes  WHERE name = 'ix_ChurnIndex1') 

create unique clustered index ix_ChurnIndex1 on #t1(customerid)

CREATE NONCLUSTERED INDEX [IX_tbl_BGL_Transactions_FileName] ON [dbo].[tbl_BGL_Transactions] 

([PolicyNumber])

 IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='Index_Name'
    AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
DROP INDEX ix_tblSVV_Permissions_Orchid_CustomerID ON [dbo].[tblSVV_Permissions]

--Table Exists
--------------
IF NOT EXISTS (SELECT top 10 * FROM INFORMATION_SCHEMA.TABLES
   	WHERE TABLE_NAME = 

'DataLoad' -- do not include square brackets
			AND TABLE_SCHEMA = 'stg')
BEGIN       --
  PRINT 'Table Exists'
END

--VIEW Exists
------------------
IF EXISTS(select top 1 * FROM sys.views where name = 'myV')
DROP VIEW myV

--Drop all temp tables
--------------------------

DECLARE @DropTableSQL varchar(max)
SELECT @DropTableSQL = ISNULL(@DropTableSQL + ';', '') + 'drop table ' + SUBSTRING(t.name, 1, 

CHARINDEX('___', t.name) - 1)
FROM tempdb..sysobjects AS t
WHERE t.name LIKE '#%[_][_][_]%'
	AND t.id = OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1));
PRINT @DropTableSQL
--EXEC (@DropTableSQL)


--Try catch block
----------------------
BEGIN TRY
DECLARE @MyInt INT, @flag bit = 1  

-- the error state is custom number given so that a developer can locate where the error occured
--http://dba.stackexchange.com/questions/35893/what-is-error-state-in-sql-server-and-how-it-can-be-used

	if @flag = 0
		RAISERROR ('my custom error message', 16, 1); -- the error state a number 
	else 
		SET @MyInt = 1/0;
END TRY
BEGIN CATCH
SELECT @flag flag
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH;
GO 


Read Full Post »

My recommendations are:
SQL Server – Save here if you want to use SQL server Athentication (e.g. when scheduling a SSIS package)

SSIS Package Store – Save here if you just want Windows authentication (e.g. to run the package in another SSIS package). Please also note that the file here are just references to the location of where they reside in HDD.

Read Full Post »

SSIS package can be run throguh SSMS jobs automatically only if

  1. it is created in the same machine
  2. the package accespts user key (user key is the saved profile for the user which created it) – (I don’t know how to do this from a different machine)

then either saving it to SQL server or File system of the SSIS server would allow the job to refer it.

When executing a SSIS “execute package task” from differenct server (not local) – the package can be saved to accept password but this CANNOT be run

automatically as there will be prompt asking for the password.

Read Full Post »

Error: Communication link failure when connecting to remote database

Suggestion: Try using:

– SQLOLEDB.1 (Microsoft OLE DB Provider for SQL Server)

rather than

– SQLNCLI.1 (Native OLE DB\SQL Server Native Client 10.0)

Read Full Post »