Use variables in Data Flow

• Helical Johan

  Ten Centuries

  Points: 1033

  More actions

  January 30, 2009 at 6:31 pm

  #206140

  Hi, I declared a global variable, but i need use it, inside Data Flow in OLE DB SOURCE, as column of a query. How can i do it?

  My query is:

  SELECT IdentificationType,

  Number,

  XXXXXX

  FROM Persons with (nolock)

  The field XXXX is my variable...

  Thanks...

• alim.shaikh

  Old Hand

  Points: 330

  More actions

  January 31, 2009 at 1:55 am

  #935964

  hi frnd,

  i dont think there is any option of setting column name to any variable and then later using that variable as column name.

  becuase variables are used for column value and not for column name itself.

• alim.shaikh

  Old Hand

  Points: 330

  More actions

  January 31, 2009 at 2:26 am

  #935967

  Although i believe that dynamic query can be achieved by formatting query in the following way:

  declare @col varchar(30)

  set @col='empname'

  exec('select '+@col+',empname from test')

  declare @tab varchar(30)

  set @tab='test'

  exec('select empname from '+@tab)

  hope this will resolve your problem.

• Helical Johan

  Ten Centuries

  Points: 1033

  More actions

  January 31, 2009 at 2:53 pm

  #936024

  Hi, it don't resolve my problem, because i need the column value and not the column name itself.

  I want in the OLE DB SOURCE editor, write a query and i use a global variable:

  SELECT IdentificationType,

  Number,

  XXXXXX as Name

  FROM Persons with (nolock)

  If I write:

  SELECT IdentificationType,

  Number,

  ?

  FROM Persons with (nolock), It is a error, because it is not a parameter. It is a column value.

  Besides,

  SELECT IdentificationType,

  Number,

  User::FileId

  FROM Persons with (nolock), this is not allowed.

  Then, how can i write this query?

  Thanks...

• alim.shaikh

  Old Hand

  Points: 330

  More actions

  February 1, 2009 at 7:10 am

  #936149

  if i am not wrong what i thought earlier is you are using sql variable in back-end but it seems that you want to format your query in the front-end itself.

  so, it should be very simple to format query in front-end.

  and execute that query. wats a big deal? m i correct?

• John Dempsey

  Hall of Fame

  Points: 3165

  More actions

  February 2, 2009 at 3:25 pm

  #936779

  Helical Johan (1/31/2009)

  ---

  Hi, it don't resolve my problem, because i need the column value and not the column name itself.

  I want in the OLE DB SOURCE editor, write a query and i use a global variable:

  SELECT IdentificationType,

  Number,

  XXXXXX as Name

  FROM Persons with (nolock)

  If I write:

  SELECT IdentificationType,

  Number,

  ?

  FROM Persons with (nolock), It is a error, because it is not a parameter. It is a column value.

  Besides,

  SELECT IdentificationType,

  Number,

  User::FileId

  FROM Persons with (nolock), this is not allowed.

  Then, how can i write this query?

  Thanks...

  Hi Helica,

  I'm not sure if I understood exactly what you are trying to accomplish but I will try and offer some advice on what I think you are trying to do. You have stated you are using a global variable which you did not state is changing. So, I will make the assumption that the global variable will stay the same (though it could be changed by a For Each container that encapsulates the Data Flow object). Based on the assumption that the Global Variable will be the same for all rows in the the data flow I believe you can handle it as follows:

  1. Add your OLE DB Source object which contains the base query:

  SELECT IdentificationType,

  Number

  FROM Persons with (nolock)

  (Notice I did not add your variable into this query yet. This will provide you the columns from your table to be passed to the next Data Flow object without the variable value which will be added next.)

  2. Add a Derived Column object to the Data Flow dragging the arrow from the OLE DB Source object to the Derive Column object. (The columns from the OLE DB Source will be visible to this object, but we will be "adding" a new column to the data flow)

  3. Open the Derived Column editor dialog window. Based on what you have provided it appears you want to add a "FileId" column. So, in the "Derived Column Name" column of the editor enter FileId as the name. In "Derived Column" column select the option . Then in the "Expression" column of the editor you can enter an expression, in this case, you will enter @[User::FileId]. Then, choose the "Data Type" (probably string[DT_STR] unless it is unicode then string[DT_WSTR]), enter the "Length" of the new column, and choose the collation. It will now add this column to your Data Flow with the other columns making it visible to other objects.

  4. I will make the assumption you are placing the results back into an OLE DB Destination. So, add the OLE DB Destination object to you Data Flow connecting it with the arrow from the Derived Column object.

  5. Open the OLE DB Destination Editor. After you have setup the Connection Manager options, go to Mappings. Your Available Input Columns should include IdentificationType, Number, FileId which you can then map to your table in the OLE DB Destination.

  I hope that helps.

  John

• Helical Johan

  Ten Centuries

  Points: 1033

  More actions

  February 2, 2009 at 4:00 pm

  #936788

  thanks so much....