locked
Use Variable RRS feed

  • Question

  • I have a field called 'Body'in tblA, and have a ssis Pkg variable caled (USER::Body) .
    I need to be able to pick up this body variable
    one at a time from the tblA and import that field to a txt file.
    I have a DataFlow task setup with SQL Command as data access mode and foll statement in it. This works.
    But I want to use the variable (USER::Body) instead of the actual field Body.

    Currently my qry works and it looks like the one below.

    select body  from tblA
    where id=?

    Pl note id field above is Mapped to the Parameter called @Id

    How can I change this so I can use the variable (USER::Body)

    Wednesday, August 26, 2009 7:48 PM

Answers

  • Desigal,
    Instead of selecting the varibale in OLEDB Source (data access mode as SQL Command from varibale), if you follow the approach of a dummy query in OLEDB Source and then a derived column with expression as
    (DT_TEXT,1252)@[User::Body], you will get the values in DEstination without any error.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by Tony Tang_YJ Thursday, September 3, 2009 6:11 AM
    Friday, August 28, 2009 5:47 AM

All replies

  • "You need to pick up the body variable one at a time from table".....please explain

    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, August 26, 2009 8:00 PM
  • I need to be able to pick up the variable User::Body, instead of the field Body.
    Wednesday, August 26, 2009 8:20 PM
  • You can use derived column inside DFT using User::Body variable.

    OR YOU WANT SOMETHING LIKE
    SELECT @[User::Body] as SQL Command inside OLEDB Source? (I dont think this is possible)

    OR
    Select Body From table
    Where Body = ? AND ID = ?


    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, August 26, 2009 8:26 PM
  • When I pick the Data Access mode as Tablename or view name variable, and chose the variable name as [User::Body]
    it returns err Destination table name has not been provided.
    Wednesday, August 26, 2009 9:56 PM
  • How are you populating the variable Body?
    Nitesh Rai- Please mark the post as answered if it answers your question
    Wednesday, August 26, 2009 10:02 PM
  • Desigal,

    Your posts are not clear enough. Does the 'body' variable have a column name or a table name? Please clarify and better yet, provide an example of what you are trying to do.
    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    Thursday, August 27, 2009 3:01 AM
  • Body is a column name. Currently I have this statement in my sql command

    select body  from tblA
    where id=?

    I need to replace the Body field with a SSIS Variable that I have called User::Body

    Thursday, August 27, 2009 3:18 AM
  • Ok, Hind of got it.

    You need to use an additional variable (string) to build the query via expression (http://rafael-salas.blogspot.com/2007/11/ssis-mapping-parameter-inside-of.html).

    Now, the only thing is that is the content of the 'body' variable changes, because let's say, you want to specify a different column name, the package will fail validation and won't run. This is because a data flow does not support changes of the metadata (change data types, columns names, etc) at run time.

    Perhaps if you explain your ultimate goal or provide an use case, we could help you better.
    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    Thursday, August 27, 2009 3:59 AM
  • Ok Rafeal,

    I created a variable called SourceSql and put the expression as evaluate expression =true
    with the foll expression

    "Select @[User::Body]"-- Pl note I dont want to select * from my table again, to get the body since I have already captured that in the variable @[User::Body]"--

    Then in oledb source compnoent- I chose the SQL Command from variable and picked 'user::SourceSql' .
    When I then hit columns, it gives the foll err
    Must declare the scalar variabe "@"

    Thursday, August 27, 2009 7:23 PM
  • Desigal,
    You are able to capture the appropriate value in variable "Body"....correct?
    Now you want to use that varibale as source in DFT......rt?
    You said you have set an expression for a varibale SourceSQL as "Select @[User::Body]" but you are not able to use SourceSQL as a SQLCommand inside OLEDB source.
    Is it possible for you to create a OLEDB source with a trivial SQLCommand like Select Top 1 ColA from Table?
    After that use a derived column and
    DerivedColumn Name          Expression
    -----------------------------------------------
    MyColumn                          @[User::Body]

    Then take this new column MyColumn to a flat file destination.


    Nitesh Rai- Please mark the post as answered if it answers your question
    Thursday, August 27, 2009 7:36 PM
  • See - My point is I DONT Want to query on the UNDERLYING SQL Tbl, since I already have the data from the body field captured in the variable [user::body]. I dont want to hit the tbls 2 times.
    Thursday, August 27, 2009 8:22 PM
  • OK,
    Dont hit the same table.
    In OLEDB Source you can use Select 'Desigal' As Name . (I am just giving a dummy query. It has no significance)
    Then follow the rest of the steps I mentioned in my earlier post.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Nitesh Rai Thursday, August 27, 2009 9:01 PM
    Thursday, August 27, 2009 8:24 PM
  • Change you SourceSQL varibale as :
    "Select '" + @[User::Body] + "' AS Body"

    Now try to use data access mode as SQL Command from Varibale.
    It shoud work
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Nitesh Rai Thursday, August 27, 2009 9:01 PM
    Thursday, August 27, 2009 8:37 PM
  • That solution is helpful. However the issue I have now is that the sourcesql variable, that I am
    uing which has that expressions "Select '" + @[User::Body] + "' AS Body" is DT_STR Dataype.
    But the body field in sql is ntext. SO i put a data conversion task to convert
    that dt_str to dt_text. however pkg fails with truncation erros.
    Is there any other dataype other than str that will be able to handle the datatype
    of the variable? ie i need to increase the len of  the sourcesql variable.-in variables,
    didnot see much choices of datatypes othe than dt_Str
    Friday, August 28, 2009 3:03 AM
  • That solution is helpful. However the issue I have now is that the sourcesql variable, that I am
    uing which has that expressions "Select '" + @[User::Body] + "' AS Body" is DT_STR Dataype.
    But the body field in sql is ntext. SO i put a data conversion task to convert
    that dt_str to dt_text. however pkg fails with truncation erros.
    Is there any other dataype other than str that will be able to handle the datatype
    of the variable? ie i need to increase the len of  the sourcesql variable.-in variables,
    didnot see much choices of datatypes othe than dt_Str
    You're going to have issues with this approach anyway, Desigal59.  Your metadata returned from the query cannot change, or it will break the data flow.  That is, if you start selecting different columns from different tables on each execution, the package will fail 100% of the time.  The metadata is strictly enforced based on the query used to design the data flow mappings.
    Friday, August 28, 2009 3:38 AM
  • It is ALWAYS GOING TO BE This one column only, colums will NOT Be changed
    Friday, August 28, 2009 4:08 AM
  • It is ALWAYS GOING TO BE This one column only, colums will NOT Be changed
    I'm not talking about the "BODY" column, I'm talking about its contents.  If "BODY" contains "col1, col2, col3 from tableA" on one execution such that the query ends up being "select col1, col2, col3 from tableA" and on the next execution "BODY" contains "col3, col1, col4 from tableB" such that the query ends up being "select col3, col1, col4 from tableB" then the data flow will fail.  Even if the two executions select from the same table, if you have different columns, or the column ordering is different, then the metadata will have changed and hence cause the data flow to fail.

    In other words, if your "sourcesql" variable's contents are going to be changing, there is a high likelyhood that the data flow will fail.  Again, I'm not talking about the "body" column, I'm talking about the end result of the expression in your "sourcesql" variable.

    I'm trying to understand why an OLE DB Source component hooked up to a Text File destination won't work - can you elaborate on why you need to populate an SSIS variable?
    Friday, August 28, 2009 4:24 AM
  • no- my query will ALWAYS BE SELECT "USER:Body" from body field only. it will NEVER BE A DIFF COLUMN. Reason I want to use varia ble is because I have alraedy captured the body field in for each loop and  need to use that field to load to txt file.  I dont want to query tbl again and hence want to use the variable user::body
    Friday, August 28, 2009 4:46 AM
  • Desigal,
    Instead of selecting the varibale in OLEDB Source (data access mode as SQL Command from varibale), if you follow the approach of a dummy query in OLEDB Source and then a derived column with expression as
    (DT_TEXT,1252)@[User::Body], you will get the values in DEstination without any error.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by Tony Tang_YJ Thursday, September 3, 2009 6:11 AM
    Friday, August 28, 2009 5:47 AM