Determine table names and column names at runtime?

Hi

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.

Thanks.The only advice I can give is this: All your database's objects are stored in the sysobjects table. And then there's syscolumns and sysindexes (shouldn't that be sysindices?).

I would be suprised if there were not free libraries out there that make it easy to get information about sql objects. I don't know of one particularly. Anyone know of one? If not, that might make a cool community project.|||There is a stored procedure sp_tables that returns a list of tables.

There is a stored procedure sp_columns that returns a list of Columns.

These are better to use than the system tables, as they are documented and should not change in a way to break your code. Look them up in Books Online...|||Gravy!

Glad you're here.|||> There is a stored procedure sp_tables that returns a list of tables.

ah, forgot about that. I guess because I usually only use it to do an

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'whatever')

and an SP is no good there.... cheers for reminding me.|||In that case, you could/should use:

IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_name = 'whatever')

Terri

Detect whether runtime or executiontime in custom component

In my custom component in the validate() method I want to determine whether this method is called during runtime or during designtime. I Can't find a property for this on PipelineComponent or ComponentMetaData. Is it possible?

Regards,
HenkI don't think you can, and I also don't think it should matter. Why would you want this?|||Well, during validation I want to access a package level variable that is filled with a lookup table by a preceding task. However, at designtime, there is no such thing as a task that has executed previously, so the variable does not exist then.
|||Should the variable not exist, but the value may be rubbish? You cannot create variables at run-time (... I'm pretty sure you can't, no run-time structure changes allowed in SSIS). If something is really missing I think you are perhaps digging too deep into the validation, unless it is only certain settings that induce this issue.

The way to get round this would be to set DelayValidation to true, so validation only happens at runtime.

You would do this for a Exec SQL Task that requires a table for created by a preceeding task, such that the table will not exist until part through run-time. Normally it is good to validate the SQL, but in this case it will cause a problem, so we can bypass validation.|||

DarrenSQLIS wrote:

You cannot create variables at run-time

I'm afraid I didn't make myself clear. I do not create variables at runtime.

But is my observation correct that if you define a variable (at designtime of course!) at package level, that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime? (so it doesn't even contain rubbish, but it just isn't there)

(By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)|||

Henk v J. wrote:

that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime

You sure? I have a component that uses variables, actually the variable name is a property value, and I validate that the variable selected does exist. This works at design-time calls to Validate.

Henk v J. wrote:

By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)

Don't be shy. I'm intrigued now, what have you done?

|||No rocket-science, I just look whether the variable exists and if it doesn't I do nothing

By the way you are probably right about, because I can't see the variable at runtime either, so there is something else wrong... I post a separate issue on that.
Thanks for your reactions!|||Ok, I lied I don't use variables in Validate, but I do in AcquireConnections, so still in a component, and that is called during design and execution times.

if (VariableDispenser.Contains(location))
{
IDTSVariables90 vars = null;
VariableDispenser.LockOneForRead(location, ref vars);
return vars[0].Value.ToString();
}

|||Try me.Designmode property may be it will work for you.

Detect whether runtime or designtime in custom component

In my custom component in the validate() method I want to determine whether this method is called during runtime or during designtime. I Can't find a property for this on PipelineComponent or ComponentMetaData. Is it possible?

Regards,
HenkI don't think you can, and I also don't think it should matter. Why would you want this?|||Well, during validation I want to access a package level variable that is filled with a lookup table by a preceding task. However, at designtime, there is no such thing as a task that has executed previously, so the variable does not exist then.
|||Should the variable not exist, but the value may be rubbish? You cannot create variables at run-time (... I'm pretty sure you can't, no run-time structure changes allowed in SSIS). If something is really missing I think you are perhaps digging too deep into the validation, unless it is only certain settings that induce this issue.

The way to get round this would be to set DelayValidation to true, so validation only happens at runtime.

You would do this for a Exec SQL Task that requires a table for created by a preceeding task, such that the table will not exist until part through run-time. Normally it is good to validate the SQL, but in this case it will cause a problem, so we can bypass validation.|||

DarrenSQLIS wrote:

You cannot create variables at run-time

I'm afraid I didn't make myself clear. I do not create variables at runtime.

But is my observation correct that if you define a variable (at designtime of course!) at package level, that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime? (so it doesn't even contain rubbish, but it just isn't there)

(By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)|||

Henk v J. wrote:

that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime

You sure? I have a component that uses variables, actually the variable name is a property value, and I validate that the variable selected does exist. This works at design-time calls to Validate.

Henk v J. wrote:

By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)

Don't be shy. I'm intrigued now, what have you done?

|||No rocket-science, I just look whether the variable exists and if it doesn't I do nothing

By the way you are probably right about, because I can't see the variable at runtime either, so there is something else wrong... I post a separate issue on that.
Thanks for your reactions!|||Ok, I lied I don't use variables in Validate, but I do in AcquireConnections, so still in a component, and that is called during design and execution times.

