An auto generating SQL Merge stored procedure for Type 2 SCD

This could shave off some time in generating SQL Merge statement by hand. This helpful stored proc. will auto generate your merge statement on tables that use Type 0,1, and 2 SCD.  The dim table just needs to be setup to use extended properties.

He uses the schema dim and etl to represent the dimension table and staging table, respectfully. The field IsRowCurrent is used to represent the current updated row.

http://www.purplefrogsystems.com/blog/2011/12/introduction-to-t-sql-merge-basics/
http://www.purplefrogsystems.com/blog/2012/01/using-t-sql-merge-to-load-data-warehouse-dimensions/
http://www.purplefrogsystems.com/blog/2012/04/automating-t-sql-merge-to-load-dimensions-scd/

Code Snippet

1. /***************************************************************
2. *                                                              *
3. *   Script for use with blog post                              *
4. *     "Automating T-SQL Merge to load Dimensions (SCD)"        *
5. *     http://www.purplefrogsystems.com/blog/2012/04/automating-t-sql-merge-to-load-dimensions-scd
6. *                                                              *   
7. *   Posted: 6th April 2012                                     *
8. *                                                              *
9. *   By: Alex Whittles - Purple Frog Business Intelligence      *
10. *       www.PurpleFrogSystems.com                              *
11. *                                                              *
12. *   All code samples are provided “AS IS” without warranty of  *
13. *   any kind, either express or implied, including but not     *
14. *   limited to the implied warranties of merchantability       *
15. *   and/or fitness for a particular purpose.                   *
16. *                                                              *
17. ***************************************************************/
18.  
19. --The generate merge proc
20. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenerateMerge]') AND type in (N'P', N'PC'))
21. DROP PROCEDURE [dbo].[GenerateMerge]
22. GO
23.  
24. CREATE PROCEDURE [dbo].[GenerateMerge]
25.     @Dimension  varchar(50),
26.     @Schema     varchar(50)='dim',
27.     @ETLSchema  varchar(50)='etl',
28.     @Execute    bit=0  --Should the resulting merge be returned or executed
29. AS
30. BEGIN
31.     SET NOCOUNT ON;
32.  
33.     --Create Carriage return variable to help format the resulting query
34.     DECLARE @crlf char(2)
35.     SET @crlf = CHAR(13)
36.  
37.  
38.     --Find out which Audit fields are used
39.     DECLARE @UseIsInferred bit
40.     DECLARE @UseValidFrom bit
41.     DECLARE @UseValidTo bit
42.     DECLARE @UseIsRowCurrent bit
43.     DECLARE @UseLastUpdated bit
44.     
45.     SET @UseIsInferred = ISNULL((SELECT MAX(1)
46.     FROM sys.columns c
47.             INNER JOIN sys.tables t on c.object_id = t.object_id
48.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
49.     WHERE       s.name = @Schema
50.             AND t.name = @Dimension
51.             AND c.name = 'IsInferred'
52.     ),0)
53.  
54.     SET @UseValidFrom= ISNULL((SELECT MAX(1)
55.     FROM sys.columns c
56.             INNER JOIN sys.tables t on c.object_id = t.object_id
57.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
58.     WHERE       s.name = @Schema
59.             AND t.name = @Dimension
60.             AND c.name = 'ValidFrom'
61.     ),0)
62.  
63.     SET @UseValidTo = ISNULL((SELECT MAX(1)
64.     FROM sys.columns c
65.             INNER JOIN sys.tables t on c.object_id = t.object_id
66.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
67.     WHERE       s.name = @Schema
68.             AND t.name = @Dimension
69.             AND c.name = 'ValidTo'
70.     ),0)
71.  
72.     SET @UseIsRowCurrent = ISNULL((SELECT MAX(1)
73.     FROM sys.columns c
74.             INNER JOIN sys.tables t on c.object_id = t.object_id
75.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
76.     WHERE       s.name = @Schema
77.             AND t.name = @Dimension
78.             AND c.name = 'IsRowCurrent'
79.     ),0)
80.  
81.     SET @UseLastUpdated = ISNULL((SELECT MAX(1)
82.     FROM sys.columns c
83.             INNER JOIN sys.tables t on c.object_id = t.object_id
84.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
85.     WHERE       s.name = @Schema
86.             AND t.name = @Dimension
87.             AND c.name = 'LastUpdated'
88.     ),0)
89.  
90.  
91.     --Identify the business key column(s)
92.     --Also define what the null replacement should be
93.     DECLARE myCurBK Cursor FOR
94.     SELECT c.name
95.         , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
96.             WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
97.             WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
98.             ELSE 'NULL' END AS NullRep
99.     FROM sys.columns c
100.         INNER JOIN sys.tables t on c.object_id = t.object_id
101.         INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
102.         INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
103.         INNER JOIN sys.extended_properties ep
104.              ON t.object_id=ep.major_id
105.             AND c.column_id=ep.minor_id
106.             AND ep.class=1
107.             AND ep.name='SCD'
108.     WHERE s.name = @Schema
109.         AND t.name = @Dimension
110.         AND ep.value = 'BK'
111.     ORDER BY c.column_id
112.     
113.     --Identify all fields to be merged (Exclude Type 0)
114.     DECLARE myCurType1 Cursor
115.     FOR SELECT c.name
116.         , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
117.             WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
118.             WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
119.             ELSE 'NULL' END AS NullRep
120.         FROM sys.columns c
121.             INNER JOIN sys.tables t on c.object_id = t.object_id
122.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
123.             INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
124.             LEFT JOIN sys.extended_properties ep
125.                  ON t.object_id=ep.major_id
126.                 AND c.column_id=ep.minor_id
127.                 AND ep.class=1
128.                 AND ep.name='SCD'
129.         WHERE s.name = @Schema
130.             AND t.name = @Dimension
131.             AND c.is_identity=0
132.             AND ISNULL(ep.value,'1') NOT IN ('0', 'Audit', 'BK')
133.         ORDER BY c.column_id ASC
134.  
135.     --Identify all fields for insert
136.     DECLARE myCurAll Cursor
137.     FOR SELECT c.name
138.         , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
139.             WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
140.             WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
141.             ELSE 'NULL' END AS NullRep
142.         FROM sys.columns c
143.             INNER JOIN sys.tables t on c.object_id = t.object_id
144.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
145.             INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
146.         WHERE s.name = @Schema
147.             AND t.name = @Dimension
148.             AND c.name NOT IN ('LastUpdated', 'IsInferred', 'ValidFrom', 'ValidTo', 'IsRowCurrent')
149.             AND c.is_identity=0
150.         ORDER BY c.column_id ASC
151.  
152.     DECLARE @Field varchar(255)
153.     DECLARE @NullRep varchar(20)
154.     
155.     DECLARE @SQL varchar(max)
156.     DECLARE @SQL2 varchar(max)
157.     DECLARE @SQL3 varchar(max)
158.  
159.     --Now start building up the dynamic SQL
160.     
161.     SET @SQL =                'MERGE      [' + @Schema + '].[' + @Dimension + '] AS Target'
162.     SET @SQL = @SQL + @crlf + '    USING [' + @ETLSchema + '].[' + @Dimension + '] AS Source'
163.     
164.     OPEN myCurBK
165.     FETCH NEXT FROM myCurBK INTO @Field, @NullRep
166.     IF (@@FETCH_STATUS>=0)
167.         BEGIN
168.             SET @SQL = @SQL + @crlf + '       ON Target.' + @Field + ' = Source.' + @Field
169.             FETCH NEXT FROM myCurBK INTO @Field, @NullRep
170.         END
171.     WHILE (@@FETCH_STATUS<>-1)
172.     BEGIN
173.         IF (@@FETCH_STATUS<>-2)
174.             SET @SQL = @SQL + @crlf + '       AND Target.' + @Field + ' = Source.' + @Field
175.         FETCH NEXT FROM myCurBK INTO @Field, @NullRep
176.     END
177.     CLOSE myCurBK
178.  
179.     IF @UseIsRowCurrent>0   SET @SQL = @SQL + @crlf + '       AND Target.IsRowCurrent=1'
180.     
181.     SET @SQL = @SQL + @crlf + '    WHEN MATCHED'
182.     
183.     OPEN myCurType1
184.     FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
185.     
186.     IF (@@FETCH_STATUS>=0)
187.         BEGIN
188.             SET @SQL = @SQL + @crlf + '       AND (ISNULL(Target.' + @Field + ',' + @NullRep + ') <> ISNULL(Source.' + @Field + ',' + @NullRep + ')'
189.             FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
190.         END
191.     WHILE (@@FETCH_STATUS<>-1)
192.     BEGIN
193.         IF (@@FETCH_STATUS<>-2)
194.             SET @SQL = @SQL + @crlf + '       OR ISNULL(Target.' + @Field + ',' + @NullRep + ') <> ISNULL(Source.' + @Field + ',' + @NullRep + ')'
195.         FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
196.     END
197.     CLOSE myCurType1
198.     
199.     SET @SQL = @SQL + @crlf + '      )'
200.     SET @SQL2 = '    THEN UPDATE SET'
201.  
202.     OPEN myCurType1
203.     FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
204.     IF (@@FETCH_STATUS>=0)
205.         BEGIN
206.             SET @SQL2 = @SQL2 + @crlf + '       ' + @Field + ' = Source.' + @Field
207.             FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
208.         END
209.     WHILE (@@FETCH_STATUS<>-1)
210.     BEGIN
211.         IF (@@FETCH_STATUS<>-2)
212.             SET @SQL2 = @SQL2 + @crlf + '      ,' + @Field + ' = Source.' + @Field
213.         FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
214.     END
215.     CLOSE myCurType1
216.     
217.     IF @UseLastUpdated>0   SET @SQL2 = @SQL2 + @crlf + '      ,LastUpdated = GetDate()'
218.     
219.     SET @SQL3 = '    WHEN NOT MATCHED THEN'
220.     SET @SQL3 = @SQL3 + @crlf + '         INSERT ('
221.     
222.     OPEN myCurAll
223.     FETCH NEXT FROM myCurAll INTO @Field, @NullRep
224.     IF (@@FETCH_STATUS>=0)
225.         BEGIN
226.             SET @SQL3 = @SQL3 + @crlf + '           ' + @Field
227.             FETCH NEXT FROM myCurAll INTO @Field, @NullRep
228.         END
229.     WHILE (@@FETCH_STATUS<>-1)
230.     BEGIN
231.         IF (@@FETCH_STATUS<>-2)
232.             SET @SQL3 = @SQL3 + @crlf + '           ,' + @Field
233.         FETCH NEXT FROM myCurAll INTO @Field, @NullRep
234.     END
235.     CLOSE myCurAll
236.     
237.      
238.     IF @UseIsInferred>0     SET @SQL3 = @SQL3 + @crlf + '           ,IsInferred'
239.     IF @UseValidFrom>0      SET @SQL3 = @SQL3 + @crlf + '           ,ValidFrom'
240.     IF @UseValidTo>0        SET @SQL3 = @SQL3 + @crlf + '           ,ValidTo'
241.     IF @UseIsRowCurrent>0   SET @SQL3 = @SQL3 + @crlf + '           ,IsRowCurrent'
242.     IF @UseLastUpdated>0    SET @SQL3 = @SQL3 + @crlf + '           ,LastUpdated'
243.     SET @SQL3 = @SQL3 + @crlf + '         ) VALUES ('
244.     
245.     OPEN myCurAll
246.     FETCH NEXT FROM myCurAll INTO @Field, @NullRep
247.     IF (@@FETCH_STATUS>=0)
248.         BEGIN
249.             SET @SQL3 = @SQL3 + @crlf + '            Source.' + @Field
250.             FETCH NEXT FROM myCurAll INTO @Field, @NullRep
251.         END
252.     WHILE (@@FETCH_STATUS<>-1)
253.     BEGIN
254.         IF (@@FETCH_STATUS<>-2)
255.             SET @SQL3 = @SQL3 + @crlf + '           ,Source.' + @Field
256.         FETCH NEXT FROM myCurAll INTO @Field, @NullRep
257.     END
258.     CLOSE myCurAll    
259.     
260.     IF @UseIsInferred>0     SET @SQL3 = @SQL3 + @crlf + '           ,0'
261.     IF @UseValidFrom>0      SET @SQL3 = @SQL3 + @crlf + '           ,GetDate()'
262.     IF @UseValidTo>0        SET @SQL3 = @SQL3 + @crlf + '           ,NULL'
263.     IF @UseIsRowCurrent>0   SET @SQL3 = @SQL3 + @crlf + '           ,1'
264.     IF @UseLastUpdated>0    SET @SQL3 = @SQL3 + @crlf + '           ,GetDate()'
265.     SET @SQL3 = @SQL3 + @crlf + '         );'
266.     
267.     --clean up
268.     DEALLOCATE myCurType1
269.     DEALLOCATE myCurAll
270.     DEALLOCATE myCurBK
271.     
272.     IF @Execute = 1
273.     BEGIN
274.         EXEC(@SQL + @SQL2 + @SQL3)           
275.     END
276.     ELSE
277.     BEGIN    
278.         PRINT @SQL
279.         PRINT @SQL2
280.         PRINT @SQL3
281.     END
282.     
283. END
284.  
285. GO

