Ambidexterious

Wednesday, April 11, 2012

Script Task in SSIS

Abstract
This article demonstrates creating a SQL Server Integration Services package that imports a text file into SQL Server database table using a Script Task component.
Requirements

Article
We begin by launching Microsoft Visual Studio and create a new Integration Services Project which is located under Business Intelligence Projects category. After you have assigned a project name, proceed to click and drag the Script Task in Control Flow pane of the package's toolbox. Right click the script task and click on "Edit" Under the Script Task Editor change the "ScriptLanguage" to "Microsoft Visual C# 2008".
In Project Explorer, ensure the following references are added:

Back to the code window, ensure that the following namespaces are declared:

using (StreamReader file = new StreamReader(@"C:\Temp\flat_src.txt"))
{
 try
    {
    while ((line = file.ReadLine()) != null)
   {
     char[] delimiters = new char[] { '|' };
     string[] parts = line.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
     
        for (int i = 0; i < parts.Length; i++)
 {
    RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol 
    = connectionFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;
    sS_AssignColumnProperties(flatFileCol, parts[i], "|");
 }
     //Exit file after reading the first line
     break;
   }                
  }
 catch (Exception ex)
    {
     throw ex;
    }
 finally
    {
     file.Close();
    }
}

Execute the ssis multiple ssis package using the T-Sql

If you want to execute a set of SSIS packages in SQL Server 2008 or 2005, you can do this using T-SQL. First you will need a table with all of your package names on it. Then a While loop to execute each package.
Here is the example code:

Declare @FilePath varchar(2000)
Declare @cmd varchar(2000)

DECLARE @package_name varchar(200)
Declare @PackageCount int
Declare @X int
Set @X = 1
Set @PackageCount = (Select COUNT(*) from Packages)
set @FilePath = 'C:\Package Path'
While (@X <= @PackageCount)
Begin

    With PackageList as
    (
    Select PackageName, Row_Number() Over(Order by PackageName) as Rownum
    From Packages
    )
    SELECT @package_name = PackageName
    FROM PackageList
    Where Rownum = @X

    select @cmd = 'DTExec /F "' + @FilePath + @Package_name + '"'

    print @cmd

    Set @X = @X + 1

    exec master..xp_cmdshell @cmd

End
In the new version of SSIS 2012 you will be able to launch packages with T-SQL Natively.

Thursday, February 16, 2012

How to Protect the Folder

Create a new folder and name it whatever you would like.

Open the folder, right-click on a blank area in it, then select New -> Text Document from the pop-up menu.

Open the text file you just created by double-clicking it and copy/paste in the following text:
cls
@ECHO OFF
title Folder Private
if EXIST "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}" goto UNLOCK
if NOT EXIST Private goto MDLOCKER
:CONFIRM
echo Are you sure you want to lock the folder(Y/N)
set/p "cho=>"
if %cho%==Y goto LOCK
if %cho%==y goto LOCK
if %cho%==n goto END
if %cho%==N goto END
echo Invalid choice.
goto CONFIRM
:LOCK
ren Private "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"
attrib +h +s "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"
echo Folder locked
goto End
:UNLOCK
echo Enter password to unlock folder
set/p "pass=>"
if NOT %pass%== PASSWORD_GOES_HERE goto FAIL
attrib -h -s "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"
ren "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}" Private
echo Folder Unlocked successfully
goto End
:FAIL
echo Invalid password
goto end
:MDLOCKER
md Private
echo Private created successfully
goto End
:End
In the above code, replace the key PASSWORD_GOES_HERE with the password you want to use to unlock the folder. For example if you want the password to be123456, the line should look like:
if NOT %pass%== 123456 goto FAIL
Save your new file in the .bat format with the complete file name being locker.bat. To do this, make sure to change the Save as type: to All Files (*.*).

click to enlarge

In the folder you created back in Step #1, double click the locker.bat file and there will now be a new folder named Private where you can put anything you want.

Upon exiting, double click the locker.bat file again. It will prompt you to answer whether you want to lock your folder or not. Press Y and the private folder will disappear.

click to enlarge

In order to retrieve the Private folder, all you have to do is double click thelocker.bat file and enter the password which you set in Step #4 and the folder will appear again for you to access.

click to enlarge

That’s it!

Wednesday, January 25, 2012

Important Question

differenct b/w the subquery and join

why we use stored proc

filter index

staging server

Bcp-Bulk copy program (
links

http://www.codeproject.com/Articles/16922/SQL-Bulk-Copy-with-C-Net
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
http://msdn.microsoft.com/en-us/library/aa196743(v=sql.80).aspx
http://www.dotnetcurry.com/ShowArticle.aspx?ID=323
http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file
)

**************************

Inside the function we can't handle the
exception and can't use the transactions.

by using the select statement we can use the function.

Inside the function we can't write the insert or update command.

Inside the function we can't call the stored Procedure.

Inside the Procedure the exception handling and transaction .
Procedure.

Monday, January 23, 2012

Difference Between Variable and Property:

Variables and Propertires both represent values that you can access.However, there are differences in storage and implementation.

Variables: A variable corresponds directly to a memory location. you define a variable with a single declaration statement. A variable can be a local variable, defined inside a procedure and available only within that procedure,
or it can be a member variable, defined in a module, class, or structure but not inside any procedure .
a member variable is also called a field.

Properties: A property is a data element defined on a module, class, or structure. you define a property with a code block between the Property and End Property Statements.
The code block contain the Get Procedure, a Set procedure, or both.
These procedures are called property procedures or Property accessors.

*****

Imp Difference B/W the Variable and Properties:

Point of Difference Variable Property

1. Declaration single declaration series of statements in a code block statement

2. Implementation Single storage Executable code (property procedures)
location

3. storage Directly associated typically has internal storage not available outside the
with variables property's conaining class or module with variable's.
value property's value might or might not exist as a stored element.

4. Executable code none Must have at least one procedure

5. Read and write access read/Write or read only read/write, read-only, or write only

6.Custom actions (in addition not possible ) can be performed as part of setting or
retrieving property value.to acception or returning value)

Examples:::

public class Car
{

    int speed; //Is this sufficient enough if Car will only set and get it.

    public Car(int initialSpeed)
    {
        speed = initialSpeed;
    }

    //Is this actually necessary, is it only for setting and getting the member
        //variable or does it add some benefit to it, such as caching and if so,
        //how does caching work with properties.
    public int Speed 
    {
        get{return speed;}
        set{speed = value;}
    }

        //Which is better?
        public void MultiplySpeed(int multiply)
        {
            speed = speed * multiply; //Line 1
            this.Speed = this.Speed * multiply; //Line 2

            //Change speed value many times
            speed = speed + speed + speed;
            speed = speed * speed;
            speed = speed / 3;
            speed = speed - 4;

        }
}


1 - Fields can’t be used in Interfaces
You can’t enforce the existence of a field in an object’s public contract through an interface. For properties though it works fine.
2 - Validation
While your application currently may not require any validation logic to set a particular value, changing business requirements may require inserting this logic later. At that point changing a field to a property is a breaking change for consumers of your API. (For example if someone was inspecting your class via reflection).
3 - Binary Serialization
Changing a field to a property is a breaking change if you’re using binary serialization. Incidentally, this is one of the reasons VB10’s auto-implemented properties have a “bindable” backing field (i.e. you can express the name of the backing field in code) – that way, if you change an auto-implemented property to an expanded property, you can still maintain serialization compatibility by keeping the backing field name the same (in C# you’re forced to change it because it generates backing fields with unbindable names).
4 - A lot of the .NET databinding infrastructure binds to properties but not fields
I’ve heard arguments on both sides as to whether or not that’s a good thing, but the reality is that’s the way it works right now. (Note from me: WPF bindings work on properties)
5 - Exposing a public field is an FxCop violation
For many of the reasons listed above :)
http://www.codinghorror.com/blog/2006/08/properties-vs-public-variables.html

http://blogs.msdn.com/b/vbteam/archive/2009/09/04/properties-vs-fields-why-does-it-matter-jonathan-aneja.aspx

Ambidexterious

Wednesday, April 11, 2012

Script Task in SSIS

Execute the ssis multiple ssis package using the T-Sql

Thursday, February 16, 2012

How to Protect the Folder

Wednesday, January 25, 2012

Important Question

Monday, January 23, 2012

Difference Between Variable and Property:

Qualcomm Short Term

Total Pageviews

Search This Blog