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/%5B/url%5D. 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy