how to get external variable value in dtsx package.

Question

Hi,

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.MiddleName, 
        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...

Answer 1

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.

Answer 2

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.