SQL SERVER: SSIS - Look Up Transformation Task

Today, I am going to give basic example of Lookup Transformation Task in SSIS. Lookup transformation performs lookup operation by joining data in input columns with reference table dataset columns.Lookup can be used to access addition information from the reference dataset based on the matching criteria Reference dataset can be OLEDB table, Excel file or cache file, or SQL query result. Use of Look up Transformation: In my source system (table), I have all the product with their details. Somehow I have products which belongs to the country which doesn’t exist in my reference (master) table. I assigned a job to rectify those products. I need to design ETL which gives me those records whenever we import products to our target database (table). So here, I am going to use “Lookup no match output” to capture those records by following steps: Let’s take an example to easily understand how to use Lookup Transformation in SSIS. 1. Create Source Connection: Select and drag “Data Flow Task”, from “Control Flow Items” to designer surface. Then double click it and Create a New OLEDB connection. 2. Create sample tables Now we will create tables named ‘LKP_Countries_Source’ and ‘LKP_Countries’ into Test Database from the given script. 1: CREATE TABLE [LKP_Countries_Source] 2: ( 3: [CountryCode] [int] NULL, 4: [CountryName] [varchar](100) NULL 5: ) 6: GO 7: 8: INSERT INTO [LKP_Countries_Source] 9: ( [CountryCode] 10: ,[CountryName]) 11: VALUES 12: (91, N'India') 13: ,(92, N'Pakistan') 14: ,(93, N'Afghanistan') 15: ,(94, N'Sri Lanka') 16: ,(95, N'Myanmar') 17: ,(960, N'Maldives') 18: ,(961, N'Lebanon') 19: ,(962, N'Jordan') 20: ,(963, N'Syrian Arab Republic') 21: ,(964, N'Iraq') 22: ,(965, N'Kuwait') 23: ,(966, N'Saudi Arabia') 24: ,(967, N'Yemen') 25: ,(968, N'Oman') 26: ,(971, N'United Arab Emirates') 27: ,(972, N'Israel') 28: ,(1, N'USA') 29: ,(65, N'Singapore') 30: GO 1: CREATE TABLE [LKP_Countries] 2: ( 3: [Country] [varchar](100) NULL 4: ,[Code] [int] NULL 5: ) 6: GO 7: 8: INSERT [LKP_Countries] 9: ( 10: [Country] 11: ,[Code] 12: ) 13: VALUES 14: (N'INDIA', 91) 15: ,(N'SINGAPORE', 65) 16: ,(N'USA', 1) 17: ,(N'PAKISTAN', 92) 18: GO 3. Create Lookup connection: Now you Need to select the proper OLEDB connection in “Connection Manager” tab and the source table for lookup task.    4. Columns Selection from Source Table: Select the columns to use as output columns.   5. Lookup Transformation Editor: Here, I have added “Lookup Data Transformation” Task to designer tab and click on edit to configure the Lookup transformation. 6. Handle No Match output: Now we need to configure various sections in the “Lookup Transformation Editor”. In General section, select “Redirect rows to no match output” to handle the unmatched data from the lookup task.  Here we will need to select Cache mode as “Full Cache”. This option is used to improve the performance while handling large scale of data. Keep connection type as “OLEDB Connection Manager”, as we are using OLEDB source. When you use Cache File as data source then you will need to select “Cache Connection Manager” Last option provide various ways in which not matched data can be handled. Ignore failure – ignores the failure and executes the next task. Redirect rows to error output – moves the not matched rows to red output to handle them separately. Fail component – throws an exception and stops processing further tasks. Redirect rows to no match output – switches rows to the secondary output, and user can handle it differently to matching data 7. Set Connection Manager for Lookup table: In Connection section select the reference table with proper connection. This list will get compared with source dataset for matching the data. 8. Column mapping for Lookup table: In Columns section, select the available input columns and map it with the available lookup columns. This will create a join between 2 source datasets.We have used “Full Cache Mode”, so Advanced section will be disabled and in “Error Output” keep fields as it is. 9. Input Output Selection Setup: Select new “OLEDB Destination” transformation and drag it to the designer surface. Drag green arrow from “Lookup Transformation” Task to “OLEDB Destination” and provide “Lookup Match Output” as Output and click OK. 10. Create Output table for Match and Not Matched Data: Now we will need to create output tables to store the matched as well as not matched result. 1: CREATE TABLE [LKP_Output_Match] 2: ( 3: [CountryCode] INT, 4: [CountryName] VARCHAR(100), 5: [Country_Calling_Code] INT 6: ) 7: GO 8: 9: CREATE TABLE [LKP_Output_NO_Match] 10: ( 11: [CountryCode] INT NULL, 12: [CountryName] VARCHAR(100) NULL, 13: ) 14: GO 11. Data Mapping for Result table: Now I need to provide mapping for the Output Table to store the Matched Data. 12. Complete Data Flow for Lookup Transformation: In order to handle the “Not Matched data”, provide the link of Not Matched data to OLEDB Destination table “LKP_Output_No_Match”. It will store the not matched results.   13. Package Execution and Result: Now let’s execute the Package and check with the output inside the tables we have created to store the result as in “LKP_Output_Match” table for Matched Data and “LKP_Output_NO_Match” table for Not Matched Data. 1: SELECT * FROM LKP_Output_Match 2: 3: SELECT * FROM LKP_Output_NO_Match 4: GO Result :  Lookup Transformation can be used in various ways according to the requirements and can be implemented accordingly. This was just an understanding document for Lookup Transformation. Reference: Tejas Shah (www.SQLYoga.com)

Read More

T-SQL to find out Space by File Group

T-SQL script to find out SQL SERVER FileGroup space details:

SELECT
 fg.groupname AS 'File Group',
 Name, 
 CONVERT (Decimal(15,2),ROUND(f.Size/128.000,2)) 
  [Currently Allocated Space (MB)],
 CONVERT (Decimal(15,2),
 ROUND(FILEPROPERTY(f.Name,'SpaceUsed')/128.000,2))
  AS [Space Used (MB)],
 CONVERT (Decimal(15,2),
 ROUND((f.Size-FILEPROPERTY(f.Name,'SpaceUsed'))/128.000,2))
  AS [Available Space (MB)]
FROM dbo.sysfiles f (NOLOCK)
JOIN sysfilegroups fg (NOLOCK) ON f.groupid = fg.groupid
ORDER BY fg.groupname

Read More

SQL SERVER: T-SQL to read all attributes of the XML

We have reviewed how to read XML with SQL server in this Post, and how to read XML with Namespace in this POST. Today I would like to share how to read all attributes of the XML and have result in Table format.

 

Recently, my team assigned one requirement to import XML file. To read an XML file is achieved by following this POST but here, we have a requirement to read each Attribute name and Attribute Value to store that info in normalized table by comparing Attribute Name. Let me explain with example.

 

XML:

 

Expected Result:

 

Query to achieve the same:

DECLARE @XML AS XML

SELECT @XML = ''

SELECT CAST(x.v.query('local-name(.)') AS VARCHAR(100)) AS AttributeName
 ,x.v.value('.', 'VARCHAR(100)') AttributeValue
FROM @XML.nodes('//@*') x(v)
ORDER BY AttributeName

Here, We have used “@*”, which gives us way to read the attributes for the ROOT tag and “local-name” gives is the Attribute name. That’s it.

 

Reference: Tejas Shah ( www.SQLYoga.com )

Read More

SQL Yoga: Parse XML with namespace with SQL SERVER

Recently, we were working on XML having namespace and we need to read that XML to fetch the information from the XML. Here, is the Sample XML which we are trying to parse: DECLARE @x XML SELECT @x = '     <X                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"                 xmlns:xsd="http://www.w3.org/2001/XMLSchema"                 xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1">         <info>Sample XML TEST</info>            </X>              ' If we need to fetch the “info” element with T-SQL we can write query as follows: ;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/search/local/ws/rest/v1')     SELECT  x.v.value('info[1]','VARCHAR(50)')     FROM    @x.nodes('/X') x (v) Here, we have to use “WITH XMLNAMESPACES”, so we can specify the namespace of the XML that we are reading, else it wont give us any result.

Read More

SQL Yoga – Read XML with T-SQL statement in SQL SERVER

In my previous post, “XML Result sets with SQL Server”, we review to generate result sets in XML from SQL server. Then I got a comment from the team, to also have post to read XML in SQL Server. To read XML in SQL server, is also simple. Lets read the XML which is created by XML PATH in previous post. Read XML Elements with T-SQL: DECLARE @SQLYoga TABLE(     ID INT IDENTITY,     Data VARCHAR(50),     CreatedDate DATETIME DEFAULT(GETDATE()),     CreatedBy INT     ) INSERT INTO @SQLYoga(Data) SELECT 'SQLYoga' UNION ALL SELECT 'Tejas Shah' UNION ALL SELECT 'Generate XML' DECLARE @xml XML SELECT @xml = (         SELECT    *         FROM    @SQLYoga         FOR XML PATH('Record'), ROOT('Records')     ) SELECT @xml Now, please find query to read the query to read XML generated above: SELECT         x.v.value('ID[1]', 'INT') AS ID,         x.v.value('Data[1]', 'VARCHAR(50)') As Data,         x.v.value('CreatedDate[1]', 'DATETIME') AS CreatedDate FROM    @xml.nodes('/Records/Record') x(v) This query generates the output as follows: That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this. Reference: Tejas Shah (www.SQLYoga.com)