if (VariableDispenser.Contains(location))
{
IDTSVariables90 vars = null;
VariableDispenser.LockOneForRead(location, ref vars);
return vars[0].Value.ToString();
}

|||Try me.Designmode property may be it will work for you.

Detect whether executiontime or designtime in custom component

In my custom component in the validate() method I want to determine whether this method is called during runtime or during designtime. I Can't find a property for this on PipelineComponent or ComponentMetaData. Is it possible?

Regards,
HenkI don't think you can, and I also don't think it should matter. Why would you want this?

|||Well, during validation I want to access a package level variable that is filled with a lookup table by a preceding task. However, at designtime, there is no such thing as a task that has executed previously, so the variable does not exist then.|||Should the variable not exist, but the value may be rubbish? You cannot create variables at run-time (... I'm pretty sure you can't, no run-time structure changes allowed in SSIS). If something is really missing I think you are perhaps digging too deep into the validation, unless it is only certain settings that induce this issue.

The way to get round this would be to set DelayValidation to true, so validation only happens at runtime.

You would do this for a Exec SQL Task that requires a table for created by a preceeding task, such that the table will not exist until part through run-time. Normally it is good to validate the SQL, but in this case it will cause a problem, so we can bypass validation.|||

DarrenSQLIS wrote:

You cannot create variables at run-time

I'm afraid I didn't make myself clear. I do not create variables at runtime.

But is my observation correct that if you define a variable (at designtime of course!) at package level, that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime? (so it doesn't even contain rubbish, but it just isn't there)

(By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)|||

Henk v J. wrote:

that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime

You sure? I have a component that uses variables, actually the variable name is a property value, and I validate that the variable selected does exist. This works at design-time calls to Validate.

Henk v J. wrote:

By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)

Don't be shy. I'm intrigued now, what have you done?

|||No rocket-science, I just look whether the variable exists and if it doesn't I do nothing

By the way you are probably right about, because I can't see the variable at runtime either, so there is something else wrong... I post a separate issue on that.
Thanks for your reactions!|||Ok, I lied I don't use variables in Validate, but I do in AcquireConnections, so still in a component, and that is called during design and execution times.

if (VariableDispenser.Contains(location))
{
IDTSVariables90 vars = null;
VariableDispenser.LockOneForRead(location, ref vars);
return vars[0].Value.ToString();
}

|||Try me.Designmode property may be it will work for you.

Detect whether executiontime or designtime in custom component

In my custom component in the validate() method I want to determine whether this method is called during runtime or during designtime. I Can't find a property for this on PipelineComponent or ComponentMetaData. Is it possible?

Regards,
HenkI don't think you can, and I also don't think it should matter. Why would you want this?|||Well, during validation I want to access a package level variable that is filled with a lookup table by a preceding task. However, at designtime, there is no such thing as a task that has executed previously, so the variable does not exist then.
|||Should the variable not exist, but the value may be rubbish? You cannot create variables at run-time (... I'm pretty sure you can't, no run-time structure changes allowed in SSIS). If something is really missing I think you are perhaps digging too deep into the validation, unless it is only certain settings that induce this issue.

The way to get round this would be to set DelayValidation to true, so validation only happens at runtime.

You would do this for a Exec SQL Task that requires a table for created by a preceeding task, such that the table will not exist until part through run-time. Normally it is good to validate the SQL, but in this case it will cause a problem, so we can bypass validation.|||

DarrenSQLIS wrote:

You cannot create variables at run-time

I'm afraid I didn't make myself clear. I do not create variables at runtime.

But is my observation correct that if you define a variable (at designtime of course!) at package level, that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime? (so it doesn't even contain rubbish, but it just isn't there)

(By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)|||

Henk v J. wrote:

that variable is not seen by custom components in a dataflow task in that packge until executiontime\runtime

You sure? I have a component that uses variables, actually the variable name is a property value, and I validate that the variable selected does exist. This works at design-time calls to Validate.

Henk v J. wrote:

By the way I can easily get around the fact that I can't detect designtime or executiontime, so that's no issue anymore)

Don't be shy. I'm intrigued now, what have you done?

|||No rocket-science, I just look whether the variable exists and if it doesn't I do nothing

By the way you are probably right about, because I can't see the variable at runtime either, so there is something else wrong... I post a separate issue on that.
Thanks for your reactions!|||Ok, I lied I don't use variables in Validate, but I do in AcquireConnections, so still in a component, and that is called during design and execution times.

if (VariableDispenser.Contains(location))
{
IDTSVariables90 vars = null;
VariableDispenser.LockOneForRead(location, ref vars);
return vars[0].Value.ToString();
}

|||Try me.Designmode property may be it will work for you.