March 3, 2006 at 9:57 am
Has anyone used variables in SSIS packages yet? I am trying to pass parameters to a stored proc which is the OLEDB source sending data to to a delimited text file. I need variables for the parameters, file name and path. DTS 2K used to have friendlier interfaces for variables and dynamic properties and even ways if tying them together. SSIS says it's more powerful in this area with variable expressions, but none of them seem to apply to what I'm looking for.
Please help or point me in the direction of some good web documentation that I can review.
THX!!!
"If you don't take the time to do it right, when are you going to find the time to do it over?"
March 3, 2006 at 1:08 pm
I am having the same issue using a script task. I can not figure out how to select the variables I have created and put a value in them
March 3, 2006 at 2:02 pm
This is as far as I got. It runs but it never stops. I created a variable called iEmail so if there are records i know whether or not to e-mail the data. Maybe someone could debug this to tell me what I am doing wrong as far as setting the variables
Public Sub Main()
Dim ds As New DataSet
Dim ireturn As Integer, ssql As String
Dim Conn As SqlClient.SqlConnection
Conn = _
DirectCast(Dts.Connections("BLOODHOUND.Navision_Recipe_Control1").AcquireConnection(Dts.Transaction), _
SqlClient.SqlConnection)
ssql =
"SELECT Count(*) as RowCnt "
ssql = ssql &
" FROM dbo.Tbl_Result_Batch "
Dim cmd As New SqlCommand(ssql, Conn)
Dim da As New SqlDataAdapter(cmd)
Try
Conn.Open()
Catch ex As Exception
End Try
da.Fill(ds,
"Tbl_Result")
Dim vars As Variables
If CInt(ds.Tables("Tbl_Result").Rows(0).Item("RowCnt")) = 0 Then
Dts.VariableDispenser.LockOneForWrite(
"iEmail", vars)
vars(
"iEmail").Value = True
Else
Dts.VariableDispenser.LockOneForWrite(
"iEmail", vars)
vars(
"iEmail").Value = False
End If
'MsgBox("Your here")
Dts.TaskResult = Dts.Results.Success
Conn.Close()
End Sub
March 3, 2006 at 2:25 pm
Got it to work. Code is below. Make sure on the script task, the readWrite Variable is put in there
If CInt(ds.Tables("Tbl_Result").Rows(0).Item("RowCnt")) = 0 Then
Dts.Variables(
"iEmail").value = True
Else
Dts.Variables("iEmail").value = False
End if
March 5, 2006 at 10:47 pm
For script tasks, you need to list the variables you will use as either ReadOnly or ReadWrite on the general tab.
For calling sprocs with the builtin SSIS execute sql task, things are a bit messy, with some bugs in the current SSIS version. The safest solution is to build the entire call dynamically in the expression editor, with string concatenation. But, this workaround only is acceptable if you have no OUTPUT params -- if you have OUTPUT params, you have to get parameter passing working of course.
Otherwise, it depends on whether you're using ADO.NET or OLEDB, and I forget the details at the moment.
This would be a great place for a url to details on how to deal with at least ADO.NET and OLEDB, and I've seen such an article, but cannot recall where.
March 6, 2006 at 1:51 pm
Thanks, Guys! This thread led me down the right path. BOL had some useful info as well:
http://msdn2.microsoft.com/en-us/library/ms140355.aspx
http://msdn2.microsoft.com/en-us/library/ms141003.aspx
Pay close attention to the advice mentioned here as well as whether or not you select "True" or "False" for the IsQueryStoredProcedure parameter - it's an option, not requred and the calls with parameters are different for each.
"If you don't take the time to do it right, when are you going to find the time to do it over?"
January 26, 2007 at 2:37 pm
What happen if my connection is OLE_DB, it doesn't work, I have the same issue, the parameter is not passing
I also have SP2 for SQL Server 2005
I discover something about SPT2 CTP for SQL Server 2005, don't install, It's not working fine
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply