Skip to main content

Posts

Showing posts from May, 2012

RUN SSIS PACKAGES IN COMMAND PROMPT

There are many ways to run packages. One is straight method is to run from BIDS. and other method is to run package from  command line Utility. Open programs -> Run -> cmd. In CMD window, type C:/>DTEXEC.exe "C:\Documents and Settings\swami\My Documents\Visual Studio 2008\Projects\just try\just try\project.dtsx" When you run above command it will give you start time, end time and total progress of the package as well. There are various options of the DTEXEC available you can see that using DTEXEC.exe /?

REPLICATEand REVERSE Function

REPLICATE : Replicate is used to replicate values as number of times we need to wish to appear. Syntax:   REPLICATE ("string that you want TO append" ,"INTEGER VALUE" ) Example : If I want SQL word 5 times, then SELECT REPLICATE ('SQL', 5) The result will be as shown below :    REVERSE : If we need to reverse a string value, We can use Reverse function for such operations.   Syntax: REVERSE ("STRING WE NEED TO REVERSE") Example:   SELECT REVERSE ('DATABASE') The output is shown in snippet

Database Mail

There are two ways that you can setup Database Mail, either by using the stored procedures that are included with SQL Server 2008 or by using SQL Server Management Studio. Once we can configure Database Mail, we can following store procedure or SSMS to activate database mail tasks. Configure and sending using SSMS is shown in this   Link1 or Link2 Store procedure is given as follows:  exec msdb.dbo.sp_send_dbmail @profile_name = 'ProfileName' in our case 'dhinakaran' @recipients = 'Client Email Address' , @blind_copy_recipients = 'BCC Address', @subject = 'Subject', @BODY = 'Message Body', @body_format = 'Message Type', it could be text OR html 

How to move multiple files in ssis and also rename simultaneously

There are two ways to achieve this. 1) We can move the flat files and then rename it. 2) While moving files itself, automatic rename should be done. We will do the second type. The criteria is to rename the files while moving from source to destination. So for that, we need FILE SYSTEM TASK to be included. Secondly since we need to move many files, we will use FOR EACH LOOP CONTAINER. To fetch all the files, we can use FOR EACH LOOP task in SSIS. In collection tab, we can select FOREACH FILE enumerator option for fetching files and we can change enumerator configuration Folder option: Points to source where we need to fetch files. Files: will give us idea whether we need to fetch all the files (*.*) or if we give extension like *.txt, it is going to fetch only  .txt files . Once I give Source name in FOR EACH LOOP container, It is going to fetch all the files corresponding to that path. Retrieve file name: This option is used to let the variables mentioned in V...

Dimension tables- Fundamental of Datawarehouse

Dimension table contain descriptive  information. Types of dimensions There are three types of dimensions Junk dimension Conformed dimension SCD or CDC dimension(Slowly changing dimension or Changed data capture) Junk Dimension: Data’s which are not useful for clients is called junk dimension. Ex: Flags, Row inserted by user etc Conformed dimension: If dimension is connected with multiple facts then it is called as conformed dimension.  Ex: Time SCD or CDC: Example: If say a person is changed from BTM to Silk board the changed values is inserted in the table. If anything is changed in the source that values, we are loading in to the target this is known as SCD or CDC. CRITICAL COLUMN : If values are changing according to the time such columns is called critical column. Types of SCD: SCD1 or TYPE1: Here only current value will be present in the dimension table. Here in SCD1 is going to overwrite on previous values. ...