I have a package-level variable [User::viewName], type = string, containing a view name. I want to setup an OLE DB source to use this variable value as the source, so Data access mode = "Table name or view name variable". The Variable name dropdown contains [User:viewName], so I select it. When I click OK to leave the edit dialog I get the error: The variable User::viewName is required to be of type "VT_BSTR". Only variables of type String occur in the Variable name dropdown; if I try changing it to a type other than stringit doesn't occur in the dropdown. What is VT_BSTR and how can I change the variable type to it?

I've never seen VT_BSTR in my life, that isn't a data type that I know of. Something is awry here - I wonder if it could be package corruption. Does the same happen when you try a differrent string variable? -Jamie

I'm copying and pasting a package in Solution Explorer, then playing with the copy to see what works, then going back to the original. The package this problem is occurring in is a copy; might that be the problem? This seems like such an ordinary thing to do (get the table or view name from a variable) that I'm really surprised it's happening. Are there known problems with copying packages?

I don't know of any - but it does sound as though some corruption has occurred somewhere. Can you share the contents of the .dtsx file? (i.e. open it in notepad and copy the contents to here)? -Jamie

mruniqueid wrote:I'm copying and pasting a package in Solution Explorer, then playing with the copy to see what works, then going back to the original. The package this problem is occurring in is a copy; might that be the problem? This seems like such an ordinary thing to do (get the table or view name from a variable) that I'm really surprised it's happening. Are there known problems with copying packages?Can't think it matters, but be sure you generate a new GUID on the copied package. Control-flow background: right click, properties. Select the drop down in the ID field and generate a new GUID.

In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try toset Data access mode = "Table name or view name variable" in the original package I run into this trouble. Itried creating a new package from scratch and realized I've got a knowledge gap. If you create an OLE DB Source and set Data access mode = "Table name or view name variable" right from the start, then how do you define the source output columns since there's no table to derive them from? I have several views with the same structure; in my original package I picked one of these as "Name of the table or the view" which ofcourse defined output columns for the source,then tried to change Data access mode to a variable as described above. Can I start with Data access mode = variable? If I try that I get the error "A destination table name has not been provided". I created an OLE DB Destination and connected it to the OLE DB Source but the error persists. What is the proper sequence of steps and settings to use a variable name for Data Access Mode in an OLE DB Source?

mruniqueid wrote: In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try toset Data access mode = "Table name or view name variable" in the original package I run into this trouble. Itried creating a new package from scratch and realized I've got a knowledge gap. If you create an OLE DB Source and set Data access mode = "Table name or view name variable" right from the start, then how do you define the source output columns since there's no table to derive them from? The name of the table needs to be stored in the variable. mruniqueid wrote: I have several views with the same structure; in my original package I picked one of these as "Name of the table or the view" which ofcourse defined output columns for the source,then tried to change Data access mode to a variable as described above. Can I start with Data access mode = variable? If I try that I get the error "A destination table name has not been provided". I created an OLE DB Destination and connected it to the OLE DB Source but the error persists. What is the proper sequence of steps and settings to use a variable name for Data Access Mode in an OLE DB Source? 1. Create the variable of type string 2. Add the table name into teh variable 3. Create your OLE DB Source and select the variable that you have just chosen. -Jamie

I didn't realize the variable value was evaluated at design time, since it's set at runtime. Makes sense though - thanks a lot! - Dana

Was wondering if this worked after you did it the way it was suggested. I am getting an error which says "Error: The type of the value being assigned to variable "User:heetname" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. "

This error can occur if you assign NULL to a string variable, for example. It means exactly what it says - the value you are assigning is not of the data type the variable was created with. If you explain your scenario a little more, we can help troubleshoot why the error is coming up.

Look at the properties of your OLE DB Connection Manager. Set Access Mode to Open Rowset from Variable and fill in the OpenRowsetVariable.