Saturday, May 19, 2012

Stuck with 64 bit ?

Execute SSIS packages with 32 bit configuration in a 64 bit environment
If you,
  • Working on 64 bit environment
  • Executing SSIS packages using a separate application
  • Using two separate server machines for SharePoint and SQL servers
Then definitely you have struggled with this issue.

If you have Excel Connection Manger in your SSIS package or  extracting data from db2 source then your SSIS package might failed without any reason. It’s because 64 bit version is not compatible with these components.
What I did as solution is, execute the SSIS packages using SQL Server Agent jobs .

How to do it ?

  • Connect to SQL server with sa privileges.
  • Create a new job

  • Then in steps you can browse your SSIS package in the file system

  •  The important point is to tick the Use 32 bit runtime option in Execution options tab.

That’s all about creating a SQL Server Agent job. It will execute your SSIS package in 32 bit mode.
But now how to start this job ?
Simply write a stored procedure to start this job. Following figure shows how to do it.

That’s all !
Now just you need to do is execute this stored procedure using your code with job name as a parameter.
I will talk about how to get the execution status of this SSIS package in next post .

No comments:

Post a Comment