Create a table and then using it as OLE DB Destination component

I want to do the following in a package:

Create a table at the beginning of a package (using a ExecuteSQLTask component) and then use the created table as a OLE DB destination component, later on the package.

Is this possible in SSIS?

The problem I run into is that I have to point the OLE DB destination component to a table and set up mappings, however as the table does not exist until the package is running, it does not seem to be possible.

I've looked at:

http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

Which is slightly similar to what I want, but the table I create would not be a temp tables, and I need to set up mappings and I don't see how this is possible.

Thanks

To design and build the package you will need to create the table. Once that has been completed, the table can be dropped, and you can rely on the table created in the Exec SQL Task for the actual running of the package. You will however need to change the DelayValidation property to be True, on the Data Flow Task. This will prevent the task (and components it contains) from being validated, and failing due to the missing table. The Data Flow Task will still be validated, immediately prior to execution, but by that stage the Exec SQL Task will have completed, so the table will then exist.|||

Ok, thanks for the answer but thats whats was mentioned on the cochango link. The difference here is that I do not wish to drop the table after it has been created, I want to create it, use it and keep it...

I'm currently creating packages in C#, however the problem occurs when I make the call:

// Establish a connection

componentInstance.AcquireConnections(null);

// Initialize the metadata

componentInstance.ReinitializeMetaData();

The component is question here is a OLE DB Destination component, it throws an exception because the table does not exist in the database whilst the package is being created.

Now, I know I can set DelayValidation on the Task which contains the destination component

It would be nice if I could:

1. set DelayValidation = true on the Task which contains this component

2. Skip the componentInstance.ReinitializeMetaData() method

3. Set up mappings between the input collection and external meta columns (each of which I would have to create)

However, if I skip the method componentInstance.ReinitializeMetaData I receive there is no inputCollection available to map to, so when you call:

// Get the destination's default input and virtual input.

IDTSInput90 input = component.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

there is are no columns available....

Therefore, my question is, can this be done in a C# environment, or does the same rule as creating this in the Designer apply, whereby I would have to have the table available in the database on the creation of the package in order to create the package?

CR11 - How do I choose a NULL value (or ALL RECORDS) in a dynamic parameter?

We are using CR11 and our own OLE DB provider that, in turn, uses our SQL Server database.

We are creating dynamic parameters that allow the user to pick from all the distinct values that a field can have. All but NULL, and we need to allow the user to pick NULL if there's any in the data.

In reality, we just need to find a way to let the user pick ALL values. However, if we create a dynamic parameter that doesn't accept multiple values, the user is forced to pick one of the available values or, if he doesn't, then CR will say the value is not valid when you hit OK to the parameter selections. If instead we create a parameter that accepts multiple values instead, the user has the option to pick all the available values, which at first seems to solve the problem, but these wouldn't include NULLs and those records would be filtered out in the results.

Thank you in advance.Anybody?|||You can try using two separate parameters in your Crystal Report (http://www.shelko.com). One can be a choice between all or specific and the second could be your list of specifics. Then base your selection criteria on the combination of these two parameters.