Microsoft SQL Server 2005 DTS Designer Components

Hi,

I learned this from one of SSIS book-Migrating from Data Transformation Services to Integration Services in SQL.

For the design time components, Microsoft has created a special distribution called the Microsoft SQL Server 2005 DTS Designer Components. The Designer Components distribution replaces the components that get uninstalled when you upgrade your last instance of SQL Server 2000. If you want to upgrade all your SQL Server 2000 instances, this is the best and only option for recovering the DTS designer.

Is anybody heard of this 2005 DTS Designer component?

Where to download? How it works? Any experience to will be appreciated.

Thanks,

Charley

Some info here-

DTS2000
(http://wiki.sqlis.com/default.aspx/SQLISWiki/DTS2000.html)

|||

Thanks so much.

Charley

Microsoft OLEDB Provider Oracle

Hi,

Previously i was using oracle8i with ssis.ssis was working fine.later i have upgraded 8i to oracle 9i.now when ever i try to establish a new connection using Microsoft OLEDB Provider for Oracle.i am getting the following error "Oracle error occurred, but error message could not be retrieved from Oracle" .but i am able to access the oracle 9i database thru pl/sql and toad. what could be the problem?

Thanks

Jegan.T

We're running fine 9.2 on a development server (2005) along with SSIS packages.

What about tnsnames.ora?

|||

Hi Enric,

my tnsnames.ora is correct .bcoz i am able to login via toad/sqlplus.while upgradeing i have unistalled 8i and installed 9i . do we need to install any drivers?

Thanks

Jegan.T

Microsoft OLE DB Provider for Visual FoxPro 9.0

Im quite interested to see if any one is using Microsoft OLE DB Provider for Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free table directory like you can with the ODBC driver. ?

thanks kindly

My basic proccess is as follows: The source foxpro database has hundreds of dbf files the SSIS procress is as follows

1: Copy only required dbf files and/or matching .fpt files over to local drive on SSIS box(we only need 10 files)

use MS Visual Foxpro ODBC driver (free table option) and set up a system DSN--dataflow tabdatareader source--and away you go

Hi! Yes, you can use the free table option with the VFP OLE DB data provider. A typical connection string for the OLE DB data provider is "Provider=VFPOLEDB.1;Data Source=C:\Temp\" -- Cindy Winegarden cindy@.cindywinegarden.com VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx ""Mr Pro Tools"@.discussions.microsoft.com" <"=?UTF-8?B?TXIgUHJvIFRvb2xz?="@.discussions.microsoft.com> wrote in message news:64e94200-765c-4209-afa1-a2d8bb82af1f@.discussions.microsoft.com... Im quite interested to see if any one is using Microsoft OLE DB Provider for Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free table directory like you can with the ODBC driver. ? ...

Microsoft OLE DB Provider for Visual FoxPro 9.0

Im quite interested to see if any one is using Microsoft OLE DB Provider for Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free table directory like you can with the ODBC driver. ?

thanks kindly

My basic proccess is as follows: The source foxpro database has hundreds of dbf files the SSIS procress is as follows

1: Copy only required dbf files and/or matching .fpt files over to local drive on SSIS box(we only need 10 files)

use MS Visual Foxpro ODBC driver (free table option) and set up a system DSN--dataflow tabdatareader source--and away you go

Hi!

Yes, you can use the free table option with the VFP OLE DB data provider. A

typical connection string for the OLE DB data provider is

"Provider=VFPOLEDB.1;Data Source=C:\Temp\"

--

Cindy Winegarden

cindy@.cindywinegarden.com

VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx

VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx

""Mr Pro Tools"@.discussions.microsoft.com"

<"=?UTF-8?B?TXIgUHJvIFRvb2xz?="@.discussions.microsoft.com> wrote in message

news:64e94200-765c-4209-afa1-a2d8bb82af1f@.discussions.microsoft.com...

Im quite interested to see if any one is using Microsoft OLE DB Provider for

Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free

table directory like you can with the ODBC driver. ? ...

Microsoft OLE DB Provider for DB2 in SSIS vs. ODBC in DTS

I finally made data transfer between AS400 and SQL2005 work by using Microsoft OLE DB provider for DB2. I have tried other methods, some do not have build-in destination, some have unicode conversion problem. The only issue I have with this method is the peformance. For example, 300,000 rows load from AS400 take only 3 minutes with the ODBC connection in DTS, but more than 5 minutes with the microsoft OLD DB provider for DB2 in SSIS.

Does anyone have the same issue or figured out any tweaking that may speed up the transfer?

Thanks.

In the 5 minute example, what was your destination?|||The destination is the same - an OLE DB connection that connect to a SQL 2005 server.|||

Jane2006 wrote:

The destination is the same - an OLE DB connection that connect to a SQL 2005 server.

And are you using the "fast load" option?|||

Since I also need to load data back from SQL2005 to AS400, I could not use ADO.NET/ODBC method unless as someone else suggested, build a component for that missing destination microsoft does not have time to build. Also, the unicode issue drives me crazy. So I decide to use Microsoft OLE DB Provider for DB2.

Based on my experience, only the version released in April 2007 is good on 64-bit machine. The old versions did not work on our 64-bit machine even thought it said it should.

|||

Are you talking about the Data access mode in OLE DB Destination Editor? Yes. I am using the fast load. Actually this is a default unless changed.

|||

Jane2006 wrote:

Since I also need to load data back from SQL2005 to AS400, I could not use ADO.NET/ODBC method unless as someone else suggested, build a component for that missing destination microsoft does not have time to build. Also, the unicode issue drives me crazy. So I decide to use Microsoft OLE DB Provider for DB2.

Based on my experience, only the version released in April 2007 is good on 64-bit machine. The old versions did not work on our 64-bit machine even thought it said it should.

This is irrelevant. On the OLE DB destination, are you using the "Table or view - fast load" option when loading your results from AS400 to SQL Server?|||Yes. I think there must be some settings in Microsoft Provider OLE DB Provider for DB2 that needs to be tuned up. I just don't know where.|||How many rows are you retrieving and what is your MaxInsertCommitSize set to?|||Retrieving 3048740 rows from AS400. Maximum insert commit size is 0.|||Try setting MaxInsertCommitSize to 20000 and see what happens.|||Just give it a try. Not much difference. Still more than 5 minutes.|||Any transformations? Or just source -> destination?

Are you specifying a query in the source connection, or selecting a table from the drop down list?|||The one fundamental difference here is that you are using a Microsoft DB2 driver. It isn't a native AS400 driver. It's a 3rd party driver as far as connecting to the mainframe is concerned. You can download the IBM DB2 driver and you might have luck with that.

This isn't the same as using ODBC, which would (perhaps) use a native driver to connect.|||

No transformation. A straight source to destination. I am using SQL commnd for this particular one.

I have used tables in other scenarions as well. The row sizes are much smaller than this one, but ODBC consistently has better performance, even though only several seconds better.

metadata update

hi, I try to post a new question about metadata refresh...even if i see other thread that work on a similar problem.

I have a ssis package that import an xml huge file (500 mb); These are the main step:

1) generate a XSD file against xml using xsd.exe utility

2) using xml task, make a diff between the old xsd and the new

3) if there are no difference, I start the data flow task that import xml in sql server; otherwise I stop all the task, edit the data flow task, change the xsd reference in advanced editor and then make many "double clik / OK" on every single flow....

The underling idea is that xml file change because some columns are added but these columns are not interesting for my elaboration, so i can ignore this new column and work without mapping it.

What I'm looking for is a way for make, via SSIS, the "double clik / OK" steps....in other words, to update the metadata.

Could anybody suggest me a way? it's a sort of macro, or keyboard recorder...I'm trying to study xml package configuration; is this a good way ?

another way is to give to the end user the task to update metadata; for making this I need to open the package editor (visual studio..:!) in a more confortable environment....For example, is possible to edit the ssis package in ms access? probably i know the answer...

please, help me!

thank tou in advance

alessandro

Can I check my understanding?

You have an XML source file, correct? And its format (i.e. the xsd that describes it) sometimes changes, correct?

You want a way of changing the SSIS package at execution-time in order to handle this eventuality, correct?

If I've got all this correct then I'm afraid you cannot do this. The metadata of a data-flow cannot be changed after design-time.

-Jamie

|||

Thank you Jamie; you perfectly catch my question, and your answer is similat to other that I have seen on this forum. What I don't really understand is why there is this "limitation" (if i can say).

SSIS are many light year better of DTS (and not only...), but the thing I'm trying to do seem to be so easy....it's only a break in the execution and a sort of macro! At this point I don't exclude to trying a mouse recorder solution....

thank you very much for you answer

Alessandro

Metadata Refresh

-We are using SSIS packages for various kind of data load from excel source.

-If there are any change in the data type or format of excel, the package cries for the Metadata mismatch.

-During design time if you accept the metadata changes, all things work fine.

But in our case we have deployed the packages on Production Server, now the excel file format/data has changed. The packages are expecting a different metadata so they are not working at all.

Do you have any suggestions for the above problem?

Thanks, Vijay.

You'll have to edit the package. Metadata cannot change once it has been designed into the package.

Metadata Model in SSIS 2005

Hi *,

Can anyone tell me what Metadata Model Microsoft supports with SSIS 2005? DTS in 2000 was supporting OIM but I was not able to get some information about this topic on SSIS 2005.

The reason I need to know is that I wanna import some metadata from Business Objects Data Integrator into SSIS 2005 and Business Objects is supporting CWM....

I would appreciate your help!

Thank you,
Steve

Can you explain what OIM & CWM stand for and what they mean?

I wasn't aware of DTS supporting any metadata models.

-Jamie

|||CWM stands for Common Warehouse Metadatamodel; OIM for Open Information Model

OIM was supported by SQL Server 2000, but this standard doesn't exist anymore. CWM is supported by a wide range of ETL-Tools (including BusinessObjects Data Integrator).

For example, it's possible to export Data Integrators Datastores and insert them into another system like ERWin. Because both support a common metadata model. I just want to know if I can share metadata with SQL Server Integration Services?

At least SSIS creates a file named CWM_1.0.dtd in my project folders which indicates some support...|||

Interesting stuff!

SSIS doesn't have a great metadata story to be honest, certainly not anything that adheres to industry standards (I hereby stand to be corrected). So you probably can't do the things that you are talking about. As far as I am aware the products you talk about there make heavy use of metadata (I'm assuming through some sort of metadata repository?) but SSIS's package architecture is different - it doesn't lend itself to metadata in quite the same way (in my opinion).

I can't find a CWM* file in any of my folders. Where exactly is yours?

How did SQL Server 2000 support OIM?

Sorry, I haven't been much help there have I?

-Jamie

|||Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...
SSIS doesn't support metadata exchange, has poor data profiling, and no possibilities to do perform impact/lineage analysis (w/out metadata samples toolkit)... does anyone have some success stories where Microsoft's Integration Services handled a really big data integration project (except for Project REAL)?|||

ssauerw wrote:

Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

No. Not for DTS.

ssauerw wrote:

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...

I'd be interested in reading that when its complete!!!

-Jamie

|||Well, you can read it but it's in German ;)

Why do you use SSIS instead of a "professional" Data Integration Tool like PowerCenter or BODI? Just the price?

Steve|||

Why don't you qualify SSIS as a professional data integration tool?

Functionally SSIS has all of the features that tools such as those have - the main difference is, I suppose,,the architecture. I would qualify tools like Informatica Powercentre (which I have used extensively) as a pureplay ETL tool - I believe SSIS is much more than that - it is a platform for data integration. The range of features that you get out-of-the-box dwarfs Informatica - it really does.

You are correct though - the main differentiator is the price. And don't forget that SSIS comes with a free relational database server in the box.

-Jamie

P.S. You may wish to read this comparison of SSIS and Informatica: http://www.microsoft.com/sql/technologies/integration/conchango.mspx

|||Hi Jamie,

interesting paper... I'll give it a closer look this afternoon.

Thanks!|||

OK great. Again though, why don't you qualify SSIS as a professional ETL tool? Is it simply because it has a Microsoft badge on it?

-Jamie

|||Hi Jamie,

interesting paper. Thanks again.

Well, I think SSIS can perform ETL Jobs pretty well. But it's not a professional data integration platform, since there is no impact analysis, data lineage, metadata exchange, no out-of-the-box support for enterprise systems, etc. I would prefer Data Integrator even though its inital costs are much higher...

Don't get me wrong, I like Microsoft software (not every piece though ;) but for an enterprise scale data integration project I would use Data Integrator...

Steve|||

Steve,

Some thoughts here for you: http://blogs.conchango.com/jamiethomson/archive/2006/08/15/4353.aspx

I'd be interested in reading your paper when it is complete. Do you think that would be possible?

-Jamie

|||Hi Jamie,

thanks for putting this page in your blog online! I'm very anxious about the comments ppl are hopefully making.

Well, as I mentioned earlier, you can read my thesis but it's written in German not English. So if that's not a problem I'll send it to you!

Steve|||

It's an interesting topic.

Strictly speaking mature metadata reporting makes enterprise data integration easier to maintain and manage over time but it is not a pre-requisite of building data warehouses and BI databases. There are a lot of DataStage sites out there that are not using MetaStage and are not aware of the numerous alternative metadata methods just like there are a lot using PowerCenter but not using SuperGlue. Several years ago we had a lot of competition between a bunch of ETL tools none of which had profiling or metadata addons.

Informatica only bought a profiling and data quality tool at the start of this year, BO only bought FirstLogic in Feb, IBM-Ascential meanwhile have spent several years trying to merge the suite onto one metadata platform, so really is Microsoft that far behind the competition? There is a risk that metadata and SOA governance becomes a big business trend in the next couple years but I don't see it captivating the market right now.

You've got to consider up front costs against ongoing costs and in some evaluations SSIS will come out ahead and in others it will be a full suite data integration vendor. A large number of sources and complex range of data problems certainly favours the full suite with profiling and metadata, but projects with a narrower scope may make SSIS more attractive.

|||

I have used Data Integrator and I prefer SSIS. My biggest complaint with DI was you cannot use a SQL Statements as a data source; you must use tables/views only (as far as I could tell). When I needed to filter a 10 million row table to only a few million records, it allowed me to set a filter on the source, but it had to read every record in first to apply the filter anyway.

Microsoft has always created a consistent look and feel to their products, making the transition from one development tool to another seamless. Within the past year, I've learned SSIS, SSAS, and SSRS just by trial-and-error, and am able to create end-to-end reporting and anlaysis solutions for our customers within a few weeks.

And I believe it will just be a matter of time before new "Data Profile" SSIS packages or custom tasks are created by the community, as was the Metadata Toolkit, and custom source adapters for enterprise systems will surface for SSIS, all probably downloadable for free.

-Kory

Metadata Model in SSIS 2005

Hi *,

Can anyone tell me what Metadata Model Microsoft supports with SSIS 2005? DTS in 2000 was supporting OIM but I was not able to get some information about this topic on SSIS 2005.

The reason I need to know is that I wanna import some metadata from Business Objects Data Integrator into SSIS 2005 and Business Objects is supporting CWM....

I would appreciate your help!

Thank you,
Steve

Can you explain what OIM & CWM stand for and what they mean?

I wasn't aware of DTS supporting any metadata models.

-Jamie

|||CWM stands for Common Warehouse Metadatamodel; OIM for Open Information Model

OIM was supported by SQL Server 2000, but this standard doesn't exist anymore. CWM is supported by a wide range of ETL-Tools (including BusinessObjects Data Integrator).

For example, it's possible to export Data Integrators Datastores and insert them into another system like ERWin. Because both support a common metadata model. I just want to know if I can share metadata with SQL Server Integration Services?

At least SSIS creates a file named CWM_1.0.dtd in my project folders which indicates some support...|||

Interesting stuff!

SSIS doesn't have a great metadata story to be honest, certainly not anything that adheres to industry standards (I hereby stand to be corrected). So you probably can't do the things that you are talking about. As far as I am aware the products you talk about there make heavy use of metadata (I'm assuming through some sort of metadata repository?) but SSIS's package architecture is different - it doesn't lend itself to metadata in quite the same way (in my opinion).

I can't find a CWM* file in any of my folders. Where exactly is yours?

How did SQL Server 2000 support OIM?

Sorry, I haven't been much help there have I?

-Jamie

|||Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...
SSIS doesn't support metadata exchange, has poor data profiling, and no possibilities to do perform impact/lineage analysis (w/out metadata samples toolkit)... does anyone have some success stories where Microsoft's Integration Services handled a really big data integration project (except for Project REAL)?|||

ssauerw wrote:

Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

No. Not for DTS.

ssauerw wrote:

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...

I'd be interested in reading that when its complete!!!

-Jamie

|||Well, you can read it but it's in German ;)

Why do you use SSIS instead of a "professional" Data Integration Tool like PowerCenter or BODI? Just the price?

Steve|||

Why don't you qualify SSIS as a professional data integration tool?

Functionally SSIS has all of the features that tools such as those have - the main difference is, I suppose,,the architecture. I would qualify tools like Informatica Powercentre (which I have used extensively) as a pureplay ETL tool - I believe SSIS is much more than that - it is a platform for data integration. The range of features that you get out-of-the-box dwarfs Informatica - it really does.

You are correct though - the main differentiator is the price. And don't forget that SSIS comes with a free relational database server in the box.

-Jamie

P.S. You may wish to read this comparison of SSIS and Informatica: http://www.microsoft.com/sql/technologies/integration/conchango.mspx

|||Hi Jamie,

interesting paper... I'll give it a closer look this afternoon.

Thanks!|||

OK great. Again though, why don't you qualify SSIS as a professional ETL tool? Is it simply because it has a Microsoft badge on it?

-Jamie

|||Hi Jamie,

interesting paper. Thanks again.

Well, I think SSIS can perform ETL Jobs pretty well. But it's not a professional data integration platform, since there is no impact analysis, data lineage, metadata exchange, no out-of-the-box support for enterprise systems, etc. I would prefer Data Integrator even though its inital costs are much higher...

Don't get me wrong, I like Microsoft software (not every piece though ;) but for an enterprise scale data integration project I would use Data Integrator...

Steve|||

Steve,

Some thoughts here for you: http://blogs.conchango.com/jamiethomson/archive/2006/08/15/4353.aspx

I'd be interested in reading your paper when it is complete. Do you think that would be possible?

-Jamie

|||Hi Jamie,

thanks for putting this page in your blog online! I'm very anxious about the comments ppl are hopefully making.

Well, as I mentioned earlier, you can read my thesis but it's written in German not English. So if that's not a problem I'll send it to you!

Steve|||

It's an interesting topic.

Strictly speaking mature metadata reporting makes enterprise data integration easier to maintain and manage over time but it is not a pre-requisite of building data warehouses and BI databases. There are a lot of DataStage sites out there that are not using MetaStage and are not aware of the numerous alternative metadata methods just like there are a lot using PowerCenter but not using SuperGlue. Several years ago we had a lot of competition between a bunch of ETL tools none of which had profiling or metadata addons.

Informatica only bought a profiling and data quality tool at the start of this year, BO only bought FirstLogic in Feb, IBM-Ascential meanwhile have spent several years trying to merge the suite onto one metadata platform, so really is Microsoft that far behind the competition? There is a risk that metadata and SOA governance becomes a big business trend in the next couple years but I don't see it captivating the market right now.

You've got to consider up front costs against ongoing costs and in some evaluations SSIS will come out ahead and in others it will be a full suite data integration vendor. A large number of sources and complex range of data problems certainly favours the full suite with profiling and metadata, but projects with a narrower scope may make SSIS more attractive.

|||

I have used Data Integrator and I prefer SSIS. My biggest complaint with DI was you cannot use a SQL Statements as a data source; you must use tables/views only (as far as I could tell). When I needed to filter a 10 million row table to only a few million records, it allowed me to set a filter on the source, but it had to read every record in first to apply the filter anyway.

Microsoft has always created a consistent look and feel to their products, making the transition from one development tool to another seamless. Within the past year, I've learned SSIS, SSAS, and SSRS just by trial-and-error, and am able to create end-to-end reporting and anlaysis solutions for our customers within a few weeks.

And I believe it will just be a matter of time before new "Data Profile" SSIS packages or custom tasks are created by the community, as was the Metadata Toolkit, and custom source adapters for enterprise systems will surface for SSIS, all probably downloadable for free.

-Kory

Metadata Model in SSIS 2005

Hi *,

Can anyone tell me what Metadata Model Microsoft supports with SSIS 2005? DTS in 2000 was supporting OIM but I was not able to get some information about this topic on SSIS 2005.

The reason I need to know is that I wanna import some metadata from Business Objects Data Integrator into SSIS 2005 and Business Objects is supporting CWM....

I would appreciate your help!

Thank you,
Steve

Can you explain what OIM & CWM stand for and what they mean?

I wasn't aware of DTS supporting any metadata models.

-Jamie

|||CWM stands for Common Warehouse Metadatamodel; OIM for Open Information Model

OIM was supported by SQL Server 2000, but this standard doesn't exist anymore. CWM is supported by a wide range of ETL-Tools (including BusinessObjects Data Integrator).

For example, it's possible to export Data Integrators Datastores and insert them into another system like ERWin. Because both support a common metadata model. I just want to know if I can share metadata with SQL Server Integration Services?

At least SSIS creates a file named CWM_1.0.dtd in my project folders which indicates some support...|||

Interesting stuff!

SSIS doesn't have a great metadata story to be honest, certainly not anything that adheres to industry standards (I hereby stand to be corrected). So you probably can't do the things that you are talking about. As far as I am aware the products you talk about there make heavy use of metadata (I'm assuming through some sort of metadata repository?) but SSIS's package architecture is different - it doesn't lend itself to metadata in quite the same way (in my opinion).

I can't find a CWM* file in any of my folders. Where exactly is yours?

How did SQL Server 2000 support OIM?

Sorry, I haven't been much help there have I?

-Jamie

|||Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...
SSIS doesn't support metadata exchange, has poor data profiling, and no possibilities to do perform impact/lineage analysis (w/out metadata samples toolkit)... does anyone have some success stories where Microsoft's Integration Services handled a really big data integration project (except for Project REAL)?|||

ssauerw wrote:

Hi Jamie,

thanks for your help. I'm really frustrated that Microsoft's information policy on this subjects is so poor... DTS supported OIM because Microsoft had a different approache in storing meta data. Correct me if I'm wrong but SQL 2000 had a central repository, right?

No. Not for DTS.

ssauerw wrote:

Well I'm disappointed that MS that there is no chance to import metadata into SSIS...

BTW: I'm writing on my diploma thesis where I'm comparing data integration suites...

I'd be interested in reading that when its complete!!!

-Jamie

|||Well, you can read it but it's in German ;)

Why do you use SSIS instead of a "professional" Data Integration Tool like PowerCenter or BODI? Just the price?

Steve|||

Why don't you qualify SSIS as a professional data integration tool?

Functionally SSIS has all of the features that tools such as those have - the main difference is, I suppose,,the architecture. I would qualify tools like Informatica Powercentre (which I have used extensively) as a pureplay ETL tool - I believe SSIS is much more than that - it is a platform for data integration. The range of features that you get out-of-the-box dwarfs Informatica - it really does.

You are correct though - the main differentiator is the price. And don't forget that SSIS comes with a free relational database server in the box.

-Jamie

P.S. You may wish to read this comparison of SSIS and Informatica: http://www.microsoft.com/sql/technologies/integration/conchango.mspx

|||Hi Jamie,

interesting paper... I'll give it a closer look this afternoon.

Thanks!|||

OK great. Again though, why don't you qualify SSIS as a professional ETL tool? Is it simply because it has a Microsoft badge on it?

-Jamie

|||Hi Jamie,

interesting paper. Thanks again.

Well, I think SSIS can perform ETL Jobs pretty well. But it's not a professional data integration platform, since there is no impact analysis, data lineage, metadata exchange, no out-of-the-box support for enterprise systems, etc. I would prefer Data Integrator even though its inital costs are much higher...

Don't get me wrong, I like Microsoft software (not every piece though ;) but for an enterprise scale data integration project I would use Data Integrator...

Steve|||

Steve,

Some thoughts here for you: http://blogs.conchango.com/jamiethomson/archive/2006/08/15/4353.aspx

I'd be interested in reading your paper when it is complete. Do you think that would be possible?

-Jamie

|||Hi Jamie,

thanks for putting this page in your blog online! I'm very anxious about the comments ppl are hopefully making.

Well, as I mentioned earlier, you can read my thesis but it's written in German not English. So if that's not a problem I'll send it to you!

Steve|||

It's an interesting topic.

Strictly speaking mature metadata reporting makes enterprise data integration easier to maintain and manage over time but it is not a pre-requisite of building data warehouses and BI databases. There are a lot of DataStage sites out there that are not using MetaStage and are not aware of the numerous alternative metadata methods just like there are a lot using PowerCenter but not using SuperGlue. Several years ago we had a lot of competition between a bunch of ETL tools none of which had profiling or metadata addons.

Informatica only bought a profiling and data quality tool at the start of this year, BO only bought FirstLogic in Feb, IBM-Ascential meanwhile have spent several years trying to merge the suite onto one metadata platform, so really is Microsoft that far behind the competition? There is a risk that metadata and SOA governance becomes a big business trend in the next couple years but I don't see it captivating the market right now.

You've got to consider up front costs against ongoing costs and in some evaluations SSIS will come out ahead and in others it will be a full suite data integration vendor. A large number of sources and complex range of data problems certainly favours the full suite with profiling and metadata, but projects with a narrower scope may make SSIS more attractive.

|||

I have used Data Integrator and I prefer SSIS. My biggest complaint with DI was you cannot use a SQL Statements as a data source; you must use tables/views only (as far as I could tell). When I needed to filter a 10 million row table to only a few million records, it allowed me to set a filter on the source, but it had to read every record in first to apply the filter anyway.

Microsoft has always created a consistent look and feel to their products, making the transition from one development tool to another seamless. Within the past year, I've learned SSIS, SSAS, and SSRS just by trial-and-error, and am able to create end-to-end reporting and anlaysis solutions for our customers within a few weeks.

And I believe it will just be a matter of time before new "Data Profile" SSIS packages or custom tasks are created by the community, as was the Metadata Toolkit, and custom source adapters for enterprise systems will surface for SSIS, all probably downloadable for free.

-Kory

metadata driven SSIS package--table variable

Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!

Oliver

Hmm,

I suppose you could do it that way. I am currently generating complete SSIS packages from metadata. No variables. Explicit references. You would still need to handle the objects of the package that change, like columns datatypes, etc...|||You cannot have 1 data-flow that is used for different tables (unless the structure of each table is exactly the same - which I doubt).

You will need multiple data-flows, one for each table.

Once you have your multiple data-flows, decide which one of them to execute using workflow expressions. Allan and Darren have a good example of this here: http://www.sqlis.com/default.aspx?306

-Jamie|||Patrick,

Do you think you could post a simple example of what you are doing? I think there are a lot of people in the SSIS community who are realizing they need to head toward dynamic package creation in order to achieve the maintainability that they were able to with DTS and ActiveX scripts.

Thanks,

Jason.|||Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver|||

Oliver77 wrote:

Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver

"More or less the same" isn't enough. They have to be exactly the same for them to be able to use the same data-flow.

-Jamie|||

Can we create a SSIS package using VB .NET? If we can, we can call metadata driven stored procedure to build the transformation task specifically for each table.

|||

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

|||

Oliver77 wrote:

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

One of the samples provided with the product shows how to build a package progamatically (although I've just looked at my Junne CTP installation and can't find it - perhaps its been removed?). Once you have mastered how to do that you could work on building them based on metadata. I haven't seen any examples of doing that - perhaps you'd be the first person to do it?

-Jamie|||Patrick,

DO you have sample code? Thanks!|||Patrick,

Can you give some rough ideas as what you are using in terms of programming languages, scripts or SSIS tasks to create metadata driven SSIS packages.

Many thanks!

Oliver

Metadata driven SSIS

I am looking for some pointers on Metadata driven SSIS. Specifically, how to automate creation of a large number of packages in SSIS using metadata. I am looking at this as a solution to deploy multiple packages on multiple servers. Any other ideas on this would be highly appreciated.

Thanks

Vishal Verma

This feature is not available right now. But seems we can get this feature in the next service pack (may be in end 2006)|||While I cannot confirm Suresh is correct (and in fact this is the first time I'm hearing this being associated with service pack), you can go through the object model documentation and generate the packages programmatically.

regards,
ash|||Well, I am not sure either. I read some posts by Microsoft guys (Ash why dont u chat with ur boss Kamal or Donald) and what I infered is that we might get something in near future.|||Ash is correct as usual. There are no confirmed plans for work in the SP. You may have interpreted what Kamal or Donald said as an assertion of forthcoming features. That is not correct. The planning for SPs and other releases is still under way and will not be announced for some time.
K|||By the way, I think what Suresh may be talking about here is that we're doing some work to identify and promulgate best practices around metadata for IS that will be released as either a KB article or whitepaper. That is supposed to be available around the time IS is released. But, no new metadata features, per se.

Metadata application

Hi all of you,

I'm focused on writing a SSIS metadata application. I'm trying to get all the metadata for all the DTSX packages from a concrete server.

I'm loading successfully tasks as Execute Sql Task, ForEach task and so on but how could I do the same for

Data Flow Task and its components. ???

For a Data Flow I don't know how to begin...

If TypeOf tmpTaskHost.InnerObject Is ? Then

My central code is the following:

sServer = "TEST1"

pkgIn = app.GetPackageInfos(carpetaraiz, sServer, Nothing, Nothing)

'''''''''''''''Para cada carpeta creada a partir de \MSDB

For Each pkgCarpeta In pkgIn

If pkgIn.Item(y).PackageDataSize <= 0 Then

pkgCarpetaSSIS = app.GetPackageInfos(pkgCarpeta.Name, sServer, Nothing, Nothing)

Else

pkgCarpetaSSIS = app.GetPackageInfos(carpetaraiz, sServer, Nothing, Nothing)

End If

If pkgCarpetaSSIS.Count >= 1 Then

While i < pkgCarpetaSSIS.Count

'cargar en memoria el SSIS en el objeto PAQUETE

pkg = app.LoadFromSqlServer(pkgCarpetaSSIS.Item(i).Folder & "\" & pkgCarpetaSSIS.Item(i).Name, _

sServer, Nothing, Nothing, Nothing)

Dim task As Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask

Dim task2 As Microsoft.SqlServer.Dts.Runtime.ForEachLoop

Dim exe As Executable

Dim tmpTaskLoop As ForEachLoop = CType(exe, ForEachLoop)

Dim tmpTaskHost As TaskHost = CType(exe, TaskHost)

For Each exe In pkg.Executables

Select Case exe.ToString

Case "Microsoft.SqlServer.Dts.Runtime.ForEachLoop"

tmpTaskLoop = CType(exe, ForEachLoop)

Case "Microsoft.SqlServer.Dts.Runtime.TaskHost"

tmpTaskHost = CType(exe, TaskHost)

End Select

issue raises here.

If TypeOf tmpTaskHost.InnerObject Is ExecuteSQLTask.ExecuteSQLTask Then

n1 = tmpTaskHost.Properties.Item(33).GetValue(tmpTaskHost)

For y = 0 To 42

Console.WriteLine(tmpTaskHost.Properties.Item(y).Name)

Next

End If

Next

'contador de paquetes

i += 1

End While

y += 1

End If

Next

..

..

..

..

..

Thanks a lot for ideas and thoughts!!!!

Hi enric

see http://www.developer.com/db/print.php/3567941

There is application called ExploreIS which the author of the website wrote ,which might be a good starting point.You can download the application source code from the above .Maybe use that as a starting point

Regards

Cedric

|||The inner object for the Data Flow is MainPipe.|||Thanks man. I'll check it.

Meta Integration Solutions

Hi,

Has anyone experience with Meta Integration Solutions and converting to SSIS ?

and converting to SQL ?

and converting to Analysis Server ?

and converting to Reporting Server ?

You can find their website http://www.metaintegration.net/Products/Overview/Solutions.html

Constantijn Enders

You can check out http://www.metaintegration.net/Partners/Microsoft.html for specific versions supported. Scroll down where it says SSIS (dtsx) packages will be supported from their 6/1/07 release. The contact info of folks from MSFT are available on the same page.

Meta Integration Solutions

Hi,

Has anyone experience with Meta Integration Solutions and converting to SSIS ?

and converting to SQL ?

and converting to Analysis Server ?

and converting to Reporting Server ?

You can find their website http://www.metaintegration.net/Products/Overview/Solutions.html

Constantijn Enders

You can check out http://www.metaintegration.net/Partners/Microsoft.html for specific versions supported. Scroll down where it says SSIS (dtsx) packages will be supported from their 6/1/07 release. The contact info of folks from MSFT are available on the same page.