Monday, 24 October 2016

Using PowerShell for SSIS

Recently I presented an SSIS & PowerShell session at the SQL Serverdays in Schelle, Belgium and SQL Saturday in Utrecht, The Netherlands. You can download the PowerPoints:

And here is a list of some of the scripts used in the demo's:

And some atmospheric impressions:

The venue in Schelle near Antwerp

Look at me pointing :-)

The venue in Utrecht

Look at me again :-)

And some atmospheric impressions on youtube in Dutch

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.

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.
Related Posts Plugin for WordPress, Blogger...