- January 5th, 2010
- Write comment
The guy that thinks up my posting titles has caught a flesh eating virus. He could lose some weight though as he was always a bit porky.
Recently I encounted the most infuriating problem with SSIS, SQL Server Agent or Windows Server 2008 to date. What it is, I dont particuarly care I just pray this issue is fixed soon.
I have a set of user defined variables sitting in an SQL Server 2008 database. These are used to generate connection strings, rainbows, pixies and other mythical creatures through expressions in an SSIS project. This works a treat when running the package in the SSIS Package Execution Utility or Business Intelligence Studio (BIS). It saves me having to change variables in each package every time I deploy to different servers. If you try running a package with file connections populated by dynamic variables through SQL Server Agent, it cannot find the specified package (the precise error I will edit in later when I can be bothered to reproduce it again – it comes with the usual completely useless hexadecimal HRESULT nonsense so you won’t be disappointed). What is particuarly aggravating is that if you boot up the very handy Process Monitor you can see as clear as day the SSIS process (dtexec.exe) is a lying git. It only throws the error 50% of the time and yet the SSIS process finds the package quite happily 100%, every time.
I really did not want to define the file connection paths explicitly but I had no alternative. I got the same bull when I tried to run it through the Windows Scheduler to no avail. I tried everything. My futile attempts are way too numerous to list here. There is NO other way around this problem.
I cannot state whether or not this occurs in W2K3 as all our servers have been downgraded to W2K8. Enough cynicism from me for today. Here is something to warm you up: 27bslash6. Enjoy and till next time.