Read More

SQLYoga – XML Result sets with SQL Server

Recently, found that people making much complex code to have an XML in application. I have found that they return result set in Table to the application and have applied code to convert that data table to XML by Asp. Net application. When I review the process, I have suggested that why don’t you use the XML feature of the application. They surprised the simple use of it and make their life easy. Let me have sample query to generate XML result sets in SQL Server database. There are two ways to have an XML from query: 1. FOR XML RAW 2. FOR XML PATH Please find following sample queries where both of the way are being used: Lets create sample data DECLARE @SQLYoga TABLE(     ID INT IDENTITY,     Data VARCHAR(50),     CreatedDate DATETIME DEFAULT(GETDATE()),     CreatedBy INT     ) INSERT INTO @SQLYoga(Data) SELECT 'SQLYoga' UNION ALL SELECT 'Tejas Shah' UNION ALL SELECT 'Generate XML' Generate XML with XML ROW SELECT * FROM @SQLYoga FOR XML RAW, ROOT('Records') XML RAW, returns the XML by each record with row element and all columns as attributes in the XML. Generate XML with XML PATH SELECT    * FROM    @SQLYoga FOR XML PATH('Record'), ROOT('Records') XML PATH, returns the XML by each record with separate element and also columns as element within respected element of the record. That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this. Reference: Tejas Shah (www.SQLYoga.com)

Read More

SQL SERVER: Backup the database and upload it on FTP and on Network

Recently one of my friend called me and asked me that his MS SQL SERVER Database server is crashed and the problem is, he has the database backups on the same server. So now as the server is crashed, and he cannot have any backup to restore the database. I suggested him, first get data recovery tool to get the data from crashed server and then restore it to make it work, so application will be up. Then, to overcome this kind of problem in future, I suggested him to always keep the database backup on another server and it is best to have database back up on another location too. He agreed with me that, and asked me a good question that: "Do you know if there is any kind of software by which I can schedule this and get out of this problem?" As he needs the solution quickly, I checked it out some Tools and I come with the very cool tool "SQL SERVER Backup And FTP". This is very simple to use. This tool works as: Connect SQL SERVER Select Database(s) Select "Network Path", "FTP connection" Enter Email Address, so software will notify the user when backup is done We need to install this software on the server and we can take SQL SERVER Database backup on Network and FTP together. First please install this software from SQL SERVER Backup And FTP and Let me explain it how it works: Connect SQL SERVER: First connect to SQL SERVER Database, for which we need to take backup. We need to Enter SQL SERVER Name and credentials to connect to SQL SERVER Select Database: Once SQL SERVER is connected, all databases of that SQL server will be listed. We need to select all the databases for which we need to take backup as follows: Here I have done following: Checked two databases, pubs and SQLYoga. Checked "Network Folder" and select Network path Checked "FTP SERVER" and specify FTP Server, to put backup on FTP Server Checked "Email Notification", so I will be notified about success/failure of moving database backup. Unchecked "Schedule this job", as I just need to run it once. If we need to run it on daily basis, we can specify schedule to run it on that time daily. That's it and now we are out of stress to move database backup to another server or on FTP Let's click on "Run now" to check it out. After process is completed, Two zip files, pubs and SQLYoga is on Network path as well as on FTP. Another cool feature is that, we can schedule Full Backup, Differential Backup, and Transaction Backup, so we can have very minimal amount of data loss. Here, I have configured this software as: Take full backup of database at every 24 hours Take differential backup at every 1 hour Take Transaction backup at every 15 minutes Here, we assume that it is ok that we have 15 minutes data loss. It is depends on your application to decide this time window. Let's take one case study to get more understanding about to restore these backups: e.g. Database server is crashed at 8:20 AM, How can I restore my database from the backups? Restore full backup database which was taken at mid night 3 AM. As we have configured to have differential back at every hour, we also have Differential backup of 8:00 AM, so restore differential database backup. We have also configured transaction backup for every 15 minutes, so we have one transaction log backup which is taken on 8:15 AM, so let's restore it. So, now we have data up to 8:15 AM As we have assume that Organization is good if we loss 15 minutes of data. Here in this case, as we have Transaction backup of 8:15 AM,  we are losing transactions which are made between 8:15 AM - 08:20 AM. So, we should proceed with 08:15 AM transaction backup and make database available Another, option is also available, to remove old Differential backup and Transaction backup. Once new differential backup is generated, there is no need to keep old differential backup. We only need to restore recent differential backup, so we can remove old differential backups. We can also remove all transaction log backup which are generated before Differential Log. SO to remove old backups, this software is also providing an option for the same, we just need to checked this option and that's it. This software is also allowed following settings (advanced): You can setup custom "temp" folder, by default its using "C:\Windows\Temp" Compression Settings (Zip) Encryption I like this tool, I recommend this tool to take back up and move it on FTP. Try to use it and let me know your comments

Read More

SQL SERVER: Cross tab Query with Pivot (Dynamic Pivot)

As I mentioned earlier, PIVOT, in my earlier post, where we have reviewed the PIVOT with static columns. In practical world, we may need to have dynamic columns that needs to be displayed as below:

 

As Mentioned in Image, Here, dates are dynamic, based on Order Date, 10/25,10/26,10/27,10/28 etc..To achieve the same with Dynamic columns please find query for the same:

 

CREATE TABLE #TestTable(ctrl_no INT, id int, Amount NUMERIC(18,2), OrderDate DATE)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100018,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100016,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,100018,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/27/2012'

DECLARE @MinDate DATE
	,@MaxDate DATE
DECLARE @Dates VARCHAR(MAX)

SELECT @Dates = ''

SELECT @MinDate = MIN(OrderDate)
	,@MaxDate = MAX(OrderDate)
FROM #TestTable

WHILE @MinDate <= @MaxDate
BEGIN
	SELECT @Dates = @Dates + '[' + CONVERT(VARCHAR(10), @MinDate, 101) + '],'

	SELECT @MinDate = DATEADD(d, 1, @MinDate)
END

SELECT @Dates = LEFT(@Dates, LEN(@Dates) - 1)

DECLARE @qry NVARCHAR(MAX)

SELECT @qry = N'

SELECT    id,
        ' + @Dates + '
FROM (
    SELECT    id,
            ISNULL(Amount,0) AS Amount,
            OrderDate
    FROM #TestTable t
) x
PIVOT(
    SUM(Amount) FOR OrderDate IN(' + @Dates + ')
) p '

EXEC (@qry)

DROP TABLE #TestTable

Let me know if you have any question/comment.

Reference : Tejas Shah http://www.SQLYoga.com

Read More

Configure SQL Reporting Service, to Access Reports using External IP With IIS 7

Hi All, Recently I come to situation where I need to Configure Reporting services on server having configuration: Window server 2008 and IIS 7. I need to configure reports in manner, so User can access this reports thru External IP Address. I found configuration for IIS 7.0, so user can access reports externally. To able to access the reports externally, do the steps as mentioned below: 1. Create Virtual Directory in IIS 7.0 : 2. Go to Handler Mappings : 3. Double Click on "Handler Mapping". You will get screen like the given below: 4. Now see Top Right Corner: Click On "Add Managed Handler". Here you need to fill up the info like: Fill this info as: Request path: Reserved.ReportViewerWebControl.axd Type: Microsoft.Reporting.WebForms.HttpHandler Name: Reserved-ReportViewerWebControl-axd Thats it. Now try with External IP. You can access the reports with External IP too.

Read More

SQL SERVER: Cross tab query with PIVOT

We had a User group meeting for "Cross Tab Queries", where I have explained how to write PIVOT queries. Many times developer has an issue while writing query with PIVOT. Let me have a sample code for query with PIVOT keyword.

 

PIVOT is used to transform rows to column, which gives result in such a way which can be send to the user directly. Let me take an example. We have an order table where we have each order stored in a row in a table as displayed below:

 

ctrl_no	id	Amount	OrderDate
1000029	100016	990	10/25/2012
1000029	100018	990	10/25/2012
1000029	100016	660	10/26/2012
1000029	100018	660	10/26/2012
1000029	206007	660	10/26/2012
1000029	206007	660	10/27/2012

This information has to be saved in a row in relational database. When this information needs to be display it on the application, it needs to be displayed as below, so user can use this info for decision making:

id	10/25/2012	10/26/2012	10/27/2012
100016	990.00	660.00	NULL
100018	990.00	660.00	NULL
206007	NULL	660.00	660.00

Did you see the difference? how easy user can review the information. Lets see an query how to achieve the expected result with PIVOT keyword.

CREATE TABLE #TestTable (
	ctrl_no INT
	,id INT
	,Amount NUMERIC(18, 2)
	,OrderDate DATE
	)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100018,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100016,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,100018,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/27/2012'

SELECT id
	,[10/25/2012]
	,[10/26/2012]
	,[10/27/2012]
FROM (
	SELECT id
		,Amount
		,OrderDate
	FROM #TestTable t
	) x
PIVOT(SUM(Amount) FOR OrderDate IN (
			[10/25/2012]
			,[10/26/2012]
			,[10/27/2012]
			)) p

DROP TABLE #TestTable

 

Let me know if you have any question/comment.

Reference: Tejas Shah (www.SQLYoga.com)

Read More