Friday, March 30, 2012
Pros and cons of having multiple DBMS in the organization
multiple RDBMS in the organization. As we acquire new companies and have to
manage them all ( Im not a manager, but would like to be some day) , what do
great database managers do? Do they just begin to choose one and how do they
go about finding the right one ? Or do they prefer to just leave them as is
..
Can you give me some pros and cons of having multiple RDBMS and what one
should do ? Any articles out there that I could read. Right now, we have SQL
Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
acquire is a stand alone entity but managers tend to be operate them all
under one umbrella.
Please advice.
ThanksWe have a similar situation , with a client at the moment . They are
acquiring companies which all have different db and web apps. Currently, we
are managing SQL7,2000,2005 - Oracle 10 and mysql . After in depth analysis,
they've decided to keep every system separate and build an Integration
service based around SQL Server. This will have a bunch of filters to import
relevant data.
The main reason for a central integration server, is that they are in the
process of acquiring more companies with all sorts of other dbms, and
therefore have to build a structure to integrate future disparate systems
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Hassan" <Hassan@.hotmail.com> wrote in message
news:#6X#Tv1jGHA.3780@.TK2MSFTNGP03.phx.gbl...
> I just want to know how does a database manager go about with having
> multiple RDBMS in the organization. As we acquire new companies and have
to
> manage them all ( Im not a manager, but would like to be some day) , what
do
> great database managers do? Do they just begin to choose one and how do
they
> go about finding the right one ? Or do they prefer to just leave them as
is
> ..
> Can you give me some pros and cons of having multiple RDBMS and what one
> should do ? Any articles out there that I could read. Right now, we have
SQL
> Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
> acquire is a stand alone entity but managers tend to be operate them all
> under one umbrella.
> Please advice.
> Thanks
>|||Hassan
If you woork for a large company, it is inevitable that you will end up with
multiple RDBMS and indeed multiple most things. I work for a large
international bank and we have multiple RDBMS on multiple platforms.
When I joined the company they were actively trying to get rid of SQL
Server. Their databases of choice were DB2 on the mainframe and Oracle on
distributed systems. The policy was to convert all SQL Server systems to
either DB2 or Oracle. They soon found this to be an unrealistic policy due t
o
the popularity of SQL Server. For every SQL Server system they managed to
convert two or three more took it's place.
More recently we accquired some companies that use Sybase, and we now have a
small Sybase team.
How you manage all these RDBMS teams is up to you. I have worked for a
company that had a central database management team and they were responsibl
e
for all RDBMS within the company. Where I work now they split them between
mainframe and distributed.
I don't know that I have really offered any help here, more just to let you
know it is a very common situation.
Regards
John
"Hassan" wrote:
> I just want to know how does a database manager go about with having
> multiple RDBMS in the organization. As we acquire new companies and have t
o
> manage them all ( Im not a manager, but would like to be some day) , what
do
> great database managers do? Do they just begin to choose one and how do th
ey
> go about finding the right one ? Or do they prefer to just leave them as i
s
> ...
> Can you give me some pros and cons of having multiple RDBMS and what one
> should do ? Any articles out there that I could read. Right now, we have S
QL
> Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
> acquire is a stand alone entity but managers tend to be operate them all
> under one umbrella.
> Please advice.
> Thanks
>
>|||We have a similar situation , with a client at the moment . They are
acquiring companies which all have different db and web apps. Currently, we
are managing SQL7,2000,2005 - Oracle 10 and mysql . After in depth analysis,
they've decided to keep every system separate and build an Integration
service based around SQL Server. This will have a bunch of filters to import
relevant data.
The main reason for a central integration server, is that they are in the
process of acquiring more companies with all sorts of other dbms, and
therefore have to build a structure to integrate future disparate systems
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Hassan" <Hassan@.hotmail.com> wrote in message
news:#6X#Tv1jGHA.3780@.TK2MSFTNGP03.phx.gbl...
> I just want to know how does a database manager go about with having
> multiple RDBMS in the organization. As we acquire new companies and have
to
> manage them all ( Im not a manager, but would like to be some day) , what
do
> great database managers do? Do they just begin to choose one and how do
they
> go about finding the right one ? Or do they prefer to just leave them as
is
> ..
> Can you give me some pros and cons of having multiple RDBMS and what one
> should do ? Any articles out there that I could read. Right now, we have
SQL
> Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
> acquire is a stand alone entity but managers tend to be operate them all
> under one umbrella.
> Please advice.
> Thanks
>|||Hassan
If you woork for a large company, it is inevitable that you will end up with
multiple RDBMS and indeed multiple most things. I work for a large
international bank and we have multiple RDBMS on multiple platforms.
When I joined the company they were actively trying to get rid of SQL
Server. Their databases of choice were DB2 on the mainframe and Oracle on
distributed systems. The policy was to convert all SQL Server systems to
either DB2 or Oracle. They soon found this to be an unrealistic policy due t
o
the popularity of SQL Server. For every SQL Server system they managed to
convert two or three more took it's place.
More recently we accquired some companies that use Sybase, and we now have a
small Sybase team.
How you manage all these RDBMS teams is up to you. I have worked for a
company that had a central database management team and they were responsibl
e
for all RDBMS within the company. Where I work now they split them between
mainframe and distributed.
I don't know that I have really offered any help here, more just to let you
know it is a very common situation.
Regards
John
"Hassan" wrote:
> I just want to know how does a database manager go about with having
> multiple RDBMS in the organization. As we acquire new companies and have t
o
> manage them all ( Im not a manager, but would like to be some day) , what
do
> great database managers do? Do they just begin to choose one and how do th
ey
> go about finding the right one ? Or do they prefer to just leave them as i
s
> ...
> Can you give me some pros and cons of having multiple RDBMS and what one
> should do ? Any articles out there that I could read. Right now, we have S
QL
> Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
> acquire is a stand alone entity but managers tend to be operate them all
> under one umbrella.
> Please advice.
> Thanks
>
>|||One common approach in large companies is to standardize on a strategic DBMS
platform in each of the mainframe, Unix/Linux, and Windows environments. It'
s
not uncommon for a company to choose DB2 on mainframe, Oracle on Unix/Linux,
and SQL Server on Windows as its startegic DBMS platforms and consider any
other DBMS platforms as legacy platforms. The company may strongly discourag
e
or even ban any new development on any other DBMS platform, and either inves
t
to migrate existing databases from other DBMS platforms to one of these thre
e
or simply sunset the them.
In decisions like this, DBMS-specific factors count for very little.
Linchi
"Hassan" wrote:
> I just want to know how does a database manager go about with having
> multiple RDBMS in the organization. As we acquire new companies and have t
o
> manage them all ( Im not a manager, but would like to be some day) , what
do
> great database managers do? Do they just begin to choose one and how do th
ey
> go about finding the right one ? Or do they prefer to just leave them as i
s
> ...
> Can you give me some pros and cons of having multiple RDBMS and what one
> should do ? Any articles out there that I could read. Right now, we have S
QL
> Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
> acquire is a stand alone entity but managers tend to be operate them all
> under one umbrella.
> Please advice.
> Thanks
>
>|||One common approach in large companies is to standardize on a strategic DBMS
platform in each of the mainframe, Unix/Linux, and Windows environments. It'
s
not uncommon for a company to choose DB2 on mainframe, Oracle on Unix/Linux,
and SQL Server on Windows as its startegic DBMS platforms and consider any
other DBMS platforms as legacy platforms. The company may strongly discourag
e
or even ban any new development on any other DBMS platform, and either inves
t
to migrate existing databases from other DBMS platforms to one of these thre
e
or simply sunset the them.
In decisions like this, DBMS-specific factors count for very little.
Linchi
"Hassan" wrote:
> I just want to know how does a database manager go about with having
> multiple RDBMS in the organization. As we acquire new companies and have t
o
> manage them all ( Im not a manager, but would like to be some day) , what
do
> great database managers do? Do they just begin to choose one and how do th
ey
> go about finding the right one ? Or do they prefer to just leave them as i
s
> ...
> Can you give me some pros and cons of having multiple RDBMS and what one
> should do ? Any articles out there that I could read. Right now, we have S
QL
> Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
> acquire is a stand alone entity but managers tend to be operate them all
> under one umbrella.
> Please advice.
> Thanks
>
>
Pros and cons of having multiple DBMS in the organization
multiple RDBMS in the organization. As we acquire new companies and have to
manage them all ( Im not a manager, but would like to be some day) , what do
great database managers do? Do they just begin to choose one and how do they
go about finding the right one ? Or do they prefer to just leave them as is
..
Can you give me some pros and cons of having multiple RDBMS and what one
should do ? Any articles out there that I could read. Right now, we have SQL
Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
acquire is a stand alone entity but managers tend to be operate them all
under one umbrella.
Please advice.
ThanksWe have a similar situation , with a client at the moment . They are
acquiring companies which all have different db and web apps. Currently, we
are managing SQL7,2000,2005 - Oracle 10 and MySQL . After in depth analysis,
they've decided to keep every system separate and build an Integration
service based around SQL Server. This will have a bunch of filters to import
relevant data.
The main reason for a central integration server, is that they are in the
process of acquiring more companies with all sorts of other dbms, and
therefore have to build a structure to integrate future disparate systems
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Hassan" <Hassan@.hotmail.com> wrote in message
news:#6X#Tv1jGHA.3780@.TK2MSFTNGP03.phx.gbl...
> I just want to know how does a database manager go about with having
> multiple RDBMS in the organization. As we acquire new companies and have
to
> manage them all ( Im not a manager, but would like to be some day) , what
do
> great database managers do? Do they just begin to choose one and how do
they
> go about finding the right one ? Or do they prefer to just leave them as
is
> ..
> Can you give me some pros and cons of having multiple RDBMS and what one
> should do ? Any articles out there that I could read. Right now, we have
SQL
> Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
> acquire is a stand alone entity but managers tend to be operate them all
> under one umbrella.
> Please advice.
> Thanks
>|||Hassan
If you woork for a large company, it is inevitable that you will end up with
multiple RDBMS and indeed multiple most things. I work for a large
international bank and we have multiple RDBMS on multiple platforms.
When I joined the company they were actively trying to get rid of SQL
Server. Their databases of choice were DB2 on the mainframe and Oracle on
distributed systems. The policy was to convert all SQL Server systems to
either DB2 or Oracle. They soon found this to be an unrealistic policy due to
the popularity of SQL Server. For every SQL Server system they managed to
convert two or three more took it's place.
More recently we accquired some companies that use Sybase, and we now have a
small Sybase team.
How you manage all these RDBMS teams is up to you. I have worked for a
company that had a central database management team and they were responsible
for all RDBMS within the company. Where I work now they split them between
mainframe and distributed.
I don't know that I have really offered any help here, more just to let you
know it is a very common situation.
Regards
John
"Hassan" wrote:
> I just want to know how does a database manager go about with having
> multiple RDBMS in the organization. As we acquire new companies and have to
> manage them all ( Im not a manager, but would like to be some day) , what do
> great database managers do? Do they just begin to choose one and how do they
> go about finding the right one ? Or do they prefer to just leave them as is
> ...
> Can you give me some pros and cons of having multiple RDBMS and what one
> should do ? Any articles out there that I could read. Right now, we have SQL
> Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
> acquire is a stand alone entity but managers tend to be operate them all
> under one umbrella.
> Please advice.
> Thanks
>
>|||One common approach in large companies is to standardize on a strategic DBMS
platform in each of the mainframe, Unix/Linux, and Windows environments. It's
not uncommon for a company to choose DB2 on mainframe, Oracle on Unix/Linux,
and SQL Server on Windows as its startegic DBMS platforms and consider any
other DBMS platforms as legacy platforms. The company may strongly discourage
or even ban any new development on any other DBMS platform, and either invest
to migrate existing databases from other DBMS platforms to one of these three
or simply sunset the them.
In decisions like this, DBMS-specific factors count for very little.
Linchi
"Hassan" wrote:
> I just want to know how does a database manager go about with having
> multiple RDBMS in the organization. As we acquire new companies and have to
> manage them all ( Im not a manager, but would like to be some day) , what do
> great database managers do? Do they just begin to choose one and how do they
> go about finding the right one ? Or do they prefer to just leave them as is
> ...
> Can you give me some pros and cons of having multiple RDBMS and what one
> should do ? Any articles out there that I could read. Right now, we have SQL
> Server,Oracle,DB2,Postgres and some mysql as well. Each business that we
> acquire is a stand alone entity but managers tend to be operate them all
> under one umbrella.
> Please advice.
> Thanks
>
>sql
Friday, March 23, 2012
Propagating results of Alter Table to its views
Enterprise Manager or with T-SQL statements in Query Analyzer, the
alterations are not automatically noticed by the views that use the table.
The only thing I have been able to figure out to do is to find each
potentially affected view and then:
1. Open it in design mode
2. Check or uncheck a field in the Altered table
3. Uncheck or check the same field to restore the view to its original field
selection
4. Close the view and Save it.
Two problems:
1. That's tedious and time-consuming
2. It is easy to miss an affected view.
Can someone advise me as to a better way? (I am mostly using SQL Server 2000
)
Thanks,
Doug MacLeanDoug MacLean wrote:
> When I alter a table to add columns or change their properties, either in
> Enterprise Manager or with T-SQL statements in Query Analyzer, the
> alterations are not automatically noticed by the views that use the table.
> The only thing I have been able to figure out to do is to find each
> potentially affected view and then:
> 1. Open it in design mode
> 2. Check or uncheck a field in the Altered table
> 3. Uncheck or check the same field to restore the view to its original fie
ld
> selection
> 4. Close the view and Save it.
> Two problems:
> 1. That's tedious and time-consuming
> 2. It is easy to miss an affected view.
> Can someone advise me as to a better way? (I am mostly using SQL Server 20
00)
> --
> Thanks,
> Doug MacLean
Firstly, do not use SELECT * in views. It's generally a bad idea to use
SELECT * anywhere in production code. It's a particularly bad idea in
views because of the way views handle changes to the columns.
So assuming you have named columns in all your views, use
sp_refreshview to ensure the view is up to date with changes to its
columns. If you want to add a column you'll have to edit the view
definition of course, which is good practice and not difficult if you
have adequate source control and change control procedures. Table
changes should never be executed by Enterprise Manager in a production
environment.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Thanks. That's very helpful. It eliminates the need for my 4-step process in
Enterprise Manager.
I wonder if there is a tool to list all of the views that depend on a
specified table, because the other challenge is actually finding all of them
when altering a table.
Best Regards,
--
Doug MacLean
"David Portas" wrote:
> Doug MacLean wrote:
> Firstly, do not use SELECT * in views. It's generally a bad idea to use
> SELECT * anywhere in production code. It's a particularly bad idea in
> views because of the way views handle changes to the columns.
> So assuming you have named columns in all your views, use
> sp_refreshview to ensure the view is up to date with changes to its
> columns. If you want to add a column you'll have to edit the view
> definition of course, which is good practice and not difficult if you
> have adequate source control and change control procedures. Table
> changes should never be executed by Enterprise Manager in a production
> environment.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Greetings,
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
> David,
> Thanks. That's very helpful. It eliminates the need for my 4-step process
> in
> Enterprise Manager.
> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
> Best Regards,
> --
> Doug MacLean
Create the views WITH SCHEMABINDING and you won't be able to change the
table without first dropping the view. This is a good reminder to fix the
view ;-)
Regards,
Neale NOON|||You may want to use this.
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where table_name = '<table_name>'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
You might find it easier to refresh all views since you cannot rely on
dependency information unless the views were created WITH SCHEMABINDING.
The script below will generate a script to refresh all views in the current
database. You can wrap it in a cursor and execute according to your
preference. BTW, even without 'SELECT *', you can run into issues with
changed datatypes in the referenced tables.
SELECT
'EXEC sp_refreshview ''' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
AND OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
> David,
> Thanks. That's very helpful. It eliminates the need for my 4-step process
> in
> Enterprise Manager.
> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
> Best Regards,
> --
> Doug MacLean
>
> "David Portas" wrote:
>|||Thanks, Neale.
That's an interesting idea to know when I change a table that has one or
more dependent views. So it is a good reminder.
But I KNOW there are views. And what I want is a clean way of finding and
refreshing them. Adding a process of dropping (and then re-adding) them seem
s
tedious.
Thanks Best Regards,
--
Doug MacLean
"Neale NOON" wrote:
> Greetings,
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
> news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
>
> Create the views WITH SCHEMABINDING and you won't be able to change the
> table without first dropping the view. This is a good reminder to fix the
> view ;-)
> --
> Regards,
> Neale NOON
>
>|||Dan,
Ahhh!. That's a great idea. Thanks for both the idea and the sample code.
And it's practical because I only do such updates on the production system a
t
times when no one is using it.
You're right. I've already seen the problem that -- even without adding
columns -- the view needs to be refreshed.
Thanks much and Best regards,
--
Doug MacLean
"Dan Guzman" wrote:
> You might find it easier to refresh all views since you cannot rely on
> dependency information unless the views were created WITH SCHEMABINDING.
> The script below will generate a script to refresh all views in the curren
t
> database. You can wrap it in a cursor and execute according to your
> preference. BTW, even without 'SELECT *', you can run into issues with
> changed datatypes in the referenced tables.
> SELECT
> 'EXEC sp_refreshview ''' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> ''''
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'VIEW'
> AND OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> ), 'IsMSShipped') = 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
> news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
>
>
Monday, March 12, 2012
Programmatically Set Flat File Destination Name (relating to timestamp)
I need dumping data from table to a file daily with file name relating to time stamp. The flat file connection manager editor only allow you to set the STATIC file name. Suppose I programmtically passing the time now to a User::TimeNowVariable, how do I set the file name programmatically relating to this variable?
This is a very classic issue associated to Graphical Programming, it seems to be very difficult to understand how to do a very simple programmatic property setting like this, uncless you KNEW it.
I appreciate any helpful info provided.
JordanUse expressions on the Flat File Connection Manager to set the Connection String using the variable.
Programmatically set a Flat File Connection Manager
Hello,
I need to know how I can programmatically set a Flat File Connection Manager's Column Delimiter value.
The Data Warehouse project I am working on, receives daily information feeds that could contain one of two delimiters. Which is just dumb...anyways, as it is now we have two seperate Data Flow Tasks which handle these two delimiters. Currently we have a script taks that "sneak previews" each incoming flat file to determine which delimiter it has, and direct our flow to the correct Data Flow Task to handle it.
I do not want to have to maintain 2 DFTs. How can I get around this problem?
Even if there is a way to do this by passing variables/setting expressions in the Flat File Connection manager, I would do that. Does not necessarily HAVE to be a pure programmatic approach.
ANY help would be greatly appreciated!
Feel free to email me at ccorbin@.topcoder.com with any questions, or leave me some good news here :)
THANKS!
Chris Corbin
Software Developer
TopCoder Inc.
I thought this was a simple question. Apparently, you can use an expression to set every property of the File Connection Manager except the column delimiter. Maybe that will change in the near future. There must be a reason.|||ColumnDelimiter is a property of the FlatFileColumn, and not the connection manager. Due to which currently it is not expressionable. Feel free to raise a DCR(Design Change Request) for this issue.|||How would I go about raising a DCR?
Thanks
|||To file a bug/DCR, start here:
http://connect.microsoft.com/SQLServer
Wednesday, March 7, 2012
Programmatic way to replace partition DSV Table bindings with Query Bindings
I'm trying to programmatically replace all my partitions' DSV table bindings with query bindings.
In the partitions manager, when creating a new partition that's restricted via a query binding, the wizard manages to pull a default query from the DSV definition. Is there a way to do this programmatically? The closest thing I could find in the object model is the "Schema" property, but this isn't what I need.
I am pretty sure anything you can do in XMLA is possible to do through AMO.
The easiest way for you to figure out what classes and properties or methods to use is to use AMOBrowser sample application. It shows you the real hierachy of AMO objects.
For instance, using that application I was able to figure out that partition has a source object and then simple search for "partition.source" brought me to this aricle http://msdn2.microsoft.com/en-gb/library/ms345091.aspx
Where I discovered following code sample
static void CreateInternetSalesMeasureGroupPartitions(MeasureGroup mg)
{
Partition part;
part = mg.Partitions.FindByName("Internet_Sales_184");
if ( part != null)
part.Drop();
part = mg.Partitions.Add("Internet_Sales_184");
part.StorageMode = StorageMode.Molap;
part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey <= '184'");
part.Slice = "[Date].[Calendar Year].&[2001]";
part.Annotations.Add("LastOrderDateKey", "184");
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks for the tip, Edward. I still haven't found the class that exposes the DSV's query binding (which I would use when generating a new partition's query binding), so I suppose I might have to do this manually.
The Annotations property looks pretty useful though, thanks!
Monday, February 20, 2012
Programatically create and configure a FlatFileConnectionManager
I am writing some C# code that builds a package programatically. the package contains a flat file connection manager. I have referenced Microsoft.SqlServer.Dts.Runtime.Wrapper in order that I can use the ConnectionManagerFlatFileClass class.
Here is some of my code:
Package p = New package();
ConnectionManager sourceCM = p.Connections.Add("FLATFILE");
sourceCM.Name = "some-name";
ConnectionManagerFlatFileClass cmffc = sourceCM.InnerObject()
I get a compilation error on the last line saying I cannot implicitly cast as ConnectionManagerFlatFileClass. Fair enough, so I change the last line of code to:
ConnectionManagerFlatFileClass cmffc = (ConnectionManagerFlatFileClass)sourceCM.InnerObject()
Now it will compile. But when I run it I get the error:
"Unable to cast COM object of type 'System.__ComObject' to class type "Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerFlatFileClass'. COM components that enter the CLR and do not support IProvideClassInfo or that do not have interop assembly referenced will be wrapped in the __ComObject type. instances of this type cannot be cast to any other class; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface"
Can anyone tell me how I can cast the ConnectionManager.InnerObject as a ConnectionManagerFlatFileClass please?
-Jamie
P.S. My ultimate aim here is to set the metdata of each of the columns defined by the flat file connection manager. I can't see how I can do that without referring to the connection manager as a ConnectionManagerFlatFileClass!
OK, it looks like I can reference the Columns collection of my flat file connection manager using
sourceCM.Properties["Columns"]
but that returns me an object of type DTSProperty which I've tried casting to IDTSOutputColumnCollection90 but it says it cannot convert a Microsoft.SqlServer.Dts.Runtime.DtsProperty to Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputColumnCollection90 which, again, makes complete sense!
So, if anyone can tell me how I can programatically access the columns in my flat file connection manager then I'd be very grateful.
-Jamie
|||
DtsProperty.GetValue Method ?
DtsProperty is the definition of the property, and you want the actual value, which is where the GetValue method comes in.
I think the whole idea of this is to abstract you from the real class, such that they can be changed and updated at will without breaking existing code. Not too sure on the deal on binary compatability as we used to think of it, it seems to be less of an issue, but there is probably some good reason!
|||Thanks mate, but that won't do it. However, I've managed to find the answer elsewhere (and for once in my life I didn't rely on Darren to give me the answer ). Namely, in this sample: http://msdn2.microsoft.com/en-us/library/ms161541.aspx
Here's the code that does it, turns out I DO need the Microsoft.SqlServer.Dts.Runtime.Wrapper namespace:
using wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;
...
private wrap.IDTSConnectionManagerFlatFile90 ffc = (wrap.IDTSConnectionManagerFlatFile90)sourceCM.InnerObject;
wrap.IDTSConnectionManagerFlatFileColumn90 col = ffc.Columns.Add();
OK, cool. I've got it working. It kinda begs the question tho, why is there no class in the Microsoft.SqlServer.Dts.Runtime namespace that enables us to do it? Its not like configuring a FlatFile connection manager isn't core functionality. I've asked the same question elsewhere (sort of). Hopefully it'll get answered.
-Jamie