SQL Server 2008 R2 - Custom SSIS Components work in BIDS not in SQL Server Agent

Question

We have developed Custom SSIS components that integrates with an online REST API (Shopify to be precise) and these components work fine in BIDS and every single time too. However, when I create a SQL Server Agent job for the SSIS package that uses the components, some of the components don’t work, there is no error, just never hits the Pre Execute. The components that don’t work call the REST API and set specific TAGS on some orders, they do so using a JSON. The SQL Server agent is running on the same machine (my own) and it's executing the same SSIS package that works in BIDS. There are no errors and the SQL Server JOB reports success.

My machine is a 64 bit setup, so obviously the Agent will be running in 64bit so there may be issues there.

This is what I have tried already

• Changed the SQL Server Agent user to eliminate the issue being a proxy/user issue.
• Changed the setting for the step to use 32bit runtime (Ticked “Use 32 bit runtime”)
• I have tried compiling my code using Any CPU x86 x64 .. with different results but none of them work etc
• The installation project (which is part of the solution) is a WIX Toolset project which can only compile using x86 which I don’t think I can change.
• Logged where it is getting to a log file.

I think it might be 32bit/64bit issue but maybe wrong. The log file shows the following When called from BIDS (works) • Validate (code running as 32bit) • Validate (code running as 32bit) • Hits Pre Execute and it all works thereafter

When called from SQL Server Agent (doesn’t work)

• Validate (code running as 32bit) • Validate (code running as 64bit) • Never hits the Pre Execute

Like I said, I suspect it being a 64bit issue but maybe wrong.

Answer 1

I realize this question is very old, but I thought I'd put in my answer. I just ran into this very issue and the answer was that the SSIS engine didn't see the custom component doing anything so it removed the component at runtime. I was able to get around this by changing the RunInOptimizedMode property of the Control Flow object to False. After this I was able to run the SSIS package as a SQL Agent job.

If you run the package through dtexec.exe as bilinkc suggested in your comments, it should give you messages that make it obvious if this is occurring.

Answer 2

Hi guys and sorry for not posting the answer.

First of all thank you for all your help, especially Nick McDermaid as you kept trying and trying. I really appreciated your pointers and without them I wouldn't have solved it ( kind of solved it :o) ).

My SSIS Custom components only work when I have a "Success path" coming out of them. This was only an issue on this particular occasion because it was the last component called in my Data Flow.

This could be down to a bug in my SSIS Custom Component but I am sure I have had this issue with other components in the past (I think it might have been the 'Send Mail Task').

If anyone comes across this issue again then I would love to hear from them.

I'm currently fire fighting at work so hadn't had a chance to look at the SSIS Custom Components again.

Once again, thanks for the help I received.