Map DataReader & OLEDB Source SQL query parameter to SSIS variable

  Following are the steps I used to get parameters mapped to my SQL query using DataReader Source  and ADO.Net connection manager. 1. Create Ado.Net Connection manager using .Net Providers. 2. Add DataReader source and map it to ADO.Net connection manager by right Click -> Edit. 3. Under component properties, Write SQL Command without where…


SSIS Job using Oracle client goes into perpetual execution.

At more than one instances while working with my customers I found that there is a SSIS package which runs fine from BIDS as well as from command prompt using DTEXEC command but as soon as you try to execute this package as SQL Agent job, The JOB goes in a perpetual execution. These are…


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…


SQLCLR Blessed assemblies

As per there is a specific set of assemblies supported inside the SQLCLR  i.e. •Microsoft.VisualBasic.dll •Mscorlib.dll •System.Data.dll •System.dll •System.Xml.dll •Microsoft.VisualC.dll •CustomMarshalers.dll •System.Security.dll •System.Web.Services.dll •System.Data.SqlXml.dll •System.Transactions.dll •System.Data.OracleClient.dll •System.Configuration.dll If a user assembly references any .NET frameworks assembly that is outside of the ‘blessed’ list and you try to register the assembly with SQL Server, error…


Typed DataSet : TableAdapter CommandTimeout

If you want to set CommandTimeout  for Typed dataset’s TableAdapter there is no public property exposed.You could still  either extend the partial TableAdapter class or modify the existing designer generated code to modify CommandTimeout behaviour. For e.g. : I added DataSet1 to my windows application which has a TableAdapter called GetProductionLocationTableAdapter. GetProductionLocationTableAdapter calls a stored…


ODBC Trace not collecting logs ?

If you are trying to run ODBC trace and do not see data being collected or log file being generated. Try following steps. 1. From your registry key HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.ini\ODBC make sure that the value for entry Trace is 1. 2. Re-Start your computer. 3. After your computer re-starts, Launch the application against which you want…


ADO.NET 2.0 performance counters

ADO.NET 2.0 introduces a new set of performance counters that are specific to each provider. In the Visual Studio 2005 release, the supported providers are the SQL Client and Microsoft’s Oracle Client managed providers. Those legacy .NET CLR data counters were deprecated in Visual Studio 2005, and they will no longer be populated. The counters…