Feeds:
Posts
Comments

Archive for March, 2011

1. Open BIDS, go to control flow

2. Create a variable ResultSetXML of data
type string

3. Drag one “Execute SQL Task”

4. Create a connection to AdventureWorks
database

5. Edit “Execute SQL Task” and use below SQL
to get XML output

SELECT * FROM Person.Contact FOR XML AUTO,ELEMENTS,ROOT(‘Contact’)

6.Go to Result Set tab and use variable ResultSetXML to map like below

7. Now drag a Script Task component

8. Use variable ResultSetXML as ReadOnlyVariables
in Script Task component

9. Now click on design script and use below
code

—————–*————————*——————-*—————-*——————–*———-

Imports
System

 

Imports
System.Data

 

Imports
System.Math

 

Imports
Microsoft.SqlServer.Dts.Runtime

 

Imports
System.Data.SqlClient

 

Imports
System.Text

 

Imports
System.IO

 

Public
Class ScriptMain

 

   
Public Sub
Main()

 

       
Using Out As
StreamWriter = New StreamWriter("C:\Contact.xml")

 

 

           
Out.Write(Dts.Variables("User::ResultSetXML").Value.ToString())

 

 

           
Out.Close()

 

       
End Using

 

       
Dts.TaskResult = Dts.Results.Success

 

   
End Sub

 

End
Class

—————–*————————*——————-*—————-*——————–*———-

 

10. Above will create C:\Contact.xml
file containing XML output

 

 

 

Read Full Post »

 

Create
a dummy or EOF (End of File) as flag to show completion of certain process,
this is very important when you are sharing same FTP location across many Users/Clients

 

One
way is to copy from a source location and rename that file

 

But
same can achieved using below

 

1.
Drag a Execute SQL Task

 

2.
Create a variable BatchID having data type as string

 

3.
Use below SQLin Execute SQL Task

 

DECLARE @DATE DATETIME

 

SET   @DATE =  GETDATE()

 

SELECT      ‘OUTPUTFILENAME’

           
+ CONVERT(VARCHAR(4),DATEPART(YY,@DATE))

           
+ CASE

           
WHEN LEN(CONVERT(VARCHAR(2),DATEPART           
(MM,@DATE)))>1

           
THEN CONVERT(VARCHAR(2),DATEPART         
(MM,@DATE))

           
ELSE

           
‘0’+CONVERT(VARCHAR(2),DATEPART          
(MM,@DATE))

           
END

           
+ CONVERT(VARCHAR(2),DATEPART(DD,@DATE))

           
+‘_’+ CONVERT(VARCHAR(2),DATEPART        
(HH,@DATE))

           
+ CONVERT(VARCHAR(2),DATEPART       (MINUTE,@DATE))

 

 

4.
Use BatchID in Result Set tab of Execute SQL Task to hold value retuned by
above

 

 

5.Drag
a script component and choose BatchID as readonlyvaraibles in script task

 

Then
use below code inside script task

 

 

Imports System

 

Imports System.Data

 

Imports System.Math

 

Imports
Microsoft.SqlServer.Dts.Runtime

 

Imports System.Data.SqlClient

 

Imports System.Text

 

Imports System.IO

 

 

Public Class
ScriptMain

 

   
Public Sub
Main()

 

 

       
Dim BatchID As String

 

       
BatchID = CStr(Dts.Variables("User::BatchID").Value)

       
‘BatchID is ReadOnlyVariable

       
‘Assuming BatchID having value like ‘OUTPUTFILENAME20110317_1752’

 

 

       
Using Out As
StreamWriter = New StreamWriter("C:\" & BatchID & ".eof")

 

           
Out.WriteLine("End of File")

 

           
Out.Close()

 

       
End Using

 

 

       
Dts.TaskResult = Dts.Results.Success

   
End Sub

 

End Class

 

Note: On can rename “eof” in above code with any other type of file depending
upon your requirements

 

 

Read Full Post »

Assume a table having name column in the form of “First Name”
and “LastName”
 separated by space and we have to get First Name from this
column

 

SELECT CHARINDEX (  , ‘FirstName LastName‘)

SELECT
CHARINDEX (  , ‘FirstName ‘)                   /*Spacerigth
hand side*/

SELECT   CHARINDEX (  , ‘FirstName‘)   /*Nospace*/

 

Output

10

10

1

0

 ‘

Now see the detail explanation below for above cases!

 

SELECT RTRIM (LTRIM (LEFT (‘FirstName
LastName‘
,
CHARINDEX
(  , ‘FirstName LastName‘) )))

 

Output

FirstName

This is correct

 

 

SELECT RTRIM (LTRIM (LEFT (FirstName , CHARINDEX (  , FirstName ) )))

Output

FirstName

 This is correct

 

SELECT RTRIM (LTRIM(LEFT(FirstName,CHARINDEX( , FirstName) )))

Output

 This is wrong

 

No output here because CHARINDEX will
return value 0 and there are no characters before this.

So use below:

 

SELECT CASE WHEN CHARINDEX ( , NameColumn) =0

THEN NameColumn

ELSE
RTRIM (LTRIM (LEFT (NameColumn, CHARINDEX ( , NameColumn))))

END

Read Full Post »

 

 

Open BIDS


Create new integration Service Project, create a new
package say “ImportMultipleExcelFiles.dtsx”

 

1. Create a Folder ExcelFiles (C:\ExcelFiles)and then create multiple
Excel files in folder C:\ExcelFiles but make sure all Excel files have identical schema (format) 

 

Assuming all Excel files above contain data in worksheet “Sheet1
with below schema

 

City

Value

AY

2000

BY

3000

CY

4000

 

You can have your own sheet name but keep a note, worksheet
name has to be uniform across all Excel files that contains data to be uploaded.

 

2. Right Click on Control Flow
Window, Select Variables

Add a variable “FileName
at Package Level having data type string

 

 

3. Go to Control Flow add a “For Each Loop Container” Component.

 

Right-click on the Foreach Loop container and select Edit.
Then, Click on Collection “Collection” tab. Assign folder path and file type as
shown below

 

 

Then go to “Variable
Mappings
“ tab and map variable created above like
below

 

 

 

6. Drag a “Data Flow Task “inside
“Foreach Loop Conatainer”, double clickson Data flow task

Drag one “Excel Source” task ,
double click on this to get “Excel Source Editor “ Window

Now choose new and new window will
open , now browse to your first excel file that is C:\ExcelFiles\First.xls

UnderName of the Excel Sheet” on “Excel
Source Editor “, choose Sheet1$

 

7. Now drag “OLE DB Destination”
task  , connect “Excel Source” to “OLE DB Destination”

Point this conenction to your database and create new table or use an existing table

Map both like below

 

8. Go to the Properties
of “Excel Connection Manager”

Expand “Expressions”

Choose “Connection String” property and assign value of Expression like below

“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::FileName] +
“;Extended Properties=\”Excel 8.0;HDR=YES\”;”



9. Click on button “Evaluate Expression” at the bottom left of above window to check for any errors

10.Set property DelayValidation=TRUE on the Data Flow task

10.Execute your Package

 

Read Full Post »

For this scenerio I have considered Foodmart database as an
example  

 

1.Assuming database is backed up earlier either through command
line or SSMS

BACKUP
DATABASE Foodmart TO
DISK=‘C:\Foodmart.bak’

 

2.While trying to restore using below

RESTORE
DATABASE Foodmart FROM
DISK=‘C:\Foodmart.bak’

 

If you are getting below error

 

Msg 3101, Level 16, State 1, Line 1

Exclusive access could not
be obtained because the database is in use.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE
is terminating abnormally.

 

3. To get rid of above error

Set the database in SINGLE_USER mode and forcibly terminate
other connections

ALTER
DATABASE Foodmart

SET
SINGLE_USER With
ROLLBACK IMMEDIATE

 

4.Then try to restore again

RESTORE
DATABASE Foodmart FROM
DISK=‘C:\Foodmart.bak’

 

If restore is Successful then you are done!

 

But if you are getting below error  go to Step 5  else Step 6

 

Msg 3159, Level 16, State 1, Line 1

The tail of the log for the database "Foodmart" has not been backed up.
Use BACKUP LOG
WITH NORECOVERY
to backup the log if it contains work you do not
want to lose.
Use the WITH REPLACE
or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE
is terminating abnormally.

 

 

5.Then take log backup using below command

BACKUP
LOG Foodmart TO
DISK=‘C:\Foodmart.trn’  WITH
NORECOVERY

 

Try to restore again

RESTORE
DATABASE Foodmart FROM
DISK=‘C:\Foodmart.bak’

 

Processed
240 pages for database
‘Foodmart’, file ‘Foodmart’ on file 1.

Processed
2 pages for database
‘Foodmart’, file ‘Foodmart_log’ on file 1.

RESTORE
DATABASE successfully processed 242 pages in 0.290 seconds (6.811
MB/sec).

 

Restore Successful

 

6.
Finally, Set the database back
to MULTI_USER mode

USE [master]

ALTER
DATABASE Foodmart

SET MULTI_USER;

Read Full Post »

1.Check for all running process

 

USE
Master

GO

 

SELECT
SPID,DBID FROM SYSPROCESSES

WHERE
DBID NOT IN (1,2,3,4)

/* Master-1, Tempdb-2, Mode-3, MSDB-4*/

 

AND
SPID >50

/*SQL Server reserves SPID values of 1 to 50 for internal use*/

 

AND
SPID<> @@spid         

/*To exclude current user process*/

 

In my case above Query returns

SPID  DBID

54       16

 

2.To find out which database associated with above dbid

SELECT

NAME
FROM

SYSDATABASES
WHERE DBID=16

 

3.To find out Query associated with above spid

 

DECLARE

@handle
VARBINARY(64)

SELECT

@handle
= sql_handle

FROM
sys.sysprocesses

WHERE
SPID =54

SELECT
text

FROM
sys.dm_exec_sql_text(@handle)

 

4.To kill this process

/*KILL SPID*/

KILL
54

GO

 

You can kill all other running process by using KILL command
like above

 

 

Read Full Post »

Script:

CREATE
FUNCTION dbo.fnReturnTable() RETURNS
@TableOutput TABLE (
A INT ,B INT)

AS

BEGIN

            DECLARE @CNT INT

            DECLARE @INR INT

            SET @CNT=0

            SET @INR=0

            WHILE @CNT <100

            BEGIN

                        INSERT INTO
@TableOutput

                        VALUES (@CNT ,@INR)

                        SET @CNT=@CNT+12

                        SET @INR=@INR+12

            END

            RETURN

END

 

Execute:

SELECT
* FROM dbo.fnReturnTable()

 

 

Output:

A     B

0     0

12    12

24    24

36    36

48    48

60    60

72    72

84    84

96    96

Read Full Post »

Older Posts »

%d bloggers like this: