Create and Load a Cache transform

gravatar

R

This snipped shows how to create and load a persistent lookup Cache. The cache is written to a cache file.

published 04.13.15

last updated 04.13.15


Share

Tags

  • Cache
  • Transform
                            


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
     <FileFormats>
          <RawFileFormat Name="CacheFormat">
               <Columns>
                    <Column Name="CustomerKey" DataType="Int32" IndexPosition="0"></Column>
                    <Column Name="CustomerAlternateKey" DataType="String" Length="15" IndexPosition="1"></Column>
               </Columns>
          </RawFileFormat>
     </FileFormats>
     <Connections>
          <FileConnection Name="LookupCacheFile" CreateInProject="1" FilePath="C:\CustomerLookupCache.CAW"></FileConnection>
          <CacheConnection Name="LookupCache" CreateInProject="1" RawFileFormatName="CacheFormat" PersistFileConnectionName="LookupCacheFile"></CacheConnection>
          <Connection Name="AdventureWorksDW2014" CreateInProject="1" ConnectionString="Data Source=MyServer\SANDBOX;Initial Catalog=AdventureWorksDW2014;Provider=SQLOLEDB.1;Integrated Security=SSPI;"></Connection>
     </Connections>
     <Packages>
          <Package Name ="TestCacheTransform" ConstraintMode="Linear">
               <Tasks>
                    <Dataflow Name ="LoadCacheTransform">
                         <Transformations>
                              <OleDbSource Name ="Read Customer" ConnectionName="AdventureWorksDW2014">
                                   <DirectInput>SELECT  CustomerKey, CustomerAlternateKey FROM DimCustomer</DirectInput>
                              </OleDbSource>
                              <Cache Name="Customer Cache" ConnectionName="LookupCache">
                                   <InputPath OutputPathName="Read Customer.Output"></InputPath>
                              </Cache>
                         </Transformations>
                    </Dataflow>
               </Tasks>
          </Package>
     </Packages>
</Biml>

First a new RawfileFormat is created. This describes the format of the Cache file Secondly two connections are created: - File connection which points to the location of the Cache file. If you are creating an In-Memory cache, this is not needed. - Cache connection. The cache connection points to both the RawfileFormat and the File connection. If you are using an In-Memory cache leave the "PersistFileConnectionName="LookupCacheFile"" out

The Package contains a dataflow to retrieve data from a source (in this case the DimCustomer table in the Adventureworks sample database) The source is written to the Cache transform. Because the Cache uses the same column names as the source, no column mappings are needed.

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

There are no comments yet.