can I use * to specify 'Output Column' for OLD DB Source Editor?

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

ZZhang wrote:

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

You could use '*' if you wanted but this is about as bad as bad practice gets. Don't do it. If the name of a column changes then SSIS will break because it stored the metadata of the external data source. This is by design.

-Jamie

|||

Hi, Jamie,
Thanks so much for your quick response! I have two questions then.

1. How to use * ? I can not find it in the 'OLD DB Source Editor'.

2. Let me simplifing my case. I have a remote source table ( which has may columns, incluing 'ID' and 'Date'). The schema may change, but not 'ID' and 'Date' columns. The DTS job is to get all rows ( select * from myTable where [Date] = getdate() ), and output to a delimited flat file.

What is the best practice SSIS for this case?

Thanks again!

-ZZ

|||

Like I said. You can't do it. If the external metadata changes then your data-flow will error.

-Jamie

|||

thanks, Jamie!

Honestly, this surprised me, if it can not use *. I will choose NOT to use SSIS for my simple job, because it does not make sense to modify ( and test) SSIS package every time the schema changes. I hope there is a workaround to meet my job requirement in SSIS.

-ZZ

|||

What? Your problem is the fact that your schema is changing, not that SSIS can't handle it. Are you saying that its impossible to know what your schema will look like from one day to the next? I've never seen a company that would run its systems like that nor would i want to.

Sorry to sound rude but it just sounds crazy to me!

-Jamie

|||

Thanks Jamie for your time to answer my question!

I am new to SSIS, and have not used variable, expression, and sricpt much. I am open-mind, and believe there is a way (simple or difficult), to solve my issue. Maybe you are right, but here I am searching 'how-to' solution, like ( select * from MyTable). Should I use *? it is another question.

Thanks again!

-ZZhang

|||

Again,

Yes you can use "SELECT * FROM MyTable"|||

It seems that MS has solution for 'Dynamic Metadata', although SSIS pipeline requires static metadata.

"Advacned ETL: Embedding Integration Services" from PDC05 mentioned this issue. SMO is needed.

I am still searching for the samples.

-ZZhang