October 28, 2016 at 5:03 am
I have a server with SQL Server 2016 on it, using SSDT 2015, and I have tried using project deployment for one of my test packages. It looks like everything is running ok, until it gets to the script task, which it looks to completely ignore. I have logged everything, checked the event viewer, checked the execution logs, and do not get any errors produced. In fact, I have changed my script task to just throw a failure with "Dts.TaskResult = (int)ScriptResults.Failure;" as the first thing in the main block, and it still doesn't throw a script failure. If I set the force execution result to failure, then the logging does say it fails.
Has anyone experienced something like this on SQL 2016? I'm at a bit of a loss on why there is a complete lack of messages that would indicate why the script task is being ignored. I will probably play around with the script task .net framework levels and whether it is building in 32 bit or 64 bit mode, but it could be anything really, with no errors or indicators pointing me in the right direction!
Package also works fine on my development machine, although I run it there with encrypt sensitive with user key, whereas on the server I have set the project parameter connection strings... having a few issues with how I can best run it between the two environments, but I don't expect this to be the issue, as on the server, it has executed things that use connection strings, it just totally ignores the script task.
October 28, 2016 at 5:39 am
kyagi.jo (10/28/2016)
I have a server with SQL Server 2016 on it, using SSDT 2015, and I have tried using project deployment for one of my test packages. It looks like everything is running ok, until it gets to the script task, which it looks to completely ignore. I have logged everything, checked the event viewer, checked the execution logs, and do not get any errors produced. In fact, I have changed my script task to just throw a failure with "Dts.TaskResult = (int)ScriptResults.Failure;" as the first thing in the main block, and it still doesn't throw a script failure. If I set the force execution result to failure, then the logging does say it fails.Has anyone experienced something like this on SQL 2016? I'm at a bit of a loss on why there is a complete lack of messages that would indicate why the script task is being ignored. I will probably play around with the script task .net framework levels and whether it is building in 32 bit or 64 bit mode, but it could be anything really, with no errors or indicators pointing me in the right direction!
Package also works fine on my development machine, although I run it there with encrypt sensitive with user key, whereas on the server I have set the project parameter connection strings... having a few issues with how I can best run it between the two environments, but I don't expect this to be the issue, as on the server, it has executed things that use connection strings, it just totally ignores the script task.
What edition of SQL Server 2016 is installed on the server (Standard, Enterprise, Express)?
Is it at the same patch level as your development machine?
If you look at the All Executions report, can you see from that whether the task is being recognised or executed?
Regarding environments, I suggest the following:
1) Set all packages and projects to Protection Level 'Don't Save Sensitive'.
2) Set the default values for all connections and parameters to their 'development' values (localhost, or whatever).
3) Use SSISDB environments to override these settings on the server.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 31, 2016 at 9:03 am
I am using SQL Server enterprise.
I am running the packages locally pointing to the production DB's, does patch levels still apply, as the instances are the same one? Not sure how to query this. I do think that they are pretty recent.
There is a post execute stage, so it should be running the script task.
I have now recreated and simplified the script task even more, and put it in another newly made package and just put a using Thread.Sleep(30000) command in the script task, and it runs in under a second, so it can't be running through the thread.sleep, as otherwise it would take a minimum of 30 seconds.
There are no sensitive variables in my package, but I have tried deploying it as "don't save sensitive" and "encrypt sensitive with password" anyway, and none of these have made any difference. There are no project parameters or package parameters, or variables, in my simplified test case, which still isn't working.
I played around with the .NET frameworks for the script task, but it seems like I cannot go below .net 4, as it stops compiling the "dts" references properly before that. I have .net 4.5 on the server. Default target .net version is 4.5
Oh and the script component works fine when deployed, I was using it to reformat dates with no issues, it is just the script task...
Does anyone have any ideas? I am starting to think there must be something wrong with the basic SQL server setup somewhere, as I have simplified the package as much as I can, and it still does not work at all! Could any aspect of SQL server be set up incorrectly enough to produce results like this?
Edit: I tried to deploy the package with a target server 2014. Does not work, cannot load script. Target server version on project moved back to 2016, where it at least runs.
My visual studio is version 14.0.25123.00 update 2, if that helps. The SQL server is listed as Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Build 9600
Edit 2: I have tried running this on my local machine's SQL server instance which is also Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Build 10586 and it works fine. I am going to see about getting the SQL server upgraded on the server, and if that fails, reinstall the SSIS part of it... if that fails, I am going to test if package deployment bypasses these issues. If that fails... Will need to come up with some more avenues to investigate!
November 1, 2016 at 4:32 am
I got the server updated, but still no luck.
It is now: Microsoft SQL Server 2016 (RTM-CU2) (KB3182270) - 13.0.2164.0 (X64) Build 9600
Looking at the previous versioning, this was actually fine, my local environment where it worked matched the production environment where it didn't; the build number being different was due to the OS difference (windows 10 vs windows server 2012 r2)
I am getting SSIS reinstalled now. If that doesn't work, I may try to install a SQL server developer edition on the server, to see how that behaves... The current install was done by rackspace, as it is a rackspace hosted physical server, with their own SPLA provided licensing. If it worked on an instance I install on the server, and not on the production server, would definitely point to a setup issue, and I'd hopefully be able to mirror the two installs, so that the enterprise one was set up in the same way as the development one.
November 1, 2016 at 5:45 am
kyagi.jo (11/1/2016)
I got the server updated, but still no luck.It is now: Microsoft SQL Server 2016 (RTM-CU2) (KB3182270) - 13.0.2164.0 (X64) Build 9600
Looking at the previous versioning, this was actually fine, my local environment where it worked matched the production environment where it didn't; the build number being different was due to the OS difference (windows 10 vs windows server 2012 r2)
I am getting SSIS reinstalled now. If that doesn't work, I may try to install a SQL server developer edition on the server, to see how that behaves... The current install was done by rackspace, as it is a rackspace hosted physical server, with their own SPLA provided licensing. If it worked on an instance I install on the server, and not on the production server, would definitely point to a setup issue, and I'd hopefully be able to mirror the two installs, so that the enterprise one was set up in the same way as the development one.
What a nightmare this has turned out to be, for something which should just work. I have run out of ideas, and so wish you good luck in tracking this one down. And please post back when you do – it will be interesting to learn what the problem was, or, failing that, at least how to fix it.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 1, 2016 at 6:26 am
Thanks, there are still avenues to explore...
The reinstall of SSIS did not help.
I tried to make the script task in VB, no luck.
I tried the package deployment rather than project deployment, and the package actually runs fine in package deployment mode. Interesting, although not sure what this might mean the problem is, given that I used the same ispac file to deploy locally with the script task running fine, but with it failing (or rather, succeeding but not doing anything) on the server. And this is an extremely simple package with no variables etc. to speak of.
I still will try to figure out if I can get this working, as I don't see why project deployment should disable the script task, but I am happy that I have a workaround, makes me feel like I am actually getting somewhere 🙂
November 1, 2016 at 6:35 am
kyagi.jo (11/1/2016)
Thanks, there are still avenues to explore...The reinstall of SSIS did not help.
I tried to make the script task in VB, no luck.
I tried the package deployment rather than project deployment, and the package actually runs fine in package deployment mode. Interesting, although not sure what this might mean the problem is, given that I used the same ispac file to deploy locally with the script task running fine, but with it failing (or rather, succeeding but not doing anything) on the server. And this is an extremely simple package with no variables etc. to speak of.
I still will try to figure out if I can get this working, as I don't see why project deployment should disable the script task, but I am happy that I have a workaround, makes me feel like I am actually getting somewhere 🙂
Interesting. Sounds like it may be an issue with SSISDB then. If this (possibly problematic) SSISDB is currently unused, you might think about recreating it. Unfortunately, I don't know how to do that & my Google searches are not throwing up anything useful.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 1, 2016 at 7:20 am
I just deleted and recreated the SSISDB... no luck, the deployed package still has the same problem!
November 1, 2016 at 7:35 am
Second breakthrough - I can deploy the package with no issues through the SSDT UI, it is only the ispac file that is not deploying properly! Very odd, I did not expect this at all 😀
November 1, 2016 at 7:35 am
kyagi.jo (11/1/2016)
I just deleted and recreated the SSISDB... no luck, the deployed package still has the same problem!
Outstanding! [sarcasm]
OK, could it somehow be a permissions thing?
How are you executing the package? Different options:
1) Right click in SSMS and 'Execute'
2) Via a SQL Agent job
3) From a stored proc
4) From the command line
If you haven't tried option (2) yet, please give that a go.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 1, 2016 at 7:40 am
kyagi.jo (11/1/2016)
Second breakthrough - I can deploy the package with no issues through the SSDT UI, it is only the ispac file that is not deploying properly! Very odd, I did not expect this at all 😀
Are you deploying from SSMS 2016?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 1, 2016 at 7:45 am
I was using SSDT 2015 and building the solution, producing an ispac file, dropping it on the server, and running (double clicking) the ispac file, to deploy the project to server.
It was only until I watched a youtube tutorial just now, I realised I had the option to right click my solution and just click "deploy".
The right click my solution, then deploy to server, works (script task now delays for 30 seconds for my simple example task). The ispac file on the server doesn't work (script task is ignored).
Not using SSMS to deploy, but I am using it to execute. I have tried executing through SQL server agent job, and direct through the SSISDB package that I deployed.
Oh and also ran it through executing the sproc (although it was more scripting out the UI execute I was running, and running that, wasn't clear on exactly what the SQL was doing).
November 1, 2016 at 8:03 am
kyagi.jo (11/1/2016)
I was using SSDT 2015 and building the solution, producing an ispac file, dropping it on the server, and running (double clicking) the ispac file, to deploy the project to server.It was only until I watched a youtube tutorial just now, I realised I had the option to right click my solution and just click "deploy".
The right click my solution, then deploy to server, works (script task now delays for 30 seconds for my simple example task). The ispac file on the server doesn't work (script task is ignored).
Not using SSMS to deploy, but I am using it to execute. I have tried executing through SQL server agent job, and direct through the SSISDB package that I deployed.
Oh and also ran it through executing the sproc (although it was more scripting out the UI execute I was running, and running that, wasn't clear on exactly what the SQL was doing).
Sounds like you have a solution. I'll bet that was a relief.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 1, 2016 at 8:10 am
Yeah, spent like 2.5 days, and was running out of options before today; was worried we'd purchased a SQL server enterprise license and would be crippled by being unable to use script tasks for the more complex pieces of logic 😀
Thanks for the assistance.
November 1, 2016 at 8:22 am
kyagi.jo (11/1/2016)
Yeah, spent like 2.5 days, and was running out of options before today; was worried we'd purchased a SQL server enterprise license and would be crippled by being unable to use script tasks for the more complex pieces of logic 😀Thanks for the assistance.
No problem. My advice was of no use whatsoever 😀
Every month or so, I experience something similar. I expect something to take 10 minutes and instead I burn a day or two. I guess that's the nature of the industry we're in.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply