One of the donts in SQL Server 2005

Standard

For the past few days, I have been trying to figure out why my SSIS (Intergration services) for SS’05 will not run through Server Agent. After some time of debugging, I realised the problem which im sure alot of people are going to run into. First of, I was developing and running my packages in SSIS (which work just fine) in a 64bit envirnoment. When I installed SS’05, I installed the 64bit version which comes with the wow magic for 32bit softwares to run just fine on 64bit machines. In any case, packages were developed in a 64bit envirnoment but my odbc driver (for remote data connection) was 32bit driver which was no problem because in order for me to see my driver in SSIS (64x), I changed the project setting (project > properties > debugging) run64bitruntime property to false. In doing that, I was able to run my packages in SSIS just fine because it could see the 32x odbc in my 32x DNS. Now, afte such a smooth ride of development, I was ready to schedule my packages in SA (Server Agent) and BOOM, suddenly, the packages wont run. Well, go figure. I had to lean it the hard way. Word of advice, do creat SSIS package in a 64bit environment that connects to a 32bit database driver because SA does not have the magic switch like SSIS does to change the runtime from 64x environment to 32x environment. So there, 64bit SA will not successfully run packages which have 32 db drivers in them because it can see it in the 64bit DNS.
But ooo wait, there is hope, you can run the packages remotes in SSIS from another SA(32x environment) and it will work just fine because it knows where to fine the 32bit DNS station.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s