SSIS package using SQL Authentication and DontSaveSensitive as ProtectionLevel

In this post i am trying to look into steps to follow while running SSIS package using SQL Authentication and DontSaveSensitive as ProtectionLevel. I created a simple SSIS package using a connection manager going to SQL Server using SQL authentication, I have my ProtectionLevel as EncryptSensitiveWithUserKey My package has an OLEDB Source (using connection manager…


Search SQL Agent job running a specific command

I recently had a need to find  all the Jobs running a specific stored procedure on a SQL Server instance. A simple query proved useful here. select as JobName from dbo.sysjobs Job, dbo.sysjobsteps Step where Job.job_id=Step.job_id and Step.command like ‘%sp_linkedservers%’


Undeclared Namespace in XML (eg: xsi is an undeclared namespace)

If you XML source publishes invalid XML for some reason ( i.e. it has Xml elements referring to undeclared namespace ) and you try to load this XML in XMLDocument. You would end up getting exception similar to <NameSpace> is an undeclared namespace. eg: <?xml version=”1.0″ encoding=”utf-16″?> <root> <Name xsi:nil=“true”>name1</Name> <Name xsi:nil=“true”>name2</Name> <Name xsi:nil=“true”>name3</Name> </root>…


SSIS package created using Import/Export wizard failing on tmp files.

If we try to create a SSIS package from Import/Export wizard with option “Optimize for many tables” its possible that we may run into error messages like Could not find file ‘C:\Documents and Settings\d108967\Local Settings\Temp\1\tmpD7.tmp’.”. The reason for this is the way this SSIS package work. If you open this package in BIDS you would…