Monday, December 31, 2012
Thursday, September 13, 2012
SSIS - Creating SQL server Agent Proxy Account for SSIS Package Execution
When you are trying to perform some file system task in SSIS or while attempting to read/write some files from the remote locations, the job which uses SSIS package will require the permission to access the network location.
If your windows NT account has all the rights to access the particular loaction then the package will work fine while runnig the package from BIDS. But the same will not work if you run the package from SQL Server Agent Job. Since the SQL Server Agent will be running on the 'sa' account by default. And this SQL admin will not have permission to access the file sytems.
So, to make it run from the SQL Server Agent, we have to create new credential (of Windows NT account) which will have all the access to the specific file server or network location.
And we have to create Proxy for the SQL Server Agent which uses the newly created credential. And the Proxy should be associated with the Subsystem SSIS.
Please find the detailed steps written in the following blogs :
http://www.vincepergolizzi.com/2012/03/setting-up-sql-server-agent-proxies-for-ssis-packagese/
http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/
Thanks for the authors of those blogs. :-)
If your windows NT account has all the rights to access the particular loaction then the package will work fine while runnig the package from BIDS. But the same will not work if you run the package from SQL Server Agent Job. Since the SQL Server Agent will be running on the 'sa' account by default. And this SQL admin will not have permission to access the file sytems.
So, to make it run from the SQL Server Agent, we have to create new credential (of Windows NT account) which will have all the access to the specific file server or network location.
And we have to create Proxy for the SQL Server Agent which uses the newly created credential. And the Proxy should be associated with the Subsystem SSIS.
Please find the detailed steps written in the following blogs :
http://www.vincepergolizzi.com/2012/03/setting-up-sql-server-agent-proxies-for-ssis-packagese/
http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/
Thanks for the authors of those blogs. :-)
Sunday, September 9, 2012
Search the Column on the DB (SQL Server)
To find a particular column from all the tables available on the particular DB, We can use the following sql query :
SELECT tab.name AS table_name,
col.name AS column_name
FROM sys.tables AS tab
INNER JOIN sys.columns col ON tab.object_id = col.object_id
WHERE col.name LIKE '%SalesDoc%' -- Column Name
Tuesday, May 29, 2012
SSRS - Implementing Drill Through action in the same RDL
Scenario:
User wants a SSRS Chart report which will display the Product Category wise
Sales in the initial view. On clicking of the bar chart, the report should
display the Product wise Sales of that selected Category.
We can enable this drill through
action in a single RDL as given below.
Note : I haven't given the SSRS chart creation steps detaisl by considering that the reader knows how to create SSRS Chart reports.
To implement the above said requirement, we can do some tweaks in the SQL query if the report data
set and achieve the same.
The given
below sample query can be used in the data set, which will return the Category
wise sales if the in parameter (@ProdCategory)
value is not available in the Category table.
if exists(
select 'x' from
dbo.Dim_Category where
Category=@ProdCategory
)
begin
select ProductName as Item ,SalesAmt as
Sales_Amt from dbo.Dim_Category
where Category=@ProdCategory
end
else
begin
select Category as Item,sum(SalesAmt) Sales_Amt from dbo.Dim_Category
group by
Category
end
Follow the
given below steps:
-In the above
created Dataset, @ProdCategory is parameter
and its default value is 'a' initially.
-Create a
Chart report and drag the field Item in to the category field and Sales_Amt
into the Data field of the chart.
-In the Action pane write an expression to Go
to this same report in the Chart series
-Pass the Field
‘Item’ as a parameter to the report
-On clicking
on the Category that Category will be taken as a new parameter. And new result
set for the selected Category (Products under the selected Category) will be
produced in the same report.
-Now, when
the report initially gets opened, the default parameter will be passed to the
data set and the value will not be available in the Dim_category table, and the
report will generate Category wise Sales Amount in the Chart.
-If the user
clicks on the Category series, then the selected Category will be passed as an
in parameter to eth data set and the selected categories Products will be
returned by the data set and the chart will provide the Product wise Sales
amount.
Saturday, February 4, 2012
To Find Duplicate Rows using Common Table Expressions and perform DML operations
Handling the Duplicates using CTE and Row_Number() - (ie, To Delete or Update the Duplicates from SQL server Tables Using CTE, Row_Number() )
Just to select the duplicate rows from a table, we can use the following kind of simple query. The below mentioned query will return duplicated rows based on the Product_Id :
SELECT Product_id,
COUNT(Product_id) AS NumOccurrences
FROM InventoryDetail
GROUP BY Product_id
HAVING ( COUNT(Product_id) > 1 )
If you want to Delete or Update the duplicate, we can use the following sample which will use the CTE (Common Table Expression) , Row_Number function and the MDL statement (Delete/ Update).
WITH CTE (Product_id,Time_Id, DuplicateCount)
AS
(
SELECT Product_id,Time_Id,
ROW_NUMBER() OVER(PARTITION BY Product_id,Time_Id ORDER BY Product_id) AS DuplicateCount
FROM InventoryDetail
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
Subscribe to:
Posts (Atom)