October 14, 2009 at 2:17 pm
Hi All
My SSIS package is executed from C# code.
I have a variable that I want passed from the C# code to the package. I know how to set it at the C# code end i.e.
ssisPackage.Variables["PaidWeight"].Value = '5'
but what I dont know is, how do i use this at the SSIS package end in a ExecuteSQL task. Do i create a package wide variable? Pls help.
Thank you
October 14, 2009 at 3:44 pm
In the execute SQL Task you will replace where you want to use it with "?", ie WHERE Field1 = ?
Then you will use the parameter mapping tab to say Variable1 maps to the first ?.
Now with that all said, you are somewhat limited in where you can use the ? to substitute. Anything more complex than a fairly simple insert or update or sproc call will probably not work. The way you get around that is to build the command in a variable (usually in a script task) and set the exec to run the variable..
CEWII
October 14, 2009 at 3:58 pm
Hi Elliot
Thank you for replying.
I discovered that what I needed was to pass the SSIS variable to an SQL statement in an 'OLE DB Source'
I am not using it in a where clause so Its not a parameter. To explain it futher , here s my SQL statement:
Declare @var AS Char(1)
set @var = <<<NEED to give my SSIS variable @[User::PaidWeight] here>>>
SELECTPaidWeight =
CASE
WHEN M.Weight = '0' THEN @var
ELSE CEILING(M.Weight)
END
FROM MailPieceBuffer M
Could let me know how i could use the variable i ve created in this. Thank you. I have nt found anything on the web related to this.
Thanku.
Gomz
October 14, 2009 at 4:19 pm
You won't be able to use it that way..
The workaround is to create another variable, I like Cmd of type string at the package level..
Add a Script Task, for the readonly variable put in PaidWeight and in the readwrite variables put in Cmd.
In the script itself replace then entire Public Sub Main with this:
Public Sub Main()
Dim _sWk As String
_sWk = "Declare @var AS Char(1)" _
& vbCrLf _
& "set @var = '" _
& Dts.Variables("PaidWeight").Value.ToString.Trim _
& "' " _
& vbCrLf _
& vbCrLf _
& "SELECT PaidWeight = CASE WHEN M.Weight = '0' THEN @var ELSE CEILING(M.Weight) END " _
& vbCrLf _
& "FROM MailPieceBuffer M"
Dts.Variables("Cmd").Value = _sWk
Dts.TaskResult = Dts.Results.Success
End Sub
Now change your Exec SQL Task to use a variable, SQLSourceType set to variable.
SourceVariable should be set to User::Cmd
What we have done is built the whole SQL and now just execute it, the parameters have already been replaced.
CEWII
October 24, 2009 at 10:56 am
Hi Elliot
Thank you for getting back to me.
From what I could see the Script task is best used when there are insertions to be made to a table. Here I need the out put to be used to write to a flat file.
Could u pls tell me how I can use this as an input to my flat file destination in data flow task.
Kindly help
Thank you
Gomz
October 24, 2009 at 4:55 pm
Hi Elliot
I solved my problem using the Data Flow task itself.
1. Created a store proc that accepts an input
2. Called the store proc in OLE DB Source
exec [dbo].[CreateFlatfileSource]
@PaidWeight = ?
3. In Parameters
Assigned @PaidWeight = [User::PaidWeight]
This put everything together. I am still interested in knowing how the Execute SQL Task output can be given to the Flat file destination.
Thanks
Gomz
October 25, 2009 at 7:41 pm
You'll need to provide more details about the EXEC sql task. As I read it you want to take its output and write it to a flat file. If that isn't what you meant then provide more details. If it was then that is not really a supported method, you may be able to pull it off but it is not a good way..
CEWII
October 26, 2009 at 10:39 am
First define a variable of string type and create your OLEDB Source query using expression and assign it to this new variable.
Next go to the OLDEDB Source component and use the option SQL Command from variable and use the variable you created.
Hope this helps!
Amol
Amol Naik
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply