visual studio seems to lose "provider" on pkg imports
-
hi, just to get this part out of the way, if my intended target for an ssis project is 2019, i always change the project over to 2019 before starting whatever i'm going to do. Locally and on my vm i work in vs 2022.
It might be just as of late, when i import an ssis project into vs on my local, vs complains about the provider not being available. It seems to change the provider over to something really generic looking. My response in simpler projects is to switch it over to the ms sql provider and all is usually fine.
but today as i wanted to add a pkg to a more complicated project i imported from our 2019 dw server, i got concerned that if i went to deploy the modified project back to that server, that provider issue would remain/become broken on all the other pkgs in the project even though i wasnt doing anything with them. i became so concerned that instead of trying to add the new pkg to that project i made a different project with only that pkg in it. on my 2022 vm i also run vs 2022 and never see this behavior. i saw other weird behavior when trying to add the new pkg from a file location. the button to proceed remained gray pretty much blocking me from moving forward. i believe the same thing happened when trying to add from the catalog but i was so harried i didnt take notes of each nuance.
does the community know why on import ssis seems to lose the connection info? Interestingly, when it was still lost the query (sql command) didnt show any more. But when i picked a valid provider, the command suddenly showed.
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
i took this a step further today. I imported a project with about 7 pkgs from our prod 2019 sql server catalog into vs 2022 locally. I used my regular (non admin) acct locally to run vs.
i added from scratch a new pkg that i actually copied from another pkg in the same project . only 1 of 2 conns came over in the copy, namely the destination conn. i renamed the new pkg. i had to fix / reset the destination provider (for some reason it showed native oledb, i changed it to ms oledb provider for sql) , typed in the server name (my 2022 vm) on latter which was now missing on dest probably because of provider reset. I made sure the project parms (target server and database) were in sync with this conn in this conn's property's expressions.
from the source component of the df i added a whole new source conn. i had to fix the provider here too. i entered the sql command, a couple of derived cols and eliminated the errors on the dest component by simply removing it altogether , re adding and remapping.
i debugged this pkg fine. Run time showed 64 bit true for debugging.
then i went to another pkg (happens to be the same i copied from above) , fixed various meta data and provider issues, debugged it fine.
then i chose one more pkg. i repaired the source provider in ways mentioned above, corrected meta data there as well, corrected meta data on the destination by simply opening it. I noticed a red down arrow on the source conn when in control flow but not in data flow. Anyway, this debugged fine as well.
Then i deployed all of it to our sql server vm 2022 . I actually did this two or 3 times today. Most of the time today, only the new pkg ran there via sql agent. The final time the new pkg and last pkg ran. I show the error below which i believe is pretty much the same error i've gotten multiple times before, on multiple pkgs before last run.
a couple of things i havent tried are 1) deploying the project straight from the prod 2019 server to the 2022 vm, 2) changing the project properties immediately after import to vs, to sql 2019 and deploying it that way to the vm, 3) deployong a 32 bit project, not 64.
the service acct is in charge when the sql agent job runs.
hopefully the community can see what bugs me about this. The risk of breaking everything after a deploy with a new pkg seems real. i've actually started deploying separate projects for now even though all of these pkgs do the same thing for different company locations/erps.
i may need to publish the images separately as my first attempt to post this response disappeared.
-
EARLIER ERRORS
-
LAST ERROR
-
for sanity purposes mostly, i imported today into vs2022 an ssis project from a sql 2019 server's catalog. Same behavior on both perm connectors and param driven connectors. A complaint by vs, after trying to edit any connector, that the provider isnt supported. the provider showing when i hit ok on the error is Native OLE DB, which isnt how any pkg in this project was authored.
At some point (if its possible) i'll try deploying right to a sql 2022 server's catalog , this project from a sql 2019's catalog. And report back here if any pkg could run from sql agent that way on the sql 2022 machine.
-
this sounds familiar. I'll probably go one of 2 ways...either install the deprecated provider on my local or switch all of this over carefully to the recommended MS provider for sql. I think i got away once with installing the deprecated somewhere on a 2022 machine.
-
i think i get it.
- on my pc where i have vs2022 with ssis extension installed, importing an ssis project from our 2019 sql server is always going to break because our server uses the deprecated native sql driver and that driver isnt installed on my pc.
- once i installed that driver (32 bit wouldn't install, only 64 would install) from https://www.microsoft.com/en-us/download/details.aspx?id=29065 onto my pc, import no longer broke the "provider".
- however, unless that provider was also installed on our newer vm, the sql target, neither debugging nor deploying and running was going to work on an imported pkg that uses the deprecated driver...unless i changed over to a new provider in vs. for now i installed the deprecated driver on our newer vm. over time, i'm going to change all providers to the newer MS provider and avoid this confusion.
Note, you don't hit deploy at that link, you expand "Install Instructions" and go to a section called "MICROSOFT SQL SERVER CONNECTIVITY FEATURE PACK COMPONENTS". i had to rt click the 64 bit item, copy the link and paste it in another tab. Also, if you dont catch the msi as its downloading , before it gets to your download folder, and mark it confirmed, it goes into your download folder with the word "Unconfirmed" in the file name.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply