Sladescross's Blog

Blogging about Sharepoint related stuff

SQL Agent and SSIS Troubleshooting April 22, 2014

Filed under: Package,SQL Agent,SSIS,Troubleshooting — sladescross @ 9:34 am

SELECT TOP 1000 [instance_id]
FROM [msdb].[dbo].[sysjobhistory]
WHERE Message Like ‘%the package execution failed%’
ORDER BY [run_date] DESC


SSIS Container Hierarchy April 17, 2014

Filed under: Container,Container Hierarchy,Hierarchy,SSIS — sladescross @ 1:33 pm


OnPipelineRowsSent April 15, 2014

Filed under: Pipeline,Rows,SSIS — sladescross @ 4:29 pm


Uploading to SQL Azure Using SSIS February 26, 2014

Filed under: SQL Azure,SSIS,Uploading — sladescross @ 4:25 pm


Upgrade SSIS 2008 Packages

Filed under: Package,SSIS,Upgrade — sladescross @ 3:50 pm


DTExec February 24, 2014

Filed under: DTEXEC,PATH,SSIS — sladescross @ 1:40 pm

On a 64-bit computer, Integration Services installs a 64-bit version of the dtexec utility (dtexec.exe). If you have to run certain packages in 32-bit mode, you will have to install the 32-bit version of the dtexec utility. To install the 32-bit version of the dtexec utility, you must select either Client Tools or Business Intelligence Development Studio during setup.

By default, a 64-bit computer that has both the 64-bit and 32-bit versions of an Integration Services command prompt utility installed will run the 32-bit version at the command prompt. The 32-bit version runs because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version. (Typically, the 32-bit directory path is :\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn, while the 64-bit directory path is :\Program Files\Microsoft SQL Server\100\DTS\Binn.)


SSIS and ODBC Driver and Bitness

Filed under: 32bit,64bit,Bitness,DTEXEC,Integration,ODBC,SQL Agent,SSIS — sladescross @ 1:11 pm

Can’t populate an Object variable with a recordset when using an Execute SQL Task connecting to an ODBC data source;

If you have a 64bit machine, with connections that only support 32bit (like Excel, Access and old ODBC connections) you will get an error like above. The solution is to run your package in 32bit mode. This can be done within Visual Studio for debugging or within SQL Server Management Studio for scheduled packages.


Right Click the SSIS project and select Properties in the context menu. Go to the Debugging pane and select false under Run64bitRuntime.

SQL Server Management Studio
Edit your job and then edit the right jobstep. Go to the Execution Options pane and check “Use 32 bit runtime”. This property will only effect the package (+child packages) called in this jobstep.

Note: For 32bit execution via dtexec.exe you need to pick the right folder:
32bit => C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
64bit => C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe

Fixing SQL Agent

You will need to edit the existing SQL Agent job to change the bittedness of the job step. This will be under the Configuration tab and then under the Advanced tab. Check/Uncheck the 32-bit runtime.


If you double-click a dtsx file from Windows Explorer, it is executed by the SQL Server 2008 Integration Services Package Execution Utility. The default fully qualified path for that tool is C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe. In other words, it is a 32-bit product, so it doesn’t have any trouble running things in 32-bit mode by definition.


SSIS and REST and Script Task February 5, 2014

Filed under: Code,REST,Script Task,SSIS — sladescross @ 3:29 pm


Applied Business Intelligence : SSIS Design Pattern – Incremental Loads January 29, 2014

Filed under: Design Pattern,Incremental Load,SSIS — sladescross @ 12:29 pm


SSIS Logging and Data Auditing | Business Intelligence content from SQL Server Pro January 25, 2014

Filed under: Auditing,Data Auditing,Logging,SSIS — sladescross @ 10:37 am



Get every new post delivered to your Inbox.

Join 63 other followers