Uncategorized

SSIS – Pass a date variable to a OLE DB Source in a Data Flow

February 28, 2012 by alirazazaidi

Currently i have to make query dynamic, i have to pass date variable to query to execute for specific date range, and this date range could be very time by time, Previously I tried for script component or expression of variable to build query but failed, but then I found command option of oledb command. I placed query there with “?” sign at places where my parameter will replace the date value . Consider following

When I passed query there Parameter button on wizard activate. I set there variable to parameter .

This approach is work fine for me, but if you have places are more then 3 to 4 , query parsing failed. so you have to write query in such a way that minimum number of places parameter are used.

Delete with identity reset

February 27, 2012 by alirazazaidi

I am dropping the rows but insertion start with next to last max identity . Something was missing in my knowledge. To reset Identity i have use one extra statement, other option is use trunc statement to drop rows. but if i have too drop rows with delete statement and reset the identity what should do, i have to use

DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

This will reset identity back to 1. for example

DELETE from tblstudent;


DBCC CHECKIDENT("tblstudent, RESEED, 0)

How to convert date to interger In TSQL

February 27, 2012 by alirazazaidi

During my assignment I have to generate Integer value from datetime filed for Date Dimension . I found excellent id this way.

CAST(CONVERT(varchar(8),StartDATE,112) AS int) DateDateKey,

The whole Query is as follow.

SELECT
CAST(CONVERT(varchar(8),BEGDATE,112) AS int) DateDateKey,
,Name
,Address
,Joindate
,EndDate
FROM dbo.from Student

🙂 its works for me.

How to get site visitor Api in asp.net

February 25, 2012 by alirazazaidi

How to get IP Host Address of remote Client

// To get IP of the client meachine
// There is two methods to obtain IP address

// Method 1
//     Gets the IP host address of the remote client.
// Returns:
//     The IP address of the remote client.
string ipMethod1 = Request.UserHostAddress; // HttpContext.Current.Request.UserHostAddress;

//Methord 2
//Request.ServerVariables is a Name Value Collection
//     Gets a collection of Web server variables.
// Returns:
//     A System.Collections.Specialized.NameValueCollection of server variables.
string ipMethod2 = Request.ServerVariables["REMOTE_ADDR"]; //HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"];
Response.Write(ipAdddress);

How to create dynamic connection string with variables SSIS

February 22, 2012 by alirazazaidi

Create Parameter on package level with string datatype with following Name, set default value with respect to your machine configuration I set according to mine

VServerName =”pc-aliraza”

VSQLUserName=”aliraza”

VSQLDbName =”Nwind”

VSQLPassword =”123”

If integrated securtity with database is false or you connect with windows authentication following is the expression you have to set expression at connection string property.

"Data Source=" + @[User::VServerName]  + ";Initial Catalog=" + @[User:: VSQLDbName]   + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;"

If you want to connect with database with Sql server authentication you have to use following expression string at expression of connection string at connection in SSIS.

"Data Source="+ @[User::VServerName] +";User ID="+ @[User::VSQLUserName]  +"Password= "+ @[User::VSQLPassword] +" ;Initial Catalog=" + @[User::VSQLDbName] + ";Provider=SQLNCLI10.1;Persist Security Info=True;"

how to get lastest inserted identity working with access database

February 20, 2012 by alirazazaidi

You know the word “@@IDENTITY” it really has magic, It returns me the latest inserted rows identity in my access database using oldebcommand. For this purpose i have to execute command object two times first for insert query and second time by execute secular for getting value back

public void InsertReviews(string _Nick, string _Name, string _EmailAddress, string _RealStateTitle, string _Address, string _City, string _ZipCode, string _Country, string _Comments, string _FromIP, string _Status, string _Langi, string _Lit)
{
string ConnString = Util.GetConnString();
string SqlString = "Insert Into ClientInfo (Nick,Name,EmailAddress,RealStateTitle,Address,City,ZipCode,Country,Comments,FromIP,Status,Langi,Lit) Values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
  string SqlString2 = "Select @@Identity";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("Nick", _Nick);
cmd.Parameters.AddWithValue("Name", _Name);
cmd.Parameters.AddWithValue("EmailAddress", _EmailAddress);
cmd.Parameters.AddWithValue("RealStateTitle", _RealStateTitle);
cmd.Parameters.AddWithValue("Address", _Address);
cmd.Parameters.AddWithValue("City", _City);
cmd.Parameters.AddWithValue("ZipCode", _ZipCode);
cmd.Parameters.AddWithValue("Country", _Country);
cmd.Parameters.AddWithValue("Comments", _Comments);
cmd.Parameters.AddWithValue("FromIP", _FromIP);
cmd.Parameters.AddWithValue("Status", _Status);
cmd.Parameters.AddWithValue("Langi", _Langi);
cmd.Parameters.AddWithValue("Lit", _Lit);
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = SqlString2;
  int _Count = (int) cmd.ExecuteScalar();
}
}

}

Its working for me

Simple Data Access Class for Access database

February 19, 2012 by alirazazaidi

After little time i have to wrote a simple data access layer for MS Access database,

For Connection string I wrote a simple Util Class as

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration; 

/// <summary>
/// Summary description for Util
/// </summary>
public static  class Util
{

    public static string GetConnString()
    {
        return WebConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;
    }
}

Then i Wrote another class where Insert , update ,delete and simple select method placed as

Insert method

 public void InsertReviews(string _Nick, string _Name, string _EmailAddress, string _RealStateTitle, string _Address, string _City, string _ZipCode, string _Country, string _Comments, string _FromIP, string _Status, string _Langi, string _Lit)
    {
        string ConnString = Util.GetConnString();
        string SqlString = "Insert Into ClientInfo (Nick,Name,EmailAddress,RealStateTitle,Address,City,ZipCode,Country,Comments,FromIP,Status,Langi,Lit) Values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("Nick", _Nick);
                cmd.Parameters.AddWithValue("Name", _Name);
                cmd.Parameters.AddWithValue("EmailAddress", _EmailAddress);
                cmd.Parameters.AddWithValue("RealStateTitle", _RealStateTitle);
                cmd.Parameters.AddWithValue("Address", _Address);
                cmd.Parameters.AddWithValue("City", _City);
                cmd.Parameters.AddWithValue("ZipCode", _ZipCode);
                cmd.Parameters.AddWithValue("Country", _Country);
                cmd.Parameters.AddWithValue("Comments", _Comments);
                cmd.Parameters.AddWithValue("FromIP", _FromIP);
                cmd.Parameters.AddWithValue("Status", _Status);
                cmd.Parameters.AddWithValue("Langi", _Langi);
                cmd.Parameters.AddWithValue("Lit", _Lit);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

    }

Update Method

  public void UpdateReviews(int _ID,string _Nick, string _Name, string _EmailAddress, string _RealStateTitle, string _Address, string _City, string _ZipCode, string _Country, string _Comments, string _FromIP, string _Status, string _Langi, string _Lit)
    {
        string ConnString = Util.GetConnString();
        string SqlString = "Update ClientInfo Set Nick=?,Name=?,EmailAddress=?,RealStateTitle=?,Address=?,City=?,ZipCode=?,Country=?,Comments=?,FromIP=?,Status=?,Langi=?,Lit=? where IDs=?";
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("Nick", _Nick);
                cmd.Parameters.AddWithValue("Name", _Name);
                cmd.Parameters.AddWithValue("EmailAddress", _EmailAddress);
                cmd.Parameters.AddWithValue("RealStateTitle", _RealStateTitle);
                cmd.Parameters.AddWithValue("Address", _Address);
                cmd.Parameters.AddWithValue("City", _City);
                cmd.Parameters.AddWithValue("ZipCode", _ZipCode);
                cmd.Parameters.AddWithValue("Country", _Country);
                cmd.Parameters.AddWithValue("Comments", _Comments);
                cmd.Parameters.AddWithValue("FromIP", _FromIP);
                cmd.Parameters.AddWithValue("Status", _Status);
                cmd.Parameters.AddWithValue("Langi", _Langi);
                cmd.Parameters.AddWithValue("Lit", _Lit);
                cmd.Parameters.AddWithValue("IDs", _ID);
               conn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }

Delete Method

 public void DelteReviews(int _ID)
    {
        string ConnString = Util.GetConnString();
        string SqlString = "Delete * From ClientInfo where IDs=?";
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("IDs", _ID);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

    }

Select Method as

 public DataSet SelectReviews(int _ID)
    {
        string ConnString = Util.GetConnString();
        string SqlString = "Select * From ClientInfo where IDs=?";
        DataSet _ds = new DataSet();
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("IDs", _ID);
                OleDbDataAdapter _Adp = new OleDbDataAdapter();
                _Adp.SelectCommand = cmd;
                _Adp.Fill(_ds);
                _Adp.Dispose();
                conn.Open();
            }

        }
        return _ds;
    }

Chears 🙂

Using a Common Table Expression (CTE) with an Delete statement

February 14, 2012 by alirazazaidi

Delete Part of ETL Process

If any row is deleted at source table, but destination table rows exits. So I did to make a inner join with source table with destination table I used right join in this case, so I got all rows from destination which I placed at right side of query. The result set I got with null at left side form source table. I filter the result set and got all the Keys which have null at source table. And call delete at destination on filter keys.

My common table Expression for Delete will be as follow

with DeleteRows as

(

SELECT     sourceEmp.empid, DestEmployee.SourceEmpId

FROM         TSQLFundamentals2008.HR.Employees sourceEmp right join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee

on

sourceEmp.empid = DestEmployee.SourceEmpId

)

--select SourceEmpId from DeleteRows where empid is null

delete from TSQLFundamentals2008DW.HR.DimEmployees

where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId in (

select SourceEmpId from DeleteRows where empid is null)

How to create SSIS Package Configuration in SQL server 2008

February 7, 2012 by alirazazaidi

There will be change possible of server name at connection strings, file paths when deploying SSIS packages in production and same issue appears when ssis package will go from development environment to QA server for testing. So what is workaround. SSIS configuration wizard allow us to generate configuration settings for connection string and properties of other objects. And this allows us to update these settings at run time at any place, dev, QA or Production.

Benfites

This way we can resolve the connection strings on runtime.
Easily update the application on different server without redeploying the application.
Change the behavior of Package at runtime, by update the configuration settings of variables.

Let see how we can do this

Open the package for which you want to generation configuration

Check the enable button.

Click on enable button. To start Wizard

From the above configuration type dialogue box, Specify the configuration type and then set the property types relevant to the configuration type.

Configuration type source can be

XML Configuration file
Environment Variable
Registry Entry
SQL server

Specify the configuration file name and then say next then you will get the following dialogue box
Select the object and properties you want in configuration file.

Press next and then press finish button.

You can open the created file with extension “dtsConfig” in notepad, notepad++ , visual studio or any text editor supports xml to update the required filed.

Configurations are included in when you create package deployment utility for Installing packages.

BizTalk Interview Advice

February 5, 2012 by alirazazaidi

Excellent article from Seroter.

http://seroter.wordpress.com/2007/07/27/biztalk-interview-advice/

« Previous Page