An example of the extended properties being set through sql statement:

Code Snippet

1. EXEC sys.sp_addextendedproperty @level2name=N'CustomerKey',  @value=N'PK' ,    
2.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
3.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
4. EXEC sys.sp_addextendedproperty @level2name=N'Email',        @value=N'BK' ,    
5.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
6.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
7. EXEC sys.sp_addextendedproperty @level2name=N'FirstName',    @value=N'1' ,     
8.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
9.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
10. EXEC sys.sp_addextendedproperty @level2name=N'LastName',     @value=N'1' ,     
11.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
12.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
13. EXEC sys.sp_addextendedproperty @level2name=N'DoB',          @value=N'1' ,     
14.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
15.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
16. EXEC sys.sp_addextendedproperty @level2name=N'Sex',          @value=N'1' ,     
17.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
18.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
19. EXEC sys.sp_addextendedproperty @level2name=N'MaritalStatus',@value=N'1' ,     
20.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
21.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
22. EXEC sys.sp_addextendedproperty @level2name=N'FirstCreated', @value=N'1' ,     
23.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
24.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
25. EXEC sys.sp_addextendedproperty @level2name=N'ValidFrom',    @value=N'Audit' ,
26.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
27.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
28. EXEC sys.sp_addextendedproperty @level2name=N'ValidTo',      @value=N'Audit' ,
29.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
30.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
31. EXEC sys.sp_addextendedproperty @level2name=N'IsRowCurrent', @value=N'Audit' ,
32.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
33.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
34. EXEC sys.sp_addextendedproperty @level2name=N'LastUpdated',  @value=N'Audit' ,
35.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
36.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'

An example of printing the script:

Code Snippet

1. EXEC dbo.GenerateMerge
2.     @Dimension = 'Customer',
3.     @Schema = 'dim',
4.     @ETLSchema = 'etl',
5.     @Execute = 0

Exporting to Excel in SSIS

How to export data to excel. I did follow other blogs, but they leave out vital information to get it working, so here is my attempt.

Preview of what's to come from Control Flow view:

 

You're going to need to first create a Execute SQL Task, because this is going to be used to create your sheet in excel. The syntax is similar to SQL Server's Create Table, except that it uses the accent quote `  instead of squaring the names in brackets i.e.: [name]. It also seems to have a problem with numeric and character cell size limits.





In this case, I'm creating an excel sheet called Process:



CREATE TABLE `Process`(
 `SuperClientVendorID` INT,
 `LoanNumber` varchar(25),
 `OpenIndicator` char(3),
 `CloseIndicator` char(3),
 `UpdateIndicator` char(3),
 `ParentRefID` INT,
 `Open_RailDescription` varchar(200),
 `AssignedVendorID` INT ,
 `CloseReason` varchar(200) ,
 `RefID` numeric(18, 0),
 `CloseDate` date ,
 `InheritedAttribute` varchar(3) ,
 `ProcessorCd` varchar(50) ,
 `ProcessStartDate` date ,
 `ReOpenIndicator` varchar(3) ,
 `NoteType` varchar(255) ,
 `Note` text
)

Now, lets create a Excel Connection Manager to dynamically create our file. You're going to need a dummy file to initially create an connection instance. In this dummy file, I had my first rows of names, but I'm not sure if this is really needed.

 You're going to point the connection to the excel file for now. Right click on the newly created connection and select properties. In this window, under expressions you're going to create a ExcelFilePath expression:

My text, I use a variable called Processed to represent the file path and folder. I then add a datetime string to the end of the file.

@[User::Processed] + "\\FileName " + RIGHT("0" + (DT_STR,2,1252)DATEPART("MM" ,GETDATE()), 2) +
 RIGHT("0" + (DT_STR,2,1252)DATEPART("DD" ,GETDATE()), 2) + (DT_STR,4,1252)DATEPART("YYYY" ,GETDATE())  + "_"  + Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) + "" + Right("0" +  (DT_STR,4,1252) DatePart("n",getdate()),2)  +""+ ".xls"


I had a problem with the connection at this point, I took the ExcelFilePath name from properties and moved my dummy excel there and renamed it. This won't be a problem when it runs, since the name will change given the time. If you need to edit the file, you will need to recreate/rename your dummy file.

Now create a Data Flow Task and inside that task, create your source file, where you will be pulling the data from. Setup your query to pull the data, etc.... And create your destination excel file.

Point it to your dummy excel file and dummy sheet name.


Run the program, and hope for no errors.



Helpful resources on the same thing:
http://geekepisodes.com/sqlbi/2011/creating-excel-files-xls-dynamically-from-ssis/
http://jandho.blogspot.com/2012/03/ssis-package-to-export-to-new-excel.html

Excel Error in SSIS - 64bit runtime error

Running a very simple SSIS 2012; which pulls data from an excel file. I've done this a million times, but all of a sudden I ran into this tidbit when running on a new machine:

Errors:

• [Excel Source [39]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
• [SSIS.Pipeline] Error: Excel Source failed validation and returned error code 0xC020801C.
• [SSIS.Pipeline] Error: One or more component failed validation.
• Error: There were errors during task validation.
• [Connection manager "Excel Connection Manager"] Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

On the blogs, people say to download the 64-bit version of the drivers at: http://www.microsoft.com/en-us/download/details.aspx?id=13255
I still had trouble getting it to work, so I changed it to run "not" in 64-bit. Just go to Project/[Name of Project] Properties/Configuration Properties/Debugging under Debug Options and change "Run64BitRuntime" from default value of true to false.

This also means, if you are going to run it in 32-bit, you also have to tell the SQL Agent to run in 32-bit as well. Edit your job and then edit the right job step. Go to the Execution Options pane and check "Use 32 bit runtime". This property will only effect the package (+child packages) called in this job step.

Quick Tip: Importing images into a database from SSIS

To have SSIS upload to the database, in your data flow task, add a "Import Column". Set the "Input Columns" as the image file path, and the "Output Columns" as a image datatype. In SQL-Server, the field just needs to be an "image" type, when doing a data flow destination.

Debugging Interop in SSIS

Had a little trouble getting a COM (interop) dll/tbl from working in a SSIS's script task, while a similar program in a normal C# project would run sucessfully.

First thing I did was a quick registry hack to turn logging on for interop:
HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/Fusion

and created a new DWORD called EnableLog
and set the value to 1

This then gave me more info when I ran the package "without debugging".

In this case, it was having trouble finding my interop dll, because it was looking in the SQL-Server and .Net directories instead of the build directory.

So, as a quick solution, I copied the interop dlls from the build directory and drop them in the necessary folders:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\

-and-

C:\Windows\Microsoft.NET\Framework\v2.0.50727