OLEDB Connection İçinde Stored Procedure Kullanmak

6. December 2010

Veritabanı ile kısa süre bile olsa ilgilenmiş bir kişi kesinlikle saklı yordam (stored procedure) kullanmıştır. Saklı yordamlar ile çoğu programın satırlar boyunca yapamadığını kısa sürede yapmışızdır. Üstelik parametrik olarak çalışabildiğinden çoğu zaman veri setlerini geri döndürmek için de kullanmışızdır. Ancak!

Stored Procedure'lar parametrize edilmiş View'lar değildirler. Öyle de kullanılmamalılardır/düşünülmemelilerdir. Prosedür/yordam, adı üzerinde, belirli bir kavramsal komut listesini işlemekle görevlidir. Size veri göndermek zorunda değildirler çünkü veri döndürmek onların asli görevi değildir. Bu nedenle de METADATA barındırmazlar. Bu bağlamda düşündüğümüzde yine de stored procedure ile veri alıp dataflow içerisinde kullananarak üretilen veri setini kullanmak isteyebilirsiniz veya zorunda kalabilirsiniz.

Bugün stored proc kullanırken dikkat edilmesi gereken bazı hususları da anlatmaya çalışacağım.

Dilerseniz hızlıca başlayalım.

Hemen aşağıdaki kodu çalıştırarak bir stored proc yaratalım.

CREATE PROCEDURE spOLEDBSourceSELECT

AS

BEGIN

SET NOCOUNT ON;

 

SELECT * FROM Tablo

GO

 

Bu yarattığımız prosedürün ürettiği verileri dataflowda kullanacağız şimdi.

 

Aşağıda bulunan Preview düğmesine bastığımızda bize select * from Tablo sorgusunun sonucunu gösterecektir.

Buraya kadar herhangi bir sorun yok ancak eğer ki sp içerisinde kullandığımız sorgu parametrik olarak değişiyorsa o zaman problemler ortaya çıkar.

Şimdi önce yarattığımız sp'yi biraz değiştirelim ve bir adet rastgele koşul ekleyelim.

CREATE PROCEDURE spOLEDBSourceSELECTWITHCONDITION

AS

BEGIN

SET NOCOUNT ON;

 

IF YEAR(GETDATE())<2010

BEGIN

    SELECT * FROM dbo.FACT_TABLE

END

ELSE

    SELECT * FROM Tablo

END

Bu durumda sp'nin çalışması Runtime'da değişik sonuçlar üretecektir. Preview'a bastığımızda aşağıdaki gibi bir sonuç dönecek.

 

Ancak metadata kolonlarına bakmak istediğimizde tamamiyle farklı bir sonuç göreceğiz.

Buradan çıkartacağımız sonuç: Columns bilgisi Sp içerisindeki ilk sorguya göre şekillenirken çalışma zamanında farklı metadataya sahip olacaktır.

Paketi çalıştırdığımızda alacağımız güzide hata mesajı:

Error: 0xC0202009 at Data Flow Task 1, OLE DB Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E55.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E55 Description: "Column does not exist.".

Error: 0xC0047038 at Data Flow Task 1, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

 

Benzer bir şekilde sp kullanırken karşılaşabileceğimiz önemli bir diğer sorun ise temp tablo kullandığımızda karşımıza çıkacaktır.

Aşağıdaki sp'yi düşünelim:

CREATE PROCEDURE spOLEDBSourceTMP

AS

BEGIN

SET NOCOUNT ON;

    

CREATE TABLE #tmp (Id int NULL,Name nvarchar(50) NULL)

 

 

INSERT INTO #tmp VALUES(1,'a')

INSERT INTO #tmp VALUES(2,'b')

INSERT INTO #tmp VALUES(3,'c')

INSERT INTO #tmp VALUES(4,'d')

INSERT INTO #tmp VALUES(5,'e')

INSERT INTO #tmp VALUES(6,'f')

INSERT INTO #tmp VALUES(7,'g')

 

 

    SELECT * FROM #tmp

END

GO

Şimdi gidip OLEDB Source içerisinde çağırıp preview diyelim bakalım ne kadar güzel olacak hata mesajımız:

Error at Data Flow Task 1 [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid object name '#tmp'.".

Error at Data Flow Task 1 [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

 

Gördüğümüz gibi ssis #tmp tablosunu bulamadığından yakınıyor. Haklı da zaten, bulmak zorunda değil çünkü onun lifetime'ı içerisinde oluşan bir şey değil.

Peki illa temp tablo kullanmamız gerekli o zaman nasıl yapıyoruz? O zaman da TABLE tipi değişken yaratıyoruz. Hemen aşağıda:

CREATE PROCEDURE spOLEDBSourceTABLEVAR

AS

BEGIN

SET NOCOUNT ON;

    

DECLARE @Tablo TABLE(Id int NULL,Name nvarchar(50) NULL)

 

 

INSERT INTO @Tablo VALUES(1,'a')

INSERT INTO @Tablo VALUES(2,'b')

INSERT INTO @Tablo VALUES(3,'c')

INSERT INTO @Tablo VALUES(4,'d')

INSERT INTO @Tablo VALUES(5,'e')

INSERT INTO @Tablo VALUES(6,'f')

INSERT INTO @Tablo VALUES(7,'g')

 

 

    SELECT * FROM @Tablo

    

END

GO

 

Son olarak, dilerseniz SP'lerimize parametre nasıl göndereceğimizi de görelim. Her ne kadar bunla ilgili bir sürü yöntem olsa da ben genelde ssis variable kullanmayı ve komutu yazdırmayı tercih ediyorum.

Aşağıdaki SP'yi kullanacağız:

CREATE PROCEDURE spOLEDBSourceTABLEVARWITHPARAMS

@KacKere int

AS

BEGIN

SET NOCOUNT ON;

    

DECLARE @Tablo TABLE(Id int NULL,Name nvarchar(50) NULL)

DECLARE @a int

SET @a=1

 

WHILE @a<=@KacKere

BEGIN

    INSERT INTO @Tablo VALUES(@a,'test')

    SET @a=@a+1

END

 

    SELECT * FROM @Tablo

    

END

GO

 

Bu Sp, @Kackere isimli parametreyi alıyor ve o kadar kez tablomuza kayıt ekliyor. Biz @kackere için bugünün gün değerini kullanacağız. (bugün kadar dönsün yani)

 

Bir adet string tii değişken yaratıyoruz. Bu paket scope'unda bir değişken olmalıdır.

Yarattığımız değişkenin özelliklerine gidiyoruz ve "Evaluate as Expression" = true yaptıktan sonra Expression' yazma ekranını açtırıyoruz:

 

"EXEC spOLEDBSourceTABLEVARWITHPARAMS "+ (DT_WSTR, 2) DAY( GETDATE())

Ardından OLEDB source'a dönüp bu değişkeni sql sorgusu olarak kullan diyeceğiz:

Keza çalıştığını görünce de seviniyoruz:

 

Meraklısına bu konudaki bazı kaynaklar (ingilizce)

Dataflow Components, Sources,Destinations ,

Add comment




biuquote
  • Comment
  • Preview
Loading