Posts Tagged ‘sql’

SSIS: How to add a timestamp to a Excel File output path.

// September 22nd, 2008 // 15 Comments » // sql, SQL Server, Systems Integration

I have been working with SSIS lately and I have been having some troubles adding a timestamp to the output excel file name. I have found several sources on how to do it, the best one (except for the video’s size which is too small) is: tutorial.

It did help, but it wasn’t what I wanted, because you have to hardcode the file path in the expression, making the developer or deployer hard code the path in two places. And I didn’t want the deployment team to add the path into the actual expression box, any mistakes or typos and that might break the package. So I wanted to extract the actual file path, from the timestamp generation expression.

So this is my solution:

1) Create a Package-wide variable called OutputFilePath

2) Set the Variable’s Value to the expected file path without the timestamp, like a template.

3) Go into the Excel data source and enter the following expression, linking the expression to the ExcelFilePath property:

REPLACE(@OutputFilePath, “.xls”, “_”+ REPLACE((DT_WSTR, 10)(DT_DBDATE)GETDATE(), “-“,””)+REPLACE((DT_WSTR, 10)(DT_DBTIME)GETDATE(), “:”,””)+”.xls”)

4) Run your package, and test.


Sanitize Your Data Inputs

// October 11th, 2007 // 2 Comments » // fun

I was just browsing and I got across this beauty:

Sanitize your Data comic

Stored Procedures vs Embedded Queries

// January 18th, 2007 // Comments Off on Stored Procedures vs Embedded Queries // software development, sql

I have been involved in a lot of discussions lately on whether to use Stored Procs or Embedded Sql Queries in layered applications. I have always supported Stored Procs over Embedded Queries. The main reason is separation because it provides a medium to extract logic from the layers. I know in some cases there is even a performance boost, but not enough to notice. I have always thought of Stored Procedures as a Logic Service, belonging of course to the BLL (Business Logic Layer) not the DAL (Data Access Layer).

People often get frustrated when I say this, but separating logic into web services (or any other kind of service) is the same as separating Logic into stored procedures. The real truth about it, is that it has the same idea. Stored procedures are specialized Services which provide specific logic.

There is an ongoing discussion, which I find very interesting on Microsoft forums.