SSIS package to export to a new Excel file

Problem

How to export a large number of columns into non existing excel file.

Developing an SSIS package to export to an existing Excel file is easy when the Excel file exists with column headings on the excel sheet. If you have an export that will be scheduled on a daily basis of 50+ columns to a new excel file, then you have some careful typing/copy-pasting to do to create an excel template file. Instead.

Solution

What I often do is to generate an SSIS package with the Sql Server Management Studio export wizard. The interesting thing about this wizard is that it can generate excel files on the fly.After saving the package thus generated by SSMS, you can open the SSIS package in Visual Studio to inspect what it is doing.

The package has a dataflow task, but it is preceded by a SQL task. The trick lies in the SQL statement that generates the columns for the Excel sheet. When we edit the Preparation SQL Task 1, then we see a SQL statement that creates a table called ‘Sheet’.

CREATE TABLE `Query` (
`PlantKey` VarChar(8),
…

‘OrderValue’ Decimal(18,5)
)
GO

Conclusion

I can use the package generated by SSMS to adapt to my needs, for instance to generate a series of excel filenames in a For Each Loop Container or in a For Loop container, store theEexcel filename in a variable and put the Preparation SQL task and the Dataflow task in the container. I will still need the Excel file generated by the SSMS package to further design my Dataflow task. But once the package is deployed it will use the Excel variable name in the Excel connection.

Remark

This solution does not take into consideration the problem of Numeric values getting converted to text in the excel sheet.

Using a different theme for LightSwitch

Because the standard Lightswitch is rather blunt, I will have a go at trying another theme. Just to get an idea of the easy with wich it can be used and deployed.

1. You can browse and download tehmes here, e.g. LightSwitch Cosmopolitan shell and Theme . Or you can use the Visual Studio Extension manager to browse and install LightSwitch themes.
2. In the Visual Studio menu select Tools / Extension manager…
3. In the left hand box click on Online Gallery, in the search box tuype ‘Lightswitch’ to see available extensions.
   
4. Select a theme and click on the download button
   
5. The download and install screen, click on Install.
   
6. You have to restart VS for the changes to take effect. Click on Restart
   
7. Start VS and open your Lightswitch solution.
8. Double click on the Project properties in the solution explorer (in Logical view). The Designer is opened, select the Extensions tab. Check the theme that you want to use in this project.
   
9. In the General properties tab.Select the theme you need.
   
10. Debug start your application and appreciate (or not) the result.
    
    

LightSwitch desktop app needs dialog to select and copy a file

Problem Description

The user of a LightSwitch intranet application needs a button to select a file from his pc and copy it over to the Windows server where another process will pick it up and process it.

I have read a lot of articles about uploading downloading files in LightSwitch for web based and desktop based apps. Here is what works for me. The solution comes from an article by Dan Seefeldt.

Solution

The user needs a button on one of his screens with which he can select a file that will then be copied by the application into a particular location on a Windows server. Clicking on the button will open an open file dialog, after selecting the file and clicking on the Open button, the app will copy the selected file into another windows folder.

1. to create the button on one of the screens, with the text ‘Load File’
   
2. In VS open the screen design, under ‘Rows layout / Screen command bar’ click on ‘+ Add’ to add a new button. the button is called ‘LoadFile’. By using this naming convention, it will be displayed with spaces between the words.
   
3. To add code behind the button, right click on the button ‘Load file’ and from the popup menu select ‘Edit Execute Code’. This will open the event handler code that handles the button click event.
   
4. the method LoadFile_Execute() will be created, I have added 2 using statements for the openfiledialog windows control and for the Ligthswitch threading. 
   
   

   using System.Windows.Controls;

   
   
   
   

   using Microsoft.LightSwitch.Threading;

   
   
   
   

   namespace LightSwitchApplication

   
   
   
   

   {

   
   
   
   

       public partial class EditablePersonnelsGrid

   
   
   
   

       {

   
   
   
   

           partial void LoadFile_Execute()

   
   
   
   

           {

   
   
   
   

               System.IO.FileInfo file = null;            // OpenFileDialog() must be opened on the UI thread

   
   
   
   

               Dispatchers.Main.Invoke(() =>

   
   
   
   

               {

   
   
   
   

                   OpenFileDialog dlg = new OpenFileDialog();

   
   
   
   

                   dlg.Filter = "CSV Files (*.csv)|*.csv|Text Files (*.txt)|*.txt";

   
   
   
   

                   if (dlg.ShowDialog() == true)

   
   
   
   

                   {

   
   
   
   

                       file = dlg.File;

   
   
   
   

                   }

   
   
   
   

               });

   
   
   
   

           }

   
   
   
   

       }

   
   
   
   

   }

   
   

   
   
   
   



5. The previous code only shows the file object being selected, there is no code yet to copy the file to another folder. The code shown is rudimentary.
   
   
   
   
   
   
   
   
   

   if (dlg.ShowDialog() == true) 
   {
       file = dlg.File;
       string destfilename = System.IO.Path.Combine("H:\\downloads", file.Name);
       file.CopyTo(destfilename, true);
   }
   
   

   
   
   
   

Conclusion

The file copying works.

Important remark

The above code will work in the protected area of LightSwitch, for instance in My Documents on your pc, it will not work on an external or on network drive. To fix this problem I simply had to write an Windows Forms (or WPF) app that I launch from the LightSwitch command button, this app then opens the OpenFileDialog and copies the file.

How to copy SSRS 2005 reports from one folder to another

If you ever need to copy reports from one folder to another folder on SSRS 2005 server.

• on the same server
• it is copy not move
• you do not have the BIDS source project
• The report copies need to point to a different data source

This tedious task can be done as follows:

• With the web browser in the reporting services home folder, create the new folder
• to copy a report from the source folder to the destination folder
• with the web browser go to the source folder
• select ‘Show details’
• click on the Edit icon next to the report name
• in the edit page, click on the edit link, this will download a copy of the report, with rdl extension.
• Store the file on your pc.
• browse to the target folder you have created in the 1st step
• click on ‘upload file’
• Select the .rdl report file you have just downloaded.
• Upload the file
• to change the datasource of the reports in the new folder
• If the reports were using shared datasource then create a new Data source in the Data Sources folder.
• browse to the folder with the new report
• select ‘Show details’
• click on the Edit icon
• in the menu on the left click on ‘Data Sources’
• 
• Select a shared datasource, click on the Browse button.
• In the Location Home/Data Sources select the new datasource.
• now you should be able to open the report

SSIS to SQL Server Data Type Translations

I found a list of translations on http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations

LightSwitch reporting, first test of XtraReports

Today I will evaluate the XtraReports for LightSwitch reporting extension.

Installation of XtraReports and very 1st use

• Downloaded evaluation version from DevExpress
• After downloading 136 MB (with XtraReports evaluation and Some free controls). The download file was called “DXComponentsLightSwitch-11.1.9.exe”
• Ran the executable and installed both XtraReports and the free devexpress controls.
• After installation I followed Lesson1 on the devexpress site
  • You need a SQL server with the Northwind sample database for this lesson. Luckily I had one available already.
  • Following the lesson was easy and worked as explained. Congrats to Developer Express!

Deployment of XtraReports

• Read the instructions from XtraReports LS Deployment and was flabbergasted ??? It is quite the opposite of the lesson I used in the very 1st use. Here there was a dry summary of files composed by the lawyers.
• Let’s assume that this version of XtraReports is setup to that deployment does not need any tweaks.
• I use the project in previous chapter to deploy on a server on which I already have a working LightSwitch app. So if deployment problems arise with this project, it can’t be the web server.
• To Publish the project I noticed one dubious question in the Publish application wizard under Other Connections. What connection should I enter for the XtraReports WCF RIA service?. In the devexpress forums I found connection string for IIS7
• I entered XtraReportsServiceData as advised.
• 
• Aftter signing with the same certificate as the other working app, I started the deployment.
• 
• Deployment was done successfully, the site ‘XtraReportDeployTest’ was created (same name as my LightSwitch project)
• On a client pc on the same LAN, use IE to browse to this new site
• 
• Click on install button.
• after installation , application started and the screen with the report was displayed. It WORKS.

Easy steps to make a copy of a database on same SQL server

Very often you need to create a copy of a database on the same server as the original db. The main purpose of this article is to remind of the restore option ‘From database’, this will autoselect your database.

1. Backup original database
2. Restore
3. Rightclick on Databases in the Object explorer tree. Select ‘Restore Database…’
4. In the Restore Database dialog
   
5. To Database: enter a new database name.
6. From database: select the database you have backed up in step 1. In ‘Select the backup sets to restore listbox’, the name of the backup set created in step 1 will appear.
7. make sure the Restore checkbox is checked.
8. Click OK. the database will be restored.

The users that had been granted access to the original database will have the same kind of access to the new database.

To speed up processing with SSIS Lookup Data Flow Item use as little columns as necessary

The number of records in a table that was the basis of a Lookup Data flow Item has grown considerateley, whilst the processing speed of the Package was diminishing. In the Lookup item properties for the connection, I had been using the table name as datasource. When running the package interactively the Lookup caching was built up steadily in chuncks until all data had been loaded. I have changed the property to use a SELECT statement that uses only the essential columns. While runnng the package now, the Lookup cache buildup is a lot faster.

Use SSIS lookup to add only new records to a table and avoid hangup

Problem

Table A is transaction table, table B is a history table. Table B only needs to be populated with new rows from table A. Table B contains a copy of the business key in table A. This wil be done by an SSIS package.

Since no updates are necessary a slowly changing dimension transformation is will not be used.

As a first solutoin I created an SSIs package with a Dataflow, inside the Dataflow I have 3 steps: an OLE DB source to read an inventory, a data transformation and an OLE DB Destination. My OLE DB select statement would read table A and join to table B to find select only rows from A that are not yet written in B.

This is the select statement in table A:

SELECT InventId, col1, col2, ...

FROM dbo.Inventory AS a

LEFT OUTER JOINdbo.HistInvent AS b

      ON a.InventId = b.A_InventId

WHERE b.HistGUID IS NULL

The 3 steps work together fine as long as there are not too many rows, a couple of 1000 rows gave no problems to debug the package.

But when there are more than 100.000 rows then the package hangs. My guess is that the data is read in chunks and written in chunks, but the writing in step 3 blocks table B from being read for the next chunk in step 1.

Solution

Only read from Table A in step 1, then use a Lookup Transformation as a new step between the 1st and 2nd step. The lookup transformation reads the business key in Table B and compares it to the Primary key of table A.

The lookup only redirects Rows that are not found in B.

The Dataflow in 4 steps:

This time the package runs fine for more than 500.000 rows.

Conclusion

although it seemed efficient to let SQL server limit the data flow in step 1, it is advisable to use the SSIS tools even though they create more overhead.

Troubleshooting orphaned users

Reminder

Every couple of months I get to migrate a database backup to some other server and most often the users need to be recreated. As a reminder for myself, I’ve written this post.

The detailed explantaion can be found in this KB article on technet

Step to resolve orphaned users

Use SSMS and connect to the server.

Open a new query window

To detect orphaned users in a database:

Use <MyDatabase>

go

sp_change_users_login 'report'

go

Query result

UserName    UserSID
someuser    0xCEC65E3AE7C0B64CA05435BE2B580EE9

Now I know the SQL user account, I can create the login with the necessary credentials in SSMS under Security – Logins. I only create the account and grant it server role ‘public’, no specific user mapping, give a temporary password, till the responsible can give me the correct one, the default db is master.

Use <MyDatabase>

go

sp_change_users_login 'update_one', 'someuser', 'someuser'

go

Now the user properties can be modified to set default database to this database name, alter password and grant other roles to the user on the database.

How to identify if SQL server installation is standard, enterprise or developer

This post from SQL server training site was very helpfulll : How to identify SQL server edition

New LightSwitch application displays red cross and no data

Problem

A new LightSwitch application works fine when debugging with Visual Studio, but no data is shown after the application has ben published, i.e. a red cross is shown in every screen of the app.

Solution

The database connection in the Data Sources was set up to use integrated security. For debugging this works. Once deployed the application will run on the web server, the account used to access the database will be the application pool account. A simple solution is to the deploy the app with a connection with a SQL user account and specify the account and password in the connection string of ‘Other Connections’ in the LightSwitch Publish Application Wizard.

Click on the button ‘…’ to open the ‘Connection properties’ dialog

enter a SQL user name and password and use this to publish the app.

Signing a LightSwitch application with test certificate for development purposes

When you deploy a LS app in house on the intranet to run on the client desktop and you do not have a certificate, then the next time you upgrade your application, the clients will not update automatically, but require the user to uninstall the app, then browse to the url for the published app and install it again.
This is the message you would get when launching the app from the desktop icon:
An error occurred while checking for updates. make sure you are running the latest version of the application by uninstalling it from Add/Remove programs in the Control Panel, and then re-installing the application.
Details: Cannot update application, the installed application and update candidate differ in certificate/signature state.
Conclusion: LS desktop apps need a certificate to automatically update after deployment of a new version. Alternatively we can deploy the app to run in the browser.

John Papa's blog explains how to manually create a certificate

Create root certificate

Run VS2010 command prompt
makecert -n "CN=JDH Root Certificate Authority" -r -a sha1 -sv c:\Development\Certificate\TestOOBRootCA.pvk c:\Development\Certificate\TestOOBRootCA.cer -sr LocalMachine -sky signature

Enter password (2x)
Click OK

Re-enter password
OK

“Succeeded“

Create child certificate from root certificate

Run VS2010 command prompt
makecert -sv c:\Development\Certificate\TestOOBCodeSigningCA.pvk -iv c:\Development\Certificate\TestOOBRootCA.pvk -n "CN=JDH Test OOB child Code Signing CA" -ic c:\Development\Certificate\TestOOBRootCA.cer c:\Development\Certificate\TestOOBCodeSigningCA.cer

Use the same password

Use the same password
Issuer password
Use the same password

“Succeeded“

Generate PFX file

This file contains pwd and private key in one file for convenience.
pvk2pfx -pvk c:\Development\Certificate\TestOOBCodeSigningCA.pvk -spc c:\Development\Certificate\TestOOBCodeSigningCA.cer -pfx c:\Development\Certificate\TestOOBCodeSigningCA.pfx -po password
Warning: the word password in the command line above, should be replaced by another password, you will need this password when you use this certificate in the LightSwitch Publish Application wizard.

enter the password used for creating the root and child certificate.
“Succeeded“
These are the files up to now

The 5 last files are the ones from my example that count, (I have already added a 2nd child cert Test2... )

Use the certificate in LS deployment

In Visual Studio 2010, publish the LS application, menu Build – Publish…
LightSwitch Publish Application wizard starts.



Service Url: http://<myserver>
User Name: a windows user account that has admin rights on the webserver, this account will be used by the deployment agent to do the web installation on the server.
Allow untrusted certificate: you cannot check this box, once you have entered url and name. (to be investigated later what purpose this server).
Other connections

Click ‘Browse for a certificate…’
Browse to the folder with the PFX certificate and select the file c:\Development\Certificate\TestOOBCodeSigningCA.pfx

enter the password from the Generate PFX file command (not from the root or child certificate)

Remark: the expiration date is 2040, and not 2013 as it would have been, if the test certificate was created by the wizard ‘Create a test certificate..’
Remark: next time you deploy a LS application this certificate will be available in this computer’s store. So next time you can click ‘Select a certificate in this computer’s store’.
Finally, In the Publish summary of the wizard, click on publish.
Visual Studio Output window finishes with: ========== Publish: 1 succeeded, 0 failed, 0 skipped ==========
Now browse to the server on which the app was deployed. http://<Myserver>/<MyApp>
The installation page appears with an installation button, click on the button ‘Install MyApp’

Click on Install
The application gets installed, a shortcut is added to the desktop and at the end of the installation the app starts.
So far so good. Now what will happen when an update to application is deployed?

Deploy an update of the application

IN VS201 added a new screen, build and deploy with same parameters. Successfully published.
When clicking on the desktop icon on the client to start the app, the application pops up

close this window and start the app again. The new screen is available in the LS app menu.

Microsoft SQL Server Integration Services: Importing images with SSIS

I have found this usefull article about Microsoft SQL Server Integration Services: Importing images with SSIS
I have been able to apply this for one of my projects. Many thanks to the editor.

SQL Server 2008 store image into table column

For data in specific tables I will need to generate images, those images will have to be printed or displayed by various different applications.
First of all I need to store the image.
To store an image in a tablecolumn on SQL Server 2008, the manual advises to use the varbinary(MAX) type and not the image data type.
Store the image with a SQL server statement
OPENROWSET is a T-SQL function that allows for reading data from many sources including using the SQL Server’s BULK import capability. 
The bulk provider keyword needs to be SINGLE_BLOB, which reads a file as varbinary(max).
The path to the image file is a path that the SQL Server needs to be able to read, do not confuse it with the path that an app can read, i.e. if you execute the statement from SSMS on your PC then the path E: will be a drive on the server not on your PC.
The SQL server statement to insert an image is

INSERT INTO dbo.MyTable (Id,Name,LocationPicture,)
SELECT 1,N'NameOfidentity',BulkColumn
FROM Openrowset( Bulk 'E:\ImportBarcode\000014.jpg', SINGLE_BLOB) as A 
This will result in one new row being added to the table dbo.MyTable.

Server prerequisites to host Lightswitch apps - Web Deployment Tool

To install the prerequisites on SBS server 2008 with SQL 2008 (not R2).
On the SBS server use the Web Platform Installer 3.0.
In the Web Platform Installer menu choose 'Products', in the bar on the left choose 'Tools'.
Then select Visual Studio Lightswitch 2011 Server Runtime without local SQL.
Althought this is for a 3-Tier deployment ( IIS server - SQL server on separate machines) this should make it work. I intend to use the SQL 2008 standard edition on the SBS server.

UPDATE

Error 1 Web deployment task failed.(Remote agent (URL http://hera/MSDEPLOYAGENTSERVICE) could not be contacted.  Make sure the remote agent service is installed and started on the target computer.)


Remote agent (URL http://hera/MSDEPLOYAGENTSERVICE) could not be contacted.  Make sure the remote agent service is installed and started on the target computer.
An unsupported response was received. The response header 'MSDeploy.Response' was '' but 'v1' was expected.
The remote server returned an error: (503) Server Unavailable. C:\Program Files\MSBuild\Microsoft\VisualStudio\LightSwitch\v1.0\Microsoft.LightSwitch.targets 964 10 LoonKostBeheerLS

I have discovered that sometimes it is necessary to install the Web Deployment Tool also, it is not done by the Lightswitch prerequisites. This is necessary on the Windows Server with IIS, to publish from Visual Studio to the Web server, i.e. when not publishing to a package.
To Install th eweb deployment tool on the server

• Start Web Platform Installer 3.0
• In the search box, type 'Web Deployment' hit enter.

• Select Web Deployment 2.1 (the most recent date)
• Click Install
• In the next screen, click 'I Accept'

Printing Sql Server Reports with LightSwitch

In real life, customers have always asked for possibility to print, customers with a limited budget also cannot and wil not pay for lengthy development cycles. In the past I've often used MS-Access to solve this problem, mostly as a front end UI for a SQL-server (express) database. Now very often I am able to offer a Lightswitch solution. But MS-Access has always had a huge advantage over other RAD solutions: it can print reports and the reports can be quite complex - from pivo tables with variable nr of columns to reports with many levels of grouping, to bills, labels, and so on.
Now I need to tackle this hurdle for LightSwitch, so the natural solution for me would be to use what is available in VS2010 and on SQL server. If at all possible I do not want to buy additional reporting tools, been there done that.
As a first step I will investigate this article by Michael Washington about Printing Sql Server Reports (.rdlc) With LightSwitch

LightSwitch 2011 where is the connection string stored

To change the connection string of a Data Source in a LS project, in Solution Explorer switch to File View, click the button 'Show all files'. The ServerGenerated folder is shown, in this folder the file web.config can be edited
You will find the connectionStrings collection, with the connectionString value that you want to change.

SQL 2008 script to populate Data dimension table (revised)

Purpose
For a business app (not a datawarehouse) I can use the date dimension table too. But I prefer not to work with a primarey key that is an autoincrement value, as I will be using it in time registration tables, that can benefit from showing a date value and not an autoincrement ID that is nondescriptive. I will be using the data table as a lookup table for entering date on specific dates, i.e. not weekends and for me it sounded easier to create a table for this than to create code for date rules in the business tier. This way a user with sufficient permission has the added flexibility to make a data available e.g. on weekend, that would otherwise be declared as a rule in code.
Finally the DATE type in SQL 2008 is not taking up a lot of space either.
Also I have clarified the naming of my columns, instead of having a column Quarter or Month, it is now called QuarterOfYear and MonthOfYear. This indicates better what the content is and the names don't conflict with sql server functions, thereby avoiding having to type square brackets around the names [Month].
Create the table statement
CREATE TABLE [dbo].[DateDim](

[ActualDate] [date] NOT NULL,
[ActualYear] [int] NOT NULL,
[QuarterOfYear] [int] NOT NULL,
[MonthOfYear] [int] NOT NULL,
[WeekOfYear] [int] NOT NULL,
[DayOfYear] [smallint] NOT NULL,
[DayOfMonth] [smallint] NOT NULL,
[DayOfWeek] [smallint] NOT NULL,
[IsWeekend] [bit] NOT NULL,
[IsHoliday] [bit] NOT NULL,
[CalendarWeek] [smallint] NOT NULL,
[BusinessYearWeek] [smallint] NOT NULL,
[LeapYear] [bit] NOT NULL,
CONSTRAINT [PK_DateDim] PRIMARY KEY CLUSTERED
(
[ActualDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Stored procedure to populate the table
CREATE PROCEDURE [dbo].[sp_createTimeDim] AS
--declare variables
DECLARE @DT DATE
DECLARE @YEAR INT
DECLARE @QUARTER INT
DECLARE @MONTH INT
DECLARE @WEEK INT
DECLARE @DayofYear INT
DECLARE @DayofMonth INT
DECLARE @DayofWeek INT
DECLARE @IsWeekend BIT
DECLARE @IsHoliday BIT
DECLARE @CalendarWeek INT
DECLARE @DayName VARCHAR(20)
DECLARE @MonthName VARCHAR(20)
DECLARE @BusinessYearWeek INT
DECLARE @LeapYear BIT

--initialize variables
SELECT @BusinessYearWeek =0
SELECT @CalendarWeek = 1
SELECT @LeapYear =0

--the starting date for the date dimension
SELECT @DT = '2012-01-01'

--start looping, stop at ending date
WHILE (@DT <= '2020-12-31')
BEGIN
--get information about the data
SELECT @IsWeekend =0
SELECT @YEAR = DATEPART (YEAR, @DT)
SELECT @QUARTER = DATEPART (QUARTER, @DT)
SELECT @MONTH = DATEPART (MONTH , @DT)
SELECT @WEEK = DATEPART (WEEK , @DT)
SELECT @DayofYear = DATEPART (DY , @DT)
SELECT @DayofMonth = DATEPART (DAY , @DT)
SELECT @DayofWeek = DATEPART (DW , @DT)

--note if weeknd or not
IF ( @DayofWeek = 1 OR @DayofWeek = 7 )
BEGIN
SELECT @IsWeekend = 1
END

--add 1 every time we start a new week
IF ( @DayofWeek = 1)
BEGIN
SELECT @CalendarWeek = @CalendarWeek +1
END

--add business rule (need to know complete weeks in a year, so a partial week in new year set to 0)
IF ( @DayofWeek != 1 AND @DayofYear = 1)
BEGIN
SELECT @BusinessYearWeek = 0
END

IF ( @DayofWeek = 1)
BEGIN
SELECT @BusinessYearWeek = @BusinessYearWeek +1
END

--add business rule (start counting business weeks with first complete week)
IF (@BusinessYearWeek =53)
BEGIN
SELECT @BusinessYearWeek = 1
END

--check for leap year
IF ((@YEAR % 4 = 0) AND (@YEAR % 100 != 0 OR @YEAR % 400 = 0))
SELECT @LeapYear =1
ELSE SELECT @LeapYear =0

--insert values into Date Dimension table
INSERT dbo.DateDim (ActualDate, [ActualYear], [QuarterOfYear], [MonthOfYear], [WeekofYear], [DayofYear], [DayofMonth], [DayofWeek], IsWeekend, CalendarWeek, BusinessYearWeek, LeapYear, IsHoliday)
VALUES (@DT, @YEAR, @QUARTER, @MONTH, @WEEK, @DayofYear, @DayofMonth, @DayofWeek, @IsWeekend, @CalendarWeek, @BusinessYearWeek, @LeapYear, 0)

--increment the date one day
SELECT @DT = DATEADD(DAY, 1, @DT)
END


GO
The resulting table
ActualDate ActualYear QuarterOfYear MonthOfYear WeekOfYear DayOfYear DayOfMonth DayOfWeek IsWeekend IsHoliday CalendarWeek BusinessYearWeek LeapYear

---------- ----------- ------------- ----------- ----------- --------- ---------- --------- --------- --------- ------------ ---------------- --------
2012-01-01 2012 1 1 1 1 1 1 1 0 2 1 1
2012-01-02 2012 1 1 1 2 2 2 0 0 2 1 1
2012-01-03 2012 1 1 1 3 3 3 0 0 2 1 1
2012-01-04 2012 1 1 1 4 4 4 0 0 2 1 1
2012-01-05 2012 1 1 1 5 5 5 0 0 2 1 1
2012-01-06 2012 1 1 1 6 6 6 0 0 2 1 1
2012-01-07 2012 1 1 1 7 7 7 1 0 2 1 1
2012-01-08 2012 1 1 2 8 8 1 1 0 3 2 1
2012-01-09 2012 1 1 2 9 9 2 0 0 3 2 1
2012-01-10 2012 1 1 2 10 10 3 0 0 3 2 1
2012-01-11 2012 1 1 2 11 11 4 0 0 3 2 1
2012-01-12 2012 1 1 2 12 12 5 0 0 3 2 1