Saturday 22 October 2016

SSIS Appetizer: Cache Transformation File is Raw File

SSIS Appetizer
I'm not sure I have a purpose for this, but did you know that you can use the cache file of the Cache Transformation (introduced in SSIS 2008) as a source file in the Raw File Source.

Demo
For this demo I use two Data Flow Tasks. The first creates the cache file and the second one uses it as a source.
Two Data Flow Task

























1) Create Cache
The first Data Flow has a random source (a flat file in this case) and a Cache Transformation named "CTR - Create Cache"  as a destination. When you create the Cache Connection Manager, make sure to check "Use file cache" to provide a file path for the cache file. Copy the path for the next step.
The Cache Transformation and Connection Manager

















2) Read Cache
The second Data Flow Task uses a Raw File Source. In the editor you can specify the location of the Raw File. Paste the path from the Cache Connection Manager (a .caw file). For demonstration purposes I added a dummy Derived Column behind it with a Data Viewer on the path between them. Now run the package a see the result. You will get some hash columns 'for free'.
Raw File Source













Please let me know in the comments if you found a good purpose for this.

Note: you can't use a raw file as a cache file unless you're able to add the extra hash columns as well.

2 comments:

  1. Glad to see you paid attention during Matts precon ;)

    ReplyDelete
    Replies
    1. Yes always good to listen to him and to have input for at least ten more posts...

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...