Irfan's World

July 31, 2011

How to truncate and load a table from SSIS ?

When you use SSIS for loading data into a database or a data warehouse or a staging database, some times you need to wipe out all the records first and load the new data from the source to target.  This can be accomplished in different ways, but I am going to show you a method that I use very often and may be useful for you and this technique do not have a data flow task and no mappings. 

   Truncate and Load a target table.

My source database is AdventureWorksDW and my target database is SampleDB.  SampleDB is a exact copy of AdventureWorksDW.  I copied the ADW database and renamed it because I need a source and target, as well as I need the same structure of the source tables in target database. WinkingIn this method,  I am going to use a table DimAcount from my source database and target database.

(Please note, I’m assuming some basic understanding of SSIS, so I’m skipping the "how to create a project", etc. stuff and just going to the pertinent parts).

Following are the steps that we need to follow: 

         Step 1: Define connections to Source and Target.
         Step 2: Get the table name and save it in a user variable.   
         Step 3: Truncate the table in Step 2.

         Step 4: Load
the Target table ?

Step 1:
Following is a screen shot that shows the two connections defined in the connection manager for Source and Target databases in my project.
  

image

Step 2:
In order to complete this step, I am going to use an SQL query to get the table name, and then the returned result will be stored in user variable that I declared as vSourceTable of type String.

image 
Add a ‘Execute SQL Task’,  a query is passed to get the “DimAccount” table from source database. This query will return the table name “table_name”, following are the screen shots for the General and Result Set tab.  (renamed this task as Source – Get the table ?)

image


image

Step 3: 
Add another ‘Execute SQL Task’, this task is used to send the Truncate command from SSIS to the target database.  Following are the screen shots for the General and Expressions tab.
(renamed this task as Target – Truncate table)

image image

Step 4:
Add another Execute SQL Task to send an INSERT command (DML statement) to the target database, this will directly load the data from source to target.  This technique will work when there are same no. of columns exists in both source and target tables.  (renamed this task as Target – Load table )

image image

At last the SSIS job will look like below.  Execute the package, At this point, you should be able to successfully execute your package. Upon successful execution, the package will get the table, truncates and loads the target table. 

image

That’s all for now. Hopefully this gives you an idea of how easy to dump the source data into the target database using SSIS.  Cool

July 13, 2011

How to display more than 1000 values in a LOV created in Crystal report or Business View Manager ?

The following resolution involves editing the registry. To increase the maximum number of values available in a dynamic parameter list, you will add a registry key. Contact your Network Services to make changes to the system registry. Using the Registry Editor incorrectly can cause serious problems that may require you to reinstall the Microsoft Windows operating system and whole lot of other required stuff. Use the Registry Editor at your own risk.

It is strongly recommended that you make a backup copy of the registry files (System.dat and User.dat on Win9x/WinXP/2003/2003/Vista/Win 7 computers) before you edit the registry.

   Step 1. Create a registry key LOV under DatabaseOptions in the following path HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.0\Crystal  
       Reports\DatabaseOptions\LOV
.  See the screenshot below:

image

 

   Step 2. Add a string value "MaxRowsetRecords" and set the value to the maximum number of values that you desire for your report. For example, a value of 3000 will return up to 
      3000 values in the lowest level of a cascading LOV parameter. In the above screen a value of 60000 is set as it was required in the environment and it works perfectly fine.

The value 0 (Unlimited) will not work with BusinessObjects Enterprise XI or Crystal Reports Server XI, you must specify another value.

NOTE: After making changes to the registry, restart the affected service or application as required.  

Blog at WordPress.com.