i got a SSIS package that changes it source connection on runtime from
one oracle database to another oracle database, extracting the same
query from both databases.
this is done by setting a user variable to ConnectionString property
on OLEDB connection's expressions.
DelayValidation is set to True on connection and the package.
This works fine as long as source connections are oracle and use
MSDAORA.1 provider.

Now issue is i got a SQL connection to set to the same package to use
for extraction. it runs the same select query through sql connection
too. sql connection use provider = SQLNCLI.1

it fails with following error.
[DTS.Pipeline] Error: "component "OLE DB Source" (2394)" failed
validation and returned validation status "VS_NEEDSNEWMETADATA".

i appears to be that oracle driver metadata and sql metadata is
different. so i tried to do casting on select statement so all
metadata will appear to driver the same with no luck though.
SELECT cast(firstName as varchar(20))
, cast ( Id as int)

does anyone have an idea to resolve this. i really need to use the
same package than creating 2 packages because this process is done in
many extraction packages from oracle already. any code base solutions
is even fine like changing
dts.connections("xyz").Properties("Provider") = "" what ever.

is there a program method to refresh pipeline metadata?

appreciate any help on this!!!

thanks






[OLE DB Source [2394]] Warning: The external metadata column
collection is out of synchronization with the data source columns. The
column "SourceIdentifier" needs to be added to the external metadata
column collection. The column "SrcReferenceGroupCode" needs to be
added to the external metadata column collection. The column
"ReferenceIdentifier" needs to be added to the external metadata
column collection. The column "ReferenceDescription" needs to be added
to the external to the external metadata column collection. The column
"AdditionalReportCode1" needs to be added to the external metadata
column collection. The column "AdditionalReportCode2" needs to be
added to the external metadata column collection. The column
"AdditionalReportCode3" needs to be added to the external metadata
column collection. The column "AdditionalReportCode4" needs to be
added to the external metadata column collection. The column
"AdditionalReportCode5" needs to be added to the external metadata
column collection. The column "SourceSystem" needs to be added to the
external metadata column collection. The column "SourceHospital" needs
to be added to the external metadata column collection. The column
"SourceCreateDate" needs to be added to the external metadata column
collection. The column "SourceUpdateDate" needs to be added to the
external metadata column collection. The column "ArchiveFlag" needs to
be added to the external metadata column collection. The "external
metadata column "ARCHIVEFLAG" (2472)" needs to be removed from the
external metadata column collection. The "external metadata column
"SOURCEUPDATEDATE" (2469)" needs to be removed from the external
metadata column collection. The "external metadata column
"SOURCECREATEDATE" (2466)" needs to be removed from the external
metadata column collection. The "external metadata column
"SOURCEHOSPITAL" (2463)" needs to be removed from the external
metadata column collection. The "external metadata column
"SOURCESYSTEM" (2460)" needs to be removed from the external metadata
column collection. The "external metadata column
"ADDITIONALREPORTCODE5" (2457)" needs to be removed from the external
metadata column collection. The "external metadata column
"ADDITIONALREPORTCODE4" (2454)" needs to be removed from the external
metadata column collection. The "external metadata column
"ADDITIONALREPORTCODE3" (2451)" needs to be removed from the external
metadata column collection. The "external metadata column
"ADDITIONALREPORTCODE2" (2448)" needs to be removed from the external
metadata column collection. The "external metadata column
"ADDITIONALREPORTCODE1" (2445)" needs to be removed from the external
metadata column collection. The "external metadata column
"ELECTIVESURGERYREPORTCODE" (2442)" needs to be removed from the
external metadata column collection. The "external metadata column
"EMERGENCYDATAREPORTCODE" (2439)" needs to be removed from the
external metadata column collection. The "external metadata column
"ADMITTEDEPISODEREPORTCODE" (2436)" needs to be removed from the
external metadata column collection. The "external metadata column
"REFERENCEDESCRIPTION" (2433)" needs to be removed from the external
metadata column collection. The "external metadata column
"REFERENCEIDENTIFIER" (2430)" needs to be removed from the external
metadata column collection. The "external metadata column
"SRCREFERENCEGROUPCODE" (2427)" needs to be removed from the external
metadata column collection. The "external metadata column
"SOURCEIDENTIFIER" (2424)" needs to be removed from the external
metadata column collection.

Eric:
Never having worked with Oracle, I cannot say for sure, but I offer this bit
of 'free' advice:

Does the SQL Syntax for MS SQL differ from Oracle? Will the same 'CAST' or
'CONVERT' statement work in both providers? You may need to fiddle around
with changing the actual SQL statement to get the same metadata to be seen by
the Source adapter.

HTH

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Eric" wrote:

Eric:
Never having worked with Oracle, I cannot say for sure, but I offer this bit
of 'free' advice:

Does the SQL Syntax for MS SQL differ from Oracle? Will the same 'CAST' or
'CONVERT' statement work in both providers? You may need to fiddle around
with changing the actual SQL statement to get the same metadata to be seen by
the Source adapter.

HTH

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Eric" wrote:

Eric:
Never having worked with Oracle, I cannot say for sure, but I offer this bit
of 'free' advice:

Does the SQL Syntax for MS SQL differ from Oracle? Will the same 'CAST' or
'CONVERT' statement work in both providers? You may need to fiddle around
with changing the actual SQL statement to get the same metadata to be seen by
the Source adapter.

HTH

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Eric" wrote:

Eric:
Never having worked with Oracle, I cannot say for sure, but I offer this bit
of 'free' advice:

Does the SQL Syntax for MS SQL differ from Oracle? Will the same 'CAST' or
'CONVERT' statement work in both providers? You may need to fiddle around
with changing the actual SQL statement to get the same metadata to be seen by
the Source adapter.

HTH

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Eric" wrote:

Eric:
Never having worked with Oracle, I cannot say for sure, but I offer this bit
of 'free' advice:

Does the SQL Syntax for MS SQL differ from Oracle? Will the same 'CAST' or
'CONVERT' statement work in both providers? You may need to fiddle around
with changing the actual SQL statement to get the same metadata to be seen by
the Source adapter.

HTH

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Eric" wrote:

Eric:
Never having worked with Oracle, I cannot say for sure, but I offer this bit
of 'free' advice:

Does the SQL Syntax for MS SQL differ from Oracle? Will the same 'CAST' or
'CONVERT' statement work in both providers? You may need to fiddle around
with changing the actual SQL statement to get the same metadata to be seen by
the Source adapter.

HTH

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Eric" wrote:

Eric:
Never having worked with Oracle, I cannot say for sure, but I offer this bit
of 'free' advice:

Does the SQL Syntax for MS SQL differ from Oracle? Will the same 'CAST' or
'CONVERT' statement work in both providers? You may need to fiddle around
with changing the actual SQL statement to get the same metadata to be seen by
the Source adapter.

HTH

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Eric" wrote:

Eric:
Never having worked with Oracle, I cannot say for sure, but I offer this bit
of 'free' advice:

Does the SQL Syntax for MS SQL differ from Oracle? Will the same 'CAST' or
'CONVERT' statement work in both providers? You may need to fiddle around
with changing the actual SQL statement to get the same metadata to be seen by
the Source adapter.

HTH

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Eric" wrote:

Did you resolve this? I have the same problem. The change in connection works
fine for some data flows but for others I have this VS_NeedsNewMetaData
problem. I actually use an expression to construct slightly different queries
with palatable syntax depending on wehter the connection uses Oracle
(Provider=OraOLEDB.Oracle) or Sqlserver provider (Provider=SQLOLEDB).