ssis 32 bit vs 64, debug and non debug
-
Hi i am looking in vs2022 at my imported 2022 dev azure vm based ssis project. And vs2022 at my 2019 non azure non vm (i think) based production ssis project. The idea is to compare their bittages after i got an error on the dev box basically saying the 64 bit connector wasnt installed and therefore the pkg couldnt connect to ssisdb.
Both ultimately run from sql agent. i already see from the error in the ssis dashboard the website from where i'll download the 64 bit connector but i want to go in with eyes wide open. so i have a question or 2.
i looked at both sql agent jobs, config->advanced and see the 32 bit checkbox NOT checked. so i believe sql agent is asking in both cases to run 64 bit.
in vs2022 i see under project->properties->debugging the run64bitruntime set to true but greyed out at least on one of these. why is it greyed out?
most importantly where in vs for both can i see the non debugging "bittage" for both? top left i see configuration set to Active(Development).
finally are there any gotchya's if i try to install the connector whose absence generated the error shown in image 2? one red flag i see already in one link's title is that its for thru sql 2019 edition only. and mine is 2022 development. this following link confuses me more because i wonder if default is applied while running vs and/or will carry over to production at deploy time or just hold in the vs 2022 dev environment... How to change default ole db provider on VS 2022 ssis connection manager? - Microsoft Q&A .
how does installing a driver (unless we do it locally too) on a server change the behavior (default) of ssis pkgs already there on the server and after they are imported locally in vs? i feel like im missing something.
at How to change default ole db provider on VS 2022 ssis connection manager? - Microsoft Q&A , yitzhak says to use 19.3.2 but there is no 19.3.2 at the previous link. and the msft ontributor points us a t a link tyhat is no longer maintained.
-
Have you tried to install the SQL Server Native Client on your dev machine? It doesn't ship with SQL 2022 so the connection is failing to acquire.
I would suggest changing the connection to use OLEDB for SQL Server, but if you're doing dev work in sql 2022 for a 2019 production box you would need to check it works in SQL 2019. It sounds safer to install a connection on the dev box than on the production box.
I don't remember if it makes a difference, but what is the TargetServerVersion in the Configuration Properties?
-
thx ed, the connection is failing on the azure dev server when i run the agent. i'm not debugging or testing locally.
so does installing anything locally really matter? i'm a little lost on where/when the default takes effect. i am deploying from my local to the dev azure machine.
i'll look for the target edition. And i'll look to see if in prod we are using "native client " which the msft person suggested if we want to continue using it.
and what we are doing isnt really "dev work for 2019", its more a poc on an azure vm. so it would be nice to be as apples to apples with prod as possible but once we flip these switches in prod, its all about the params as none of the plumbing will really change. im going to look at what conn technology we use in the prod ssis project and post that answer back here.
in vs i see the conn technology chosen in both environments is Native OLE DB\SQL Server Native Client 11.0. i'm assuming whatever matches that choice in prod isnt installed on dev. and that's what everybody is calling the default? and you want (or perhaps offer as an option that requires the fewest changes for) me to start by installing that on the vm from Download Microsoft® SQL Server® 2012 Native Client - QFE from Official Microsoft Download Center .
...is 2012 of that connector flavor the latest? i assume native means not 3rd party.
both vs projects target (project->...properties->configuration properties->general) sql 2022. i wouldnt swear that we've ever deployed from vs2022 to production. but we did so for the vm.
-
If the Azure VM is a potential replacement I would change the connection to MSOLEDBSQL rather than install a deprecated connection on the SQL 2022 box.
I think you need to change the TargetServerVersion to 2022, then modify the connection to use Microsoft OLEDB Driver for SQL Server. I don't remember, but you may also need to upgrade the packages.
-
ok, i think i understand. i'm going to try to install native (presumably a 64 bit flavor) on the vm first.
we aren't sure the vm is a replacement. and i need to minimize the risk points for what i am proving with two new erps as there are a bunch of connections in each of the 14 pkgs in this project.
if that doesnt do it i'll try to muddle thru an uninstall and then change the connections as you suggested. is there an install i'll need if i go to msoledbsql?
-
i think that got me going. thx ed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply