SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Posts Tagged ‘SQLJourney’

SSIS – Create New Excel File Dynamically to Export Data

Posted by Prashant on January 12, 2013


Recently I got a requirement to export error data to a newly created excel file every time the SSIS package runs. The challenge here was to create new excel file with time stamp every time the package ran and export data to that newly created file.

In this post will demonstrate this step by step.

  • Create a template file to export the data. You need this template file to set up the package for the first time. Once the setup is done you can delete the template.
  • Create connection managers:
    • Add Excel Connection Manager pointing to the template excel file created before.
    • Add OLEDB Connection Manager pointing to the desired database (here I am using AdventureWorks2008R2 database)
  • Add an Execute SQL Task and use the below script as SQL Statement as shown in fig-2:
CREATE TABLE Data (`DepartmentID` LongText, `Name` LongText, `GroupName` LongText)

Fig-1
Fig-1

ExportExcel-Package11
Fig-2

  • Now setup a Data Flow Task to export the data.
    • Add a Data Flow Task to the package. Open the Data Flow Task, inside Data Flow tab add a OLEDB Source, select SQL Command as data access mode and use your query to fetch the required data. I will use the below query:
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
  • Add an Excel Destination to the package. In the excel destination editor select Table or View as Data Access Mode, select the name of excel sheet and do the required mapping. Set ValidateExternalMetadata property to False.

ExportExcel-Package12

Fig-3

  • Now go to Expressions property of Excel Connection Manager and set ExcelFilePath with below expression:
"S:\\ETL Lab\\CreateNewExcel\\ExportData_"+REPLACE((DT_STR, 20, 1252)(DT_DBTIMESTAMP)@[System::StartTime], ":", "")+".xls"

and the package is ready to run.

You can download the sample package here.

Posted in Excel, SQL Server, SSIS | Tagged: , , , , | 15 Comments »

SQL Server – List of Tables Used in Stored Procedure

Posted by Prashant on December 1, 2012


Today while developing a piece of logic in our development, my colleague said that I know there is a procedure which has same kind of logic and that may help us building the logic quickly. But the question was how to find that procedure !!!

Then the idea came up was if we can find the list of procedures that are dependable on Table1 & Table2 ?

…and that inspired me to write this post.

We spend some time researching system tables and here is the findings to find list of tables used in a procedure.

;WITH procs
AS
(
SELECT o1.name AS proc_name,
o2.name AS table_name,
ROW_NUMBER() OVER(PARTITION BY o1.name,o2.name ORDER BY o1.name,o2.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o1
ON o1.id=d.id
INNER JOIN sysobjects o2
ON o2.id=d.depid
WHERE o1.xtype = 'P'
--AND o2.name = 'tabname1' OR o2.name = 'tblname2'
)
SELECT proc_name, table_name
FROM procs
WHERE row = 1
ORDER BY proc_name, table_name

Let me know if you have any better idea for this.

Posted in SQL Server | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: