Değişiklik veri almak için işlevi oluşturma

Makale
10/29/2012

Denetim akış tamamladıktan sonra bir Integration Servicesartımlı bir yük değişiklik veri, sonraki görev yapan paketidir, değişiklik veri alan bir tablo değerli işlev oluşturmak için. Bir süre önce ilk artımlı yükü bu işlevi oluşturmak yeterlidir.

[!NOT]

Değiştirmek veri paket oluşturma sürecinde ikinci adım gerçekleştirir artımlı bir yük almak için işlevi oluşturma verileri değiştirin. Bu paket oluşturma işlemindeki açıklaması için bkz: (SSIS) verisini Değiştir yakalama.

Paketleri içinde değişiklik veri yakalama kullanımını gösteren eksiksiz, uçtan uca örnekler için bkz: Readme_Change Data Capture for Specified Interval Package Sampleve Readme_Change Data Capture since Last Request Package Sample.

Işlevler tasarımları Değiştir veri yakalama

Değişiklik veri almak için bir kaynak bileşen paketi veri akışı aşağıdaki değişiklik veri yakalama sorgu işlevlerinden birini çağırır:

değiştirir<capture_instance > Bu sorgu için tek bir satır döndürülen her güncelleştirme değişen her satırın son durumunu içeren için. Çoğu durumda, sadece net değişiklik için bir sorgu tarafından döndürülen verileri gerekir. Daha fazla bilgi için, bkz. değiştirir<capture_instance> (Transact-sql).
CDC.fn_cdc_get_all_changes_<capture_instance > Bu sorgu meydana gelen tüm değişiklikleri her satır döndürür yakalama aralığı sırasında. Daha fazla bilgi için, bkz. CDC.fn_cdc_get_all_changes_<capture_instance> (Transact-sql).

Kaynak bileşen sonra işlev tarafından döndürülen sonuçlarını alır ve akım dönüşümleri ve hedefleri, nihai hedefe Değiştir veri geçerli geçirir.

Ancak, bir Integration Serviceskaynak bileşeni bu değişiklik veri yakalama işlevleri doğrudan arama yapamazsınız. Bir Integration Serviceskaynak bileşeni gerektiren meta veri sütunları sorgunun döndürdüğü hakkında. Değişiklik veri yakalama işlevleri kendi çıkış tablosunun sütunlarını tanımlamak değil. Böylece, bu işlevler için yeterli meta verileri dönmek bir Integration Serviceskaynak bileşeni.

Bunun yerine bu tür bir işlevi kendi döndürür yan tümcesinde onun çıkış tablo sütunları açıkça tanımladığından bir sarmalayıcı tablo valued işlevini kullanın. Bu açık tanımı sütun meta verileri sağlayan bir Integration Serviceskaynak bileşen gereksinimlerini. Değişiklik veri almak istediğiniz her tablo için bu işlev oluşturmak zorunda.

Değişiklik veri yakalama sorgu işlevini çağırır sarmalayıcı tablo değerli işlev oluşturmak için iki seçeneğiniz vardır:

Sen-ebilmek seslenmek gösterirsys.sp_cdc_generate_wrapper_functiontüm sistem saklı yordamı, tablo değerli işlevler için oluşturmak için.
Bu konudaki kurallar ve örneği kullanarak kendi tablo değerli işlev yazabilirsiniz.

Tablo değerli işlev oluşturmak için saklı yordam çağırma

Aramak için gereken tablo değerli işlevler oluşturmak için hızlı ve kolay bir yol olduğunu sys.sp_cdc_generate_wrapper_function sistem saklı yordamını. Bu saklı yordam özel ihtiyaçlarını karşılamak üzere tasarlanmış sarıcı işlevleri oluşturmak üzere komut dosyaları üreten bir Integration Serviceskaynak bileşeni.

Önemli
Gösterirsys.sp_cdc_generate_wrapper_functiontüm sistem saklı yordamını doğrudan oluşturma sarıcı işlevleri. Bunun yerine, saklı yordam sarıcı işlevleri için oluşturma komut dosyaları oluşturur. Geliştirici, saklı yordam bir artımlı yük paketi olabilir önce oluşturduğu oluşturma komut dosyaları arama sarmalayıcı işlevleri çalıştırmanız gerekir.

Bu sistem saklı yordamını nasıl anlamak için yordamı, hangi komut yordamı oluşturur, ne anlamak ve komut dosyalarını hangi wrapper işlevler oluşturmak.

Anlama ve saklı yordamı kullanma

Gösterirsys.sp_cdc_generate_wrapper_functiontüm sistem saklı yordamı oluşturur sarıcı işlevleri tarafından kullanılmak üzere oluşturmak için komut dosyası Integration Servicespaketleri.

İşte ilk birkaç satırı saklı yordam tanımı:

CREATE PROCEDURE sys.sp_cdc_generate_wrapper_function

(

@capture\_instance sysname = null

@closed\_high\_end\_point bit = 1,

@column\_list = null,

@update\_flag\_list = null

)

Saklı yordam için tüm parametreler isteğe bağlıdır. Saklı yordam parametreler için değerler sağlayarak olmadan çağırırsanız, sarıcı işlevleri erişimi olan tüm yakalama örnekleri için saklı yordam oluşturur.

[!NOT]

Daha fazla bu sözdizimi hakkında bilgi için bkz: saklı yordam ve parametrelerinin, gösterirsys.sp_cdc_generate_wrapper_functiontüm (Transact-sql).

Saklı yordam, her zaman tüm değişiklikleri her yakalama örneği dönmek için sarmalayıcı işlevi oluşturur. Eğer @supports\_net\_changesparametresi ayarlanmış yakalama örneği oluşturulduğunda, saklı yordam ayrıca net değişiklikleri her uygulanabilir yakalama örneği dönmek için sarmalayıcı işlevi oluşturur.

Saklı yordam, bir sonuç kümesi ile iki sütun döndürür:

Saklı yordam oluşturduğu işlevi adı. Bu saklı yordam işlevi yakalama örnek adı adından türetilmiştir. (İşlev 'fn_all_changes_' adıdır yakalama örneği adı izler. Oluşturulursa, net değişiklikleri işlevi için kullanılan öneki 'fn_net_changes_' dir.)
create deyimi için sarmalayıcı işlevi.

Anlama ve saklı yordam tarafından oluşturulan komut dosyalarını kullanma

Genellikle, bir geliştirici bir ekleme kullanmak istiyorsunuz...exec deyimi aramak için gösterirsys.sp_cdc_generate_wrapper_functiontüm saklı yordamı ve geçici tabloya saklı yordamı oluşturur komut dosyalarını kaydedin. Her komut dosyası daha sonra olabilir tek tek seçilen ve ilgili işlevi oluşturmak için çalıştırırsınız. Ancak, bir geliştirici Ayrıca sql komutları bir dizi tüm oluşturma komut çalıştırmak için aşağıdaki örnek kodda gösterildiği gibi kullanabilirsiniz:

create table #wrapper_functions
      (function_name sysname, create_stmt nvarchar(max))
insert into #wrapper_functions
exec sys.sp_cdc_generate_wrapper_function

declare @stmt nvarchar(max)
declare #hfunctions cursor local fast_forward for 
      select create_stmt from #wrapper_functions
open #hfunctions
fetch #hfunctions into @stmt
while (@@fetch_status <> -1)
begin
      exec sp_executesql @stmt
      fetch #hfunctions into @stmt
end
close #hfunctions
deallocate #hfunctions

create table #wrapper_functions
      (function_name sysname, create_stmt nvarchar(max))
insert into #wrapper_functions
exec sys.sp_cdc_generate_wrapper_function

declare @stmt nvarchar(max)
declare #hfunctions cursor local fast_forward for 
      select create_stmt from #wrapper_functions
open #hfunctions
fetch #hfunctions into @stmt
while (@@fetch_status <> -1)
begin
      exec sp_executesql @stmt
      fetch #hfunctions into @stmt
end
close #hfunctions
deallocate #hfunctions

Anlama ve saklı yordam tarafından oluşturulan işlevleri kullanma

Sistematik olarak yakalanan Değiştir veri çizelgesi yürümek için oluşturulan sarıcı işlevleri bekliyoruz @end\_timeparametresi için bir Aralık-ecek var olmak @start\_timeparametresi sonraki aralığı için. Bu Kongre izlendiğinde, oluşturulan sarıcı işlevleri aşağıdaki görevleri yapabilirsiniz:

Tarih/Saat değerleri dahili olarak kullanılan lsn değerleri eşleyin.
Hiçbir veri kaybı veya tekrarlanan emin olun.

Basit bir değişiklik tablonun tüm satırlar için sorgulama yapmak için aşağıdaki kurallar da üretilen sarmalayıcı işlevleri destekler:

@ Start_time parametre null ise, sarıcı işlevleri sorgu alt sınır olarak yakalama örneği en düşük lsn değerini kullanın.
@ End_time parametre null ise, sarıcı işlevleri sorgu üst sınır yakalama örneği en yüksek lsn değeri kullanın.

Çoğu kullanıcı-meli muktedir kullanma sarmalayıcı işlevleri sys.sp_cdc_generate_wrapper_function sistem saklı yordam, değişiklik olmadan oluşturur. Ancak, sarıcı işlevleri özelleştirmek için oluşturma komut dosyaları, komut dosyalarını çalıştırmadan önce özelleştirme gerekir.

Paketinizi sarıcı işlevleri aradığında, paketi üç parametre değerlerini sağlamanız gerekir. Değişiklik veri işlevleri yakalama üç parametre kullanmak gibi bu üç parametre vardır. Bu üç parametreleri aşağıdaki gibidir:

Başlangıç tarihi/saati ve bitiş tarihi/saati değer aralığı için. Sarmalayıcı işlevleri sorgu aralığı için bitiş noktaları Tarih/Saat değerlerini kullanırken, değişiklik veri işlevleri kullanımı iki lsn değeri bitiş noktaları yakalayın.
Satır filtre. İşlevler, sarıcı işlevleri ve değişiklik veri yakalama için @row\_filter\_optionparametresi aynıdır. Daha fazla bilgi için CDC.fn_cdc_get_all_changes_<capture_instance> (Transact-sql)ve değiştirir<capture_instance> (Transact-sql).

Aşağıdaki veri sarmalayıcı işlevleri includesthe tarafından döndürülen sonuç kümesi:

Tüm istenen sütunların veri değiştirin.
Bir sütun, bir veya iki karakterlik alan satır ile ilişkili olan işlemi tanımlamak için kullandığı verir__cdc_operation adlı. Bu alan için geçerli değerler aşağıdaki gibidir: 'Ben' için INSERT, 'd' silmek için 'uo' için güncelleştirme eski değerleri ve 'un' için yeni değerleri güncelleştir.
Onları, sonra işlem kodunun ve belirtilen sırada bit sütun olarak beliren istediğinde bayrakları, güncelleştirme @update\_flag\_listparametresi. Bu sütunları ilişkili sütun adı '_uflag' eklenerek adlandırılır.

Sarmalayıcı işlevi, paket için tüm değişiklikleri sorguladığı bir sarmalayıcı işlevi çağırırsa, sütunlar, __cdc_startlsn ve __cdc_seqval döndürür. Bu iki sütunu birinci ve ikinci sütununda, sırasıyla, sonuç kümesi olur. Sarmalayıcı işlevi de bu iki sütunları temel alan sonuç kümesini sıralar.

Kendi tablo değerli işlev yazma

Ayrıca SQL Server Management Studiokendi sarmalayıcı tablo değerli işlev değişiklik veri yakalama sorgu işlevi ve tablo valued sarıcı saklamak aramalar içinde fonksiyonu yazmak için SQL Server. Transact-sql işlevi oluşturma hakkında daha fazla bilgi için bkz: CREATE FUNCTION (Transact-SQL).

Aşağıdaki örnek, değişiklik bir Müşteri tablosundan alır belirtilen değişim aralığı için tablo değerli bir işlevi tanımlar. Eşlemek için bu işlevi kullanır değişiklik veri yakalama işlevleri datetimedeğerler ikili günlük sıra numarası (lsn) değerleri dahili olarak değişiklik tabloları kullanan. Bu işlev Ayrıca birkaç özel koşullarını işleme:

Boş değer için başlangıç saatini geçirildiğinde, bu işlev kullanılabilir en erken değeri kullanır.
Boş değer için bitiş saatini geçirildiğinde, bu işlev kullanılabilir en son değeri kullanır.
Ne zaman başlangıç lsn genellikle gösterir seçili aralığı için kayıt vardır, bu işlev çıkar bitiş lsn eşittir.

Değiştir veri sorgular tablo değerli fonksiyon örneği

CREATE function CDCSample.uf_Customer (
     @start_time datetime
    ,@end_time datetime
)
returns @Customer table (
     CustomerID int
    ,TerritoryID int
    ,CustomerType nchar(1)
    ,rowguid uniqueidentifier
    ,ModifiedDate datetime
    ,CDC_OPERATION varchar(1)
) as
begin
    declare @from_lsn binary(10), @to_lsn binary(10)

    if (@start_time is null)
        select @from_lsn = sys.fn_cdc_get_min_lsn('Customer')
    else
        select @from_lsn = sys.fn_cdc_increment_lsn(sys.fn_cdc_map_time_to_lsn('largest less than or equal',@start_time))

    if (@end_time is null)
        select @to_lsn = sys.fn_cdc_get_max_lsn()
    else
        select @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)

    if (@from_lsn = sys.fn_cdc_increment_lsn(@to_lsn))
        return

    -- Query for change data
    insert into @Customer
    select 
        CustomerID,    
        TerritoryID, 
        CustomerType, 
        rowguid, 
        ModifiedDate, 
        case __$operation
                when 1 then 'D'
                when 2 then 'I'
                when 4 then 'U'
                else null
         end as CDC_OPERATION
    from 
        cdc.fn_cdc_get_net_changes_Customer(@from_lsn, @to_lsn, 'all')

    return
end 
go

CREATE function CDCSample.uf_Customer (
     @start_time datetime
    ,@end_time datetime
)
returns @Customer table (
     CustomerID int
    ,TerritoryID int
    ,CustomerType nchar(1)
    ,rowguid uniqueidentifier
    ,ModifiedDate datetime
    ,CDC_OPERATION varchar(1)
) as
begin
    declare @from_lsn binary(10), @to_lsn binary(10)

    if (@start_time is null)
        select @from_lsn = sys.fn_cdc_get_min_lsn('Customer')
    else
        select @from_lsn = sys.fn_cdc_increment_lsn(sys.fn_cdc_map_time_to_lsn('largest less than or equal',@start_time))

    if (@end_time is null)
        select @to_lsn = sys.fn_cdc_get_max_lsn()
    else
        select @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)

    if (@from_lsn = sys.fn_cdc_increment_lsn(@to_lsn))
        return

    -- Query for change data
    insert into @Customer
    select 
        CustomerID,    
        TerritoryID, 
        CustomerType, 
        rowguid, 
        ModifiedDate, 
        case __$operation
                when 1 then 'D'
                when 2 then 'I'
                when 4 then 'U'
                else null
         end as CDC_OPERATION
    from 
        cdc.fn_cdc_get_net_changes_Customer(@from_lsn, @to_lsn, 'all')

    return
end 
go

Değişiklik veri ile ek meta veri alma

Daha önce gösterilen kullanıcı tarafından oluşturulan tablo değerli işlev yalnızca kullansa __$ işlem sütununda değiştirir<capture_instance > işlevi dört sütun meta verileri her değişiklik satır. Bu değerler, veri akışı kullanmak isterseniz, bunları sarmalayıcı tablo değerli işlev ek sütunlar dönebilirsiniz.

Sütun adı	Veri türü	Açıklama
__$ start_lsn	binary(10)	lsn değişikliği tamamlama hareket ile ilişkili. Aynı hareket kaydedilmiş tüm değişiklikleri aynı kayıt lsn paylaşın. Değişikliği tablo kaynak tablo güncelleştirme işlemi iki farklı satırları değiştirir, örneğin, dört satır (iki eski değerleri ile) ve iki yeni değerlerle içeren her ile aynı __$ start_lsn değer.
__$ seqval	binary(10)	Bir hareket içinde satır değişiklikleri sipariş için kullanılan sıra değeri.
__$ işlem	int	Değişikliği ile ilişkili veri düzenleme dili (dml) işlemi. Aşağıdakilerden biri olabilir: 1 = silme 2 = Ekle 3 = güncelleştirme (değerler güncelleştirme işleminden önce.) 4 = Güncelleştirme (değerleri güncelleştirme işlemi sonra.)
__$ update_mask	varbinary(128)	Değiştirilen bu sütunları tanımlama değişiklik tablonun sütun sıra sayıları temel alan bir bit maskesi. Eğer hangi sütunların değiştirilmiş belirlemek zorunda bu değer incelemek.
<Yakalanan kaynak tablo sütunları>	değişir	İşlev tarafından döndürülen kalan sütunları yakalama örneği oluşturulduğunda, yakalanan sütun olarak tespit edilmiştir kaynak tablo sütunları olan. Hiçbir sütun başlangıçta yakalanan sütun listesinde belirtilmiş olması durumunda, kaynak tablodaki tüm sütunlar döndürür.