Run a script with Invoke-Sqlcmd in SSIS Package
Hello everybody :-)
With your help last time, I created a nice script to create views automatically. Now, I need to run the script automatically. How do you think I can launch it in an SSIS Package? Which component?
Thank you in advance :-)
There is the script:
$server='server'
$Instance='instance'
$SrvInstance="$Server\$Instance"
$Base="baseName"
$Users=Invoke-Sqlcmd -Query "SELECT DISTINCT Name,Login FROM GP" -ServerInstance $SrvInstance -Database $Base
$Users| Foreach {
$checkTable="SELECT TABLE_Name FROM [baseName].[INFORMATION_SCHEMA].[VIEWS] WHERE TABLE_Name = 'GP_$($_.Name)'"
$result = Invoke-Sqlcmd -Query $checkTable -ServerInstance $SrvInstance -Database $Base
if (!$result)
{
$Query=@"
CREATE VIEW [GP_$($_.Name)] AS SELECT [Enabled],[Login]
FROM A7 CROSS JOIN AD
WHERE (A7.Login LIKE '%$($_.Name)%') AND (A7.Name = AD.Login)
"@
Write-host $_.Name 'View Created' -fore blue
Invoke-Sqlcmd -Query $Query -ServerInstance $SrvInstance -Database $Base
}
}
September 8th, 2012 10:09am
Hi,
you can use Execute Process task, Please follow this link for execute process task.
1.) http://rjbook.wordpress.com/2009/07/15/powershell-and-ssis/
2.) http://msdn.microsoft.com/en-us/library/ms141166.aspxRegards Harsh
Free Windows Admin Tool Kit Click here and download it now
September 8th, 2012 10:17am
Looking at the code I can see it is a PowerShell script.
I guess you need to pass in the three variables listed at the top.
So in general, your best mechanism to executing a PoSh script would be the Execute Process Task, and this question is answered there:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/216d2ee6-0f04-480f-808d-8241bc4a8d18/
It also includes the command to pass in parameters.
Arthur My Blog
September 8th, 2012 10:17am
Hmm Ok. I have already used this component (and I know how to then :-)). I feel I am going to have some trouble with this cmd invoke-sqlcmd... it's blocked by the firewalls. I will try and tell you if I succeed.
Thx!
Free Windows Admin Tool Kit Click here and download it now
September 8th, 2012 10:32am
Hey I think that's it! I have just discovered I can run SQLPS.exe . And there, I can run Invoke-SQLCMD.
1 - Add the Execute Process Task
2 - In Executable Section write the path to sqlps.exe: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe
3 - In Argument Section, write the path to your script: C:\Users\Desktop\myWonderfullScript.ps1
It works ;-) Thanks!!
September 8th, 2012 10:55am