Hello!
I have a PowerShell script that I have ran using a few different ways (Directly in ISE, Scheduled Task, SCOM, etc…) and it works fine. The script connects to a SQL Database, runs a query, stores the results in a dataset and then I use that dataset to populate another application. I want to be able to run this on a schedule using Jenkins. I have tried using both the PowerShell plugin and to run it using a bat file calling the ps script. It fails on this command:
$recordCount = $sqlAdapter.Fill($dataSet)
Specifically, the fill command won’t work. Does anyone have any insight to get this working? TIA!!
Script:
#Variables - details of the connection, stored procedure and parameters
$uid = "<User>"
$SecurePassword = $env:<USER> | ConvertTo-SecureString -AsPlainText -Force
$connectionString = "server=<Server>;database='<Database>';trusted_connection=true;User ID = $uid; Password = $SecurePassword;";
$Query = "<QUERY>"
$Date = (Get-Date -Format "MM-dd-yyyy-HH-mm")
#SQL Connection - connection to SQL server
$sqlConnection = new-object System.Data.SqlClient.SqlConnection;
$sqlConnection.ConnectionString = $connectionString;
#SQL Command - set up the SQL call
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$sqlCommand.Connection = $sqlConnection;
$sqlCommand.CommandText = $Query;
#SQL Adapter - get the results using the SQL Command
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCommand
$dataSet = new-object System.Data.Dataset
$recordCount = $sqlAdapter.Fill($dataSet)
#Close SQL Connection
$sqlConnection.Close();
#Get single table from dataset
$data = $dataSet.Tables[0]
Error Message:
Started by user Damon
Running as SYSTEM
[EnvInject] - Loading node environment variables.
Building in workspace C:\Users<User>\AppData\Local\Jenkins.jenkins\workspace\SWIS\SWIS-Test
[EnvInject] - Inject global passwords.
[EnvInject] - Mask passwords that will be passed as build parameters.
[SWIS-Test] $ powershell.exe -NonInteractive -ExecutionPolicy Bypass -File C:\Users<User>\AppData\Local\Temp\jenkins14054267963543193435.ps1
Exception calling “Fill” with “1” argument(s): “Login failed for user ‘’.”
At C:\Users<USER>\AppData\Local\Temp\jenkins14054267963543193435.ps1:54 char:1
- $recordCount = $sqlAdapter.Fill($dataSet)
-
+ CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : SqlException
Build step ‘PowerShell’ marked build as failure
Finished: FAILURE
Jenkins info:
Jenkins: 2.441
OS: Windows Server 2022 - 10.0
Java: 21.0.2 - Oracle Corporation (Java HotSpot™ 64-Bit Server VM)
active-directory:2.34
ant:497.v94e7d9fffa_b_9
antisamy-markup-formatter:162.v0e6ec0fcfcf6
apache-httpcomponents-client-4-api:4.5.14-208.v438351942757
bootstrap5-api:5.3.2-3
bouncycastle-api:2.30.1.77-225.v26ea_c9455fd9
branch-api:2.1144.v1425d1c3d5a_7
build-timeout:1.32
caffeine-api:3.1.8-133.v17b_1ff2e0599
checks-api:2.0.2
cloudbees-folder:6.858.v898218f3609d
commons-lang3-api:3.13.0-62.v7d18e55f51e2
commons-text-api:1.11.0-95.v22a_d30ee5d36
credentials:1311.vcf0a_900b_37c2
credentials-binding:642.v737c34dea_6c2
dark-theme:416.v535839b_c4e88
display-url-api:2.200.vb_9327d658781
durable-task:543.v262f6a_803410
echarts-api:5.4.3-2
email-ext:2.103
envinject:2.908.v66a_774b_31d93
envinject-api:1.199.v3ce31253ed13
font-awesome-api:6.5.1-1
git:5.2.1
git-client:4.6.0
github:1.37.3.1
github-api:1.318-461.v7a_c09c9fa_d63
github-branch-source:1767.va_7d01ea_c7256
gradle:2.9
gson-api:2.10.1-15.v0d99f670e0a_7
instance-identity:185.v303dc7c645f9
ionicons-api:56.v1b_1c8c49374e
jackson2-api:2.16.1-373.ve709c6871598
jakarta-activation-api:2.0.1-3
jakarta-mail-api:2.0.1-3
javax-activation-api:1.2.0-6
javax-mail-api:1.6.2-9
jaxb:2.3.9-1
jjwt-api:0.11.5-77.v646c772fddb_0
jquery3-api:3.7.1-1
json-path-api:2.8.0-21.v8b_7dc8b_1037b_
junit:1256.v002534a_5f33e
ldap:711.vb_d1a_491714dc
mailer:463.vedf8358e006b_
matrix-auth:3.2.1
matrix-project:822.v01b_8c85d16d2
mina-sshd-api-common:2.11.0-86.v836f585d47fa_
mina-sshd-api-core:2.11.0-86.v836f585d47fa_
okhttp-api:4.11.0-157.v6852a_a_fa_ec11
pam-auth:1.10
pipeline-build-step:540.vb_e8849e1a_b_d8
pipeline-github-lib:42.v0739460cda_c4
pipeline-graph-analysis:202.va_d268e64deb_3
pipeline-groovy-lib:689.veec561a_dee13
pipeline-input-step:477.v339683a_8d55e
pipeline-milestone-step:111.v449306f708b_7
pipeline-model-api:2.2168.vf921b_4e72c73
pipeline-model-definition:2.2168.vf921b_4e72c73
pipeline-model-extensions:2.2168.vf921b_4e72c73
pipeline-rest-api:2.34
pipeline-stage-step:305.ve96d0205c1c6
pipeline-stage-tags-metadata:2.2168.vf921b_4e72c73
pipeline-stage-view:2.34
plain-credentials:143.v1b_df8b_d3b_e48
plugin-util-api:3.8.0
powershell:2.1
resource-disposer:0.23
scm-api:683.vb_16722fb_b_80b_
script-security:1313.v7a_6067dc7087
snakeyaml-api:2.2-111.vc6598e30cc65
ssh-credentials:308.ve4497b_ccd8f4
ssh-slaves:2.948.vb_8050d697fec
structs:325.vcb_307d2a_2782
theme-manager:215.vc1ff18d67920
timestamper:1.26
token-macro:400.v35420b_922dcb_
trilead-api:2.133.vfb_8a_7b_9c5dd1
variant:60.v7290fc0eb_b_cd
workflow-aggregator:596.v8c21c963d92d
workflow-api:1283.v99c10937efcb_
workflow-basic-steps:1042.ve7b_140c4a_e0c
workflow-cps:3837.v305192405b_c0
workflow-durable-task-step:1313.vcb_970b_d2a_fb_3
workflow-job:1385.vb_58b_86ea_fff1
workflow-multibranch:770.v1a_d0708dd1f6
workflow-scm-step:415.v434365564324
workflow-step-api:639.v6eca_cd8c04a_a_
workflow-support:865.v43e78cc44e0d
ws-cleanup:0.45