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