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 string it 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 to set Data access mode = "Table name or view name variable" in the original package I run into this trouble.
I tried 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 of course 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?
|||In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try to set Data access mode = "Table name or view name variable" in the original package I run into this trouble.
I tried 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.
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 of course 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
No comments:
Post a Comment