Usefull SSIS tools

Today I'm listing 4 of my useful SSIS third party tools


1-BIDS Helper, The variables extension is an amazing and a must tool for changing the variable's scope  http://bidshelper.codeplex.com/

2-Validate, create and test variable outide BIDS   SSIS Expression Editor & Tester

3-SSIS Component Explorer http://www.sqllion.com/2009/06/ssis-component-explorer/

4-SSIS Log Analyzer http://ssisloganalyzer.codeplex.com/

Hope that helps.

Strip the file name out of the file path

Strip the file's name out of the file path:

Example: [User::strTest] Value = C:\Test\TestTextFile.TXT
Results: TestTextFile.TXT

RIGHT(@[User::strTest],FINDSTRING(REVERSE(@[User::strTest]),"\\",1)-1)



Strip the file's extension out of the file path:

Example: [User::strTest] Value = C:\Test\TestTextFile.TXT
Results: TXT

RIGHT(@[User::strTest],FINDSTRING(REVERSE(@[User::strTest]),".",1)-1)

BIDS - Visual Studio - Faster Startup Load With -nosplash

Just a quick note to save time over appearance and save on memory when you start BIDS or SSMS.
By adding ( -NoSplash ) switch at the end.

The same trick can be applied to all versions of Visual Studio and SSMS (SQL Server Management Studio) by the way.

The worst data mining example ever!!

Today I was buying a suit online from Macys when I got hit by a bizarre unmatched recommendations.
Although I wasn't logged in!
Right away sqlserverdatamining.com came to my mind with their Movie!Click example.

Weeks In SQL - Start and End days of a week.

Declare @Date datetime = '03/15/2011' ------- Your Date

select dateadd(day,( -datepart(weekday,dateadd(day,(7)-datepart(weekday,@Date),@Date))+((7)-datepart(weekday,@Date)))+(1),@Date) AS [First_Day_Of_The_Week]

Select dateadd(day,(7)-datepart(weekday,@Date),@Date) AS [Last_Day_Of_The_Week]

BIDS slower than DTEXEC !

I Recently came accross two important that I would like to share articles regarding that topic.

http://blogs.msdn.com/b/mattm/archive/2011/01/15/why-does-my-package-run-slower-in-bids-than-dtexec.aspx?wa=wsignin1.0

http://sqlblog.com/blogs/jamie_thomson/archive/2011/01/12/always-use-dtexec-exe-to-test-performance-of-your-dataflows-no-exceptions.aspx


Helpful Dtexec.exe cheats:

To execute an SSIS package saved to SQL Server using Windows Authentication:
dtexec /sq pkgOne /ser productionServer

To execute an SSIS package saved to the File System folder in the SSIS Package Store:
dtexec /dts "\File System\MyPackage"

To validate a package that uses Windows Authentication and is saved in SQL Server without executing the package:
dtexec /sq pkgOne /ser productionServer /va

To execute an SSIS package that is saved in the file system:
dtexec /f "c:\pkgOne.dtsx"

To execute an SSIS package that is saved in the file system, and specify logging options:
dtexec /f "c:\pkgOne.dtsx" /l "DTS.LogProviderTextFile;c:\log.txt"

To execute a package that uses Windows Authentication and is saved to the default local instance of SQL Server, and verify the version before it is executed:
dtexec /sq pkgOne /verifyv {c200e360-38c5-11c5-11ce-ae62-08002b2b79ef}

To execute an SSIS package that is saved in the file system and configured externally:
dtexec /f "c:\pkgOne.dtsx" /conf "c:\pkgOneConfig.cfg"


More here http://msdn.microsoft.com/en-us/library/ms162810.aspx