September 27, 2011 at 3:41 am
I have a simple PS script which reads the first line of a text file:
param
(
$File = "C:\SSIS\Import\ACME1_ExternalEvents_Test.txt"
)
$FirstLine = (get-content $File)[0]
Write-Output $FirstLine
This script is called from a SSIS process task which appears to function correctly. I have set the process task to use a 'Standard Output Variable' of @User::FirstLine.
There is a further SQL control flow task which makes use of this variable and creates a table.
The SQL task works correctly when I specify the @User::FirstLine variable directly but fails when I try to use the value when it is set by the powershell process task.
How do I associate the output of the PS script with the the 'Standard Output Variable' of the process task? I think it's a problem with my lack of understanding of Powershell.:(
September 27, 2011 at 8:05 am
It appears the variable is passed through, however.......
I changed the SQL Task to:
INSERT Test
VALUES (?)
so that enabled me to see the variables value in a table.
However, there are two spaces added into the variable at the 78th character no matter what text file is being viewed. eg
ID|title|Forename|Othername|Surname|Address1|Address2|Address3|Address4|Addr ess5|Town|County|Postcode
The spaces appear in the middle of Address5??
Is this another design feature or does anyone a fix?
September 27, 2011 at 9:03 am
I've found the problem if anyone is interested.
Write-Output limited the result set to the size of my powershell window which meant the second half of the output was put on a new line even though I wasn't viewing via Powershell. SSIS interpreted this 'newline' as two spaces.
I've changed the command to Write-Host and all works fine. I can now create a dynamic table using SQL based on the header information in a text file.
October 3, 2011 at 5:18 pm
Nice work. Thanks for sharing that.
This may save you a bit of time down the line. You can add hooks to the PowerShell runtime directly into your SSIS environment enabling you to communicate to and from a PowerShell session without going through an Execute Process Task (CmdShell). Check out this add-in http://www.youdidwhatwithtsql.com/powershell-script-task-for-ssis/488 (via http://powershellscripttask.codeplex.com/[/url]. I have demoed it and it works as advertised.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 2, 2012 at 1:30 pm
Hi,
I am having similar issue the one you described in the first post. I am using Power Shell Script to check the connection status (ping) of remote server before proceeding with the SSIS tasks. The output of Power Shell script (True/False or 1/0) is passed to User variable ServerStaus in the SSIS package. If I use this variable (ServerStatus) in Precedence Constraint with condition @ServerStatus == "True", the flow doesn't go to the next task. But if I remove the expression from the precedence constraint and use only constraint condition, the flow continues to next SSIS task.
Basically my next SSIS task is Send Mail task where I am checking the value of variable (ServerStatus) in the subject line. So when I don't have expression with condition, I get correct value in my email. But when I use expression along with constraint, the task flow doesn't proceed further.
Any idea, what's going wrong on my side???
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply