Bryan McKenney


I have searched the archives and haven't found a solution that works.

I started out with a package based on the Excel import wizard and have modified it to include a for each loop for processing more than one Excel source file and also modified it to do some dynamic SQL like dropping and creating the tables with names that are variable based. The package is cabable of processing more than one file, and each file has the same data elements or columns in it, but each file has to go in it's own table. The drop and preperation tasks use string variable SQL that does dynamically create the SQL and runs great. The data flow task and it's OLE destination task (which the wizard did intially create) does not perform as expected. There were some posts in the archives stating that for dynamic destination table names it can be done in the OLE destination but I can't get it to work. I tried using an access mode of table/view name variable and select a variable that is string expression based. But this variable isn't fully populated until run time and so when I'm trying to configure this I get a msg stating the table name object doesn't exist and I can't save the task.

Am I doing something wrong, or trying to do something that wrong way or trying to do something that isn't possible




Re: Dynamic Destination Table Issue - Can't find solution in archives

jwelch


You'll need to have a least one destination table set up at design time, so that the package can be validated. You may also want to set the DelayValidation property to true on the dataflow.







Re: Dynamic Destination Table Issue - Can't find solution in archives

Bryan McKenney

jwelch wrote:
You'll need to have a least one destination table set up at design time, so that the package can be validated. You may also want to set the DelayValidation property to true on the dataflow.



There is a table already that was created when I did the wizard initially and it's data elements match up and if I do a fast load to this table it works fine. But when I switch to a variable based table name I get the msg I mentioned in my original post. When I use the advanced editor for the OLE db destination task I can't see a DelayValidation property. I've got SP2 installed. I searched the SSIS online book but online found reference to this property as it relates to writing code but I'm not writing code - just using the SSIS UI for the destination tasks.






Re: Dynamic Destination Table Issue - Can't find solution in archives

Phil Brammer

Bryan McKenney wrote:


There is a table already that was created when I did the wizard initially and it's data elements match up and if I do a fast load to this table it works fine. But when I switch to a variable based table name I get the msg I mentioned in my original post. When I use the advanced editor for the OLE db destination task I can't see a DelayValidation property. I've got SP2 installed. I searched the SSIS online book but online found reference to this property as it relates to writing code but I'm not writing code - just using the SSIS UI for the destination tasks.


You need to have a default table name entered into the variable that you want to use in the OLE DB Destination.

The DelayValidation property is on the OLE connection manager object.





Re: Dynamic Destination Table Issue - Can't find solution in archives

jwelch

Phil Brammer wrote:
Bryan McKenney wrote:


There is a table already that was created when I did the wizard initially and it's data elements match up and if I do a fast load to this table it works fine. But when I switch to a variable based table name I get the msg I mentioned in my original post. When I use the advanced editor for the OLE db destination task I can't see a DelayValidation property. I've got SP2 installed. I searched the SSIS online book but online found reference to this property as it relates to writing code but I'm not writing code - just using the SSIS UI for the destination tasks.



You need to have a default table name entered into the variable that you want to use in the OLE DB Destination.

The DelayValidation property is on the OLE connection manager object.

The DelayValidation property is also on the DataFlow task. You'll likely need to set that one to true. The connection manager shouldn't require it unless you are changing database connections on the fly too.






Re: Dynamic Destination Table Issue - Can't find solution in archives

Phil Brammer

True.




Re: Dynamic Destination Table Issue - Can't find solution in archives

Bryan McKenney

Phil Brammer wrote:
Bryan McKenney wrote:





You need to have a default table name entered into the variable that you want to use in the OLE DB Destination.

The DelayValidation property is on the OLE connection manager object.


I can set the access mode to OpenRowset Using FastLoad From Variableto and set the OpenRowsetVariable to my variable using the advance editor and it doesn't check if the object exists already so I can make it stick. When I execute the package the preceeding preperation task has SQL that creates the table and that works so the table is there. The destination OLE db task can't find the table though. The variable I use for the table name that is passed to the destination task is a string and I have set it to different sting values and it's not working - msg in the progress tab says the object doesnt exist. The values of the string I've tried look OK to me. I've set it to different qualified table name string values including the following:

[Convergys].[dbo].[DPT_VER]
[dbo].[DPT_VER]
[DPT_VER]

The prep task SQL string value for creating the table is like this:
CREATE TABLE [Convergys].[dbo].[DPT_VER] .....

How come the table name string values that I tried don't work when the table was created in a previous step and does exist and the values for the variable containing the table name look legit to me





Re: Dynamic Destination Table Issue - Can't find solution in archives

Phil Brammer

Because the table DOESN'T exist at validation time.

You need to set DelayValidation to TRUE on the data flow and I'd also set it on the OLE DB connection manager object.

Also, I'd probably set ValidateExternalMetadata to FALSE on the OLE DB Destination component.





Re: Dynamic Destination Table Issue - Can't find solution in archives

Bryan McKenney

Phil Brammer wrote:
Because the table DOESN'T exist at validation time.

You need to set DelayValidation to TRUE on the data flow and I'd also set it on the OLE DB connection manager object.

Also, I'd probably set ValidateExternalMetadata to FALSE on the OLE DB Destination component.


I have set DelayValidation to TRUE for the dataflow task and the destination connection. Also set the ValidateExternalMetadata to FALSE. Still say's it can't find the table to load in to.





Re: Dynamic Destination Table Issue - Can't find solution in archives

Bryan McKenney

Got it to work. The string value of the table name has to be just "tablename" with no brackets.

Thanks for the help.