how to get external variable value in dtsx package
I am executing .dtsx package from c#, it was executing fine, if i am passing one variable value from c# code then how can i get it on .dtsx package for my ole db source query. Here is my c# code.
string file = @"D:\CYNCZFuzzy\CYNCZFuzzy\Contact.dtsx";
package = app.LoadPackage(file, null);
Variables vars = package.Variables;
vars["User::parentContactID"].Value = 1028203;
pkgResults = package.Execute();
string result = pkgResults.ToString();
I need this 1028203 value on my ole db source query, here my query.
select cr.MasterContactID as ParentContactID,
c.ID,C.FirstName,
C.Middl开发者_运维技巧eName,
c.LastName,
c.ID as FieldID
from Contact c inner join
ContactRelation cr on cr.SlaveContactID = c.ID
where RelationshipID = 1
AND cr.MasterContactID = ?
what I should write on ? for getting 1028203 value from c# page.
Thanks in advance...
You have to map variables to parameters in the Execute SQL task Also see Working with Parameters and Return Codes in the Execute SQL Task and OLEDB Source.
Unfortunately, the documentation does go around the houses a bit and some stuff is SSIS requires mental Judo.
Edit your OLE DB source component.
On the first dialog you see, Connection Manager should be highlighted to the left.
On the right will be your connection manager name in the first box (OLE DB connectionManager:), SQL Command in the second box (Data access mode:), and the third box will have your Sql text as you provided in your question above (SQL command text:).
To the right of the SQL command text: box, there will be four buttons.
The top button in this group is named Parameters.... Clicking this button will bring up a child dialog box. There is only one box in this dialog labelled Mappings:
Under the Parameters heading in this box there should be a parameter named Parameter0 (this is prepopulated based upon the number and location of the question marks in your query, i.e. the first ? in your query creates a parameter0, the second question mark creates a parameter1, and the nth question mark in your query creates a parameter(n-1). The other column in this box is labelled Variables.
If you click in the textbox below Variables you can see that it becomes a dropdown textbox. You can drop this down and select the entry that matches your variable "User::parentContactID" then click OK to close the child dialog and OK again to close the OLE DB Source edit dialog box. This will add the parameter in the manner you asked for in your original question.
精彩评论