SSMS : Preventing from Saving Table using designer

Sometime, we wanted to change the table design by renaming the column name, changing the data type and resizing the data type.  After changes are applied and change the save the table, you will get the dialog box saying “Saving is not permitted”. For example, I wanted to change the “Product” table in the “Adventure Works” database. I am changing the Name column from nvarchar(50) to nvarchar(100).

When you try to save you will get the following dialog box.

We have the data in the table. We don’t want to drop and recreate the table for this small change.

Configuration in SSMS:

1. Click on Tools –> Options.
2. Click on Designer. You will design the following screen.
3. Uncheck “Prevent saving changes that requires table re-creation”. Take a look at the following screen.

Now, you could save the table without any problem. If there are any dependency table, the dialog box will appear which warns about affected table, but this one wont harm anything.

Hope this helps someone.

Thanks,

Gowdhaman

Running SSIS package using Script component

We can run the SSIS package in many ways using SSIS package. If you have lot of child package to be called in your master package, We can use the “Execute Package Task”. But if you have to pass some dynamic variables to be passed from the parent package to the child package. We can use the “Script Component” to run the SSIS package. We have Dts.Variables to assign the variables dynamically. Following are Steps.

1. Create SSIS package
2. Drag “Script Component” to control flow.
3. Double click on “Script Component”. You can see “ReadOnlyVariables” and “ReadWriteVariables”. To test, we are selecting two variables.
   1. ChildPackageParam
   2. ChildPkgLocation
4. If you have any parameters to pass, then you can use PackageParam. Otherwise, we don’t need to map package parameter.

Following is the code used to run the SSIS package using the script task.

public void Main()
{
    string pkgLocation = Dts.Variables["ChildPkgLocation"].Value.ToString();

    try
    {
        Package pkg;
        Microsoft.SqlServer.Dts.Runtime.Application app;
        DTSExecResult pkgResults;
        app = new Microsoft.SqlServer.Dts.Runtime.Application();
        pkg = app.LoadPackage(pkgLocation, null);

        pkg.Variables["Varaible1"].Value = Dts.Variables["ChildPackageParam"].Value.ToString();

        pkgResults = pkg.Execute();

        bool isFieragain = false;
        
        Dts.Events.FireInformation(0, "Calling package", String.Format("Package Execution successfull and package path is:[{0}]", pkgLocation), string.Empty, 0, ref isFieragain);

        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        Dts.Events.FireError(0, "Calling package", String.Format("Error in calling your master package:[{0}]", ex.Message), string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

Hope this helps.

Thanks,

Gowdhaman

SSIS : FTP Task–Step by Step

FTP task is used to do file operations using SSIS in our remote ftp. We can see more information about FTP task in technet. Following is the step by step walkthrough on how to configure your FTP task to send a file from local system and remote ftp.

1. Right click on the connection manager and click “New connection”. You will be prompted with the following screen.

2. Select “FTP” and click “Add” button. You need to enter the credentials, if you have any specific ftp portal to be used you can contact your customer or administrator for the following details. In my case, I have configured it locally.
   • Server name
   • Port name [ if they configured any]
   • User Name and password of portal to connect. Please check with your customer about the permission [read / write]. You need to be sure that your going to download files, send the files , deleting the file in your ftp portal and get appropriate permission.
   • Others are optional parameters, if you want to configure about retries and timeout, you can do it here.

3. Drag your “FTP task” into your “control flow”.
4. Click on the “FTP task” to configure. If you want to set package to fail on FTP connection problem, you can set the property “StopOnFailure” to true. Take a look at the following screenshot.

5. Click on the “File Transfer”. Need to mention the local and remote path of the FTP to do the file operations.

• Local parameter, If we are preparing the file information in the variables, we need to set to true for “IsLocalPathVariable” to true. Otherwise create new file connection.
• Operations, Select the required operations you want to do. In my case, I wanted to send my local file to the remote FTP, so I have configured it for “Send files”
• Remote parameter, If we are preparing the file information in variable, we need to set the “IsRemotePathVariable” to false. Otherwise click on the RemotePath, you will be notified the folder structure inside the remote FTP.

Important:

Remote path will not take a full path like “ftp://localhost/<YourFolder>”. So if your using the variable, use it like “/<YourFolder>”.

Testing:

Package looks like follow. I have done task to send a local file to the remote ftp site.

I am sending the files from my local machine to the remote site ftp://localhost/TestXml/. , once I run file is send to the portal. Please take look at the following screenshot

Other implementations:

1. We can send all the files in the folder using the for each loop container and call the FTP task to send the files into remote ftp.
2. We can download the file and move the file to our local environment and use it for the ETL purpose.

Thanks,

Gowdhaman

SSAS- Clearing Cache

To measure the performance of the query. We need to clear the SSAS cache, because the recent result will be stored in the cache memory. So the MDX query will quickly return the result set. To find out the correct timing of the query run. We need to clear the cache. Following is the XMLA query, will just work on the MDX query window.

XMLA Query:

<Batch xmlns="http://schemas.Microsoft.com/analysis services/2003/engine">

   <ClearCache>

      <Object>

         <DatabaseID>Database ID</DatabaseID>

         <CubeID> Cube ID </CubeID>

      </Object>

   </ClearCache>

</Batch>

How to find the Database ID:

1. Click on the Analysis service database name
2. Click on the properties

We can use the same way to fetch the Cube ID from the Cube properties.

Thanks,

Gowdhaman

SSRS Parameter pattern validation

One of our client wanted to validate SSRS parameters.If parameter is “Non-Alphanumeric”, then we need to write the message saying that “You have entered the Non Alphanumeric data [Value entered in the parameter], which is invalid” We have achieved as follows. Otherwise report should return the data properly.

We have added the Text box in the SSRS report body and entered the following expression.

= IIF(Parameters!Param1.Value like "*[a-z][0-9]*" OR Parameters!Param1.Value like "*[0-9][a-z]*",string.Empty,string.Format("You have entered the Non Alphanumeric data [{0}], which is invalid",Parameters!Param1.Value))

Take a look at the following screenshot.

Thanks

Gowdhaman

Microsoft BI projects into the TFS 2010

We don’t have any good source control for our Microsoft BI projects.Business intelligence development studio 2005 and 2008 does not compatible with Team foundation server for maintaining the source control. If you have installed the Team explorer 2008 in our machine and try to connect to the TFS 2010 server. You can add the project server, but we could not connect to the project collection.

We can incorporate BIDS 2008 into TFS and Maintain the source control. You can use the steps from following link from David Elish’s blog

http://sqlserverselect.blogspot.in/2011/12/bids-visual-studio-2008-shell-with-tfs.html

I have followed the above steps and configured our BI projects. Please take look at the following screenshot. We have created the project collection called the “BIVersion” and added three BI projects [SSISDemo, SSRSDemo and SSASDemo].

Did you integrate your project BI project into the TFS?

Thanks,

Gowdhaman

Data Profiling task in SSIS

Data profiler task can be used for the following reasons.

1. To understand the source data

2. To compare data against the previous load and new load

Data profiling task will create the xml file which gives you entire statics data present in SQL server database / tables. We can use only “ADO.NET” connection for profile purpose, so make sure we have moved our source data from different system like Oracle, Flat file and etc into the SQL server staging table.

DataProfileViewer.exe [Location : C:\Program Files\Microsoft SQL Server\100\DTS\Binn] will be used to view the xml file and see the statistics about the database /table.

Advantages:

1. We use the lot of Select statement to validate the data. Like distinct data and NULL ratio and etc. Profiling task does everything and reduces the time for data analysis

2. We can do it for entire database / specific table, so that validation becomes eaiser.

Disadvantage:

1. This is very expensive, because it scan through the entire table / database. So if we have this task in our regular loading process takes lot of time and resources.

How to configure:

AdventureWorks is the source database for AdventureWorksDW2008 database. So we will use the profiler task against the AdventureWorks and see how the data present there.

2. We can use only the file connection[we can also use the xml file path from the variable] for destination, because it will write only the XML file. Please take a look at the following screen.

3. Click on the “Quick Profile” to configure the database / table to see the statistics

4. Create a new connection and select the <All> to see the statistics for the entire database / or you can select single table. I am selecting “Prodction.Product” table for quick example.

5. On the compute, select the necessary profile and click OK.

6. Now run the SSIS package.

7. AdvWork.xml file will have all the information about the “Product” table in AdventureWorks database.

This is the very huge xml file. Seeing in the notepad will not help. So use the following tool see the profiled information.

DataProfileViewer.exe:

You can find DataProfileViewer.exe in the location “C:\Program Files\Microsoft SQL Server\100\DTS\Binn”.

Thanks,

Gowdhaman

SQL server 2008 R2 Best Practice analyzer

To find out the configuration best practices for your SQL server 2008 R2 and 2008, you can use this “Microsoft SQL Server 2008 R2 Best Practices Analyzer(BPA)” using the following link.

http://www.microsoft.com/en-us/download/details.aspx?id=15289

There are two prerequisites, Please download and install prior to install the best practices analyzer. 

1. Microsoft Baseline configuration Analyzer 2.0
2. PowerShell V2.0

Once, you done with installation.

Configuration:

Important note: Check with administrator before you run the following command

SQL server BPA can connect to both local and remote sql servers.

1. Open the Powersheel as an administrator
2. Enter the following command
   “Enable-PSRemoting”
3. Enter “Y” to overwrite the PowerSheel configurations

Scan for Best practice:

1. Open the “Microsoft Baseline configuration Analyzer 2.0” from the start menu.
2. Select “SQL server 2008 R2 BPA”
3. Click on the parameters tab and enter the required parameters and Select the required objects like “Database Engine”, “Analysis Services” ,“Integration Services” and Etc.
4. Wait the for the reports to come.

We can see the Errors and warning with different Components. These information is very useful for configuring you SQL server 2008 R2 and 2008.

following are some use full error messages for your reference.

Error in Engine:

Category: Performance

Source: localhost

Issue: This instance of SQL Server has only one tempdb data file

Impact: Under a heavy load, the tempdb database can become a single point of contention and affect concurrency and performance

Resolution: To reduce contention in the tempdb database, increase the number of tempdb data files, and configure the required startup trace flags.

 

Warning in Analysis Services:

Category: Performance

Source: localhost

Issue: One or more dimensions have all attribute relationships defined as Flexible

Impact: The RelationshipType property determines whether Analysis Services creates rigid or flexible aggregations. After an incremental update on a dimension, flexible aggregations are dropped and must be reprocessed, but rigid aggregations persist

Resolution: Examine the relationship between attributes and define them as Rigid when the relationship between the attributes is not likely to change such as City and State

 

Warning in Integration Services:

Category: Performance

Source: localhost

Issue: The Sysssislog table was found in the master or msdb database

Impact: Integration Services package logging can generate a large volume of lengthy log entries. You may experience decreased performance if you log to a system database, such as the master or msdb database

Resolution: A good practice is to create a separate database for Integration Services logging. You do not have to create the Sysssislog table manually. If the table does not already exist, Integration Services creates this table for you when you select a SQL Server database as your logging destination

 

Thanks,

Gowdhaman

ForEach loop–file enumerator

Foreach File Enumerator

ForEach loop enumerator Iterate through given folder. From the following example, I am going to iterate through location “C:\Users\gowdhdhan\Desktop\SampleData\SrcFiles”. I have mixture of Excel and txt files inside my source the folder.

Files configuration:

Below screenshot, I have highlighted where to configure the file filter.

Following table illustrate the filter configuration.

Filter option	Description
*.*	Searches all the files. Including Excel file and Text file
*.txt	Searches only text file
Sam*.*	Searches all the files which starts with File name “Sam”.
Sam*.txt	Searches only text file which starts with File name “Sam”.
*Data.xls	Searches only excel file which ends with File name “Data”
*Data*.txt	Searches all text files and file name contains “Data”

Retrieve file Name configuration: 

There are three options available for retrieve file configuration. We need to carefully select depend on the requirement. Following table illustrate configuration and output sample.

Configuration	Output sample
Fully qualified	C:\Users\gowdhdhan\Desktop\SampleData\SrcFiles\Sample Data.txt
Name and Extension	Sample Data.txt
Name only	Sample Data

If we are using the file archiving strategy and have extension in another folder then we can use the “Name only” option. Variable mapping should be very important to save the “Fully qualified” path or “Name and Extension” or “Name only”. Following screenshot illustrate variable mapping. Index is always 0 for “ForEach File enumerator”.

Variable mappings and Index usage:

How to test and retrieve what is saved in variable?

We can use the “Script component” to test. We can use “FileName” variable. We can select in ReadOnlyVariables and ReadWriteVariables based on the requirement.

Following code [void main() method] to test the how variable mapping is working.

   1:    public void Main()

   2:          {

   3:              var fileName = Dts.Variables["FileName"].Value.ToString();

   4:   

   5:              var path = string.Format("From FileName variable : [{0}]",fileName);

   6:   

   7:              MessageBox.Show(fileName);

   8:   

   9:              // TODO: Add your code here

  10:              Dts.TaskResult = (int)ScriptResults.Success;

  11:          }

 

running the SSIS package you will get the following screen. It will be iterated based on the number of file. I have given first screen shot.

 

With Fully qualified:

 

 

With Fully qualified:

Name only:

Based on the requirement we can use the “Retrieve file name” and use it accordingly. I will write separate post on archiving file strategy.

Thanks for reading

-Gowdhaman

Extracting ZIP files using SSIS

Sometime client provides many Flat files\Excel files zipped into single zip file. There are many ways to unzip the files. We can use WinZip and WinRar,but they are not free. In this blog, we will see about how to use 7 zip for extracting the data. 7-Zip is free to use and support all compressed format .  It’s a open source, we have used in our customer environment.You can use the following link to download.

http://www.7-zip.org/

Download 7-Zip and install.

I am going to use Sample.zip file in location “C:\Users\gowdhdhan\Desktop\SampleData\SampleZipFiles\” for demo purpose. Execute process task will extract the source files to “C:\Users\gowdhdhan\Desktop\SampleData\SrcFiles” folder.

Execute Progress Task for running 7 Zip

• Create SSIS package and “Execute Progress Task” in control flow.
• Configure “Execute Progress Task” something as follows. (argument “e” stands for extract)

Executable	C:\Program Files\7-Zip\7z.exe
Arguments	e "C:\Users\gowdhdhan\Desktop\SampleData\SampleZipFiles\Sample.zip"
Working directory	C:\Users\gowdhdhan\Desktop\SampleData\SrcFiles

• Click “Ok”

Run the SSIS package. All the files are moved to “C:\Users\gowdhdhan\Desktop\SampleData\SrcFiles” location. Source files are ready and can be used for further processing. I will write about using “ForEach loop container” in separate post.

 

Thanks for reading .

-Gowdhaman

SSIS : Alternate way of sending E-mail and test it locally

We need to send emails on success/failure of any SSIS packages. If you want to test them locally without creating the SMTP Connection or without the internet use the following tool.

Installation and configuration of SMTP4Dev:

smtp4dev is the small utility to create local mail server. Download it from the following location.

http://smtp4dev.codeplex.com/

We can use the Send E-mail task only with port 25. If you can configure smtp4dev in port 25 then use Send E-mail task. otherwise use the following technique.

Mail servers are always looking for PORT 25. Look at the following screenshot[there will be error message saying “Server failure”].

Click on Options and change the port number from 25 to 26. Look at the following screenshot.

Click “Ok”

Click “Start Listening”. You will see the screen as follows.

Creating SSIS package:

Create a SSIS package. In script component, I have used the following code in the Void main() function. I used “emailMsg”  variable for the body. If any changes required on the body of email can be changed outside the script component.

   1:  public void Main()

   2:          {

   3:   

   4:              var emailMsgFromSSIS = Dts.Variables["emailMsg"].Value.ToString();

   5:              SmtpClient client = new SmtpClient("127.0.0.1");

   6:              MailMessage msg = new MailMessage("From@test.com", 

   7:                  "To@test.com", 

   8:                  "Subject", 

   9:                  emailMsgFromSSIS);

  10:   

  11:              msg.IsBodyHtml = true;

  12:   

  13:              client.Credentials = CredentialCache.DefaultNetworkCredentials;

  14:              client.Port = 26;

  15:              client.Send(msg);

  16:   

  17:              // TODO: Add your code here

  18:              Dts.TaskResult = (int)ScriptResults.Success;

  19:          }

 

Now, package look like this.

I run the SSIS package and email message will be popped up by smtp4dev. Click view and you will see the email message[using outlook].

Thanks for reading .

 

-Gowdhaman