Comparing two SSIS Packages?

I'd like to know how people out there are comparing ("diffing") their DTSX files.

Using XML file compare doesn't seem to work, because the package XML appears to be arbitrarily ordered and reordered by the designer in Visual Studio.

ApexSQL is supposedly planning to have a tool available in Q3, but even their product page does not seem too hopeful: "Compare and Document SSIS Packages (may be released with ApexSQL Doc and ApexSQL Diff in Q3)"

I remember seeing a CodePlex project that was hoping to address this and other issues, but it was in its infancy, and I can't seem to locate it today.

I'm thinking of starting to write my own package compare tool that will work with the SSIS .NET API, but this seems like it's going to be a ridiculous amount of work and an exercise in pain, and it seems unlikely that I will make the time to target anything more than the specific portions of the package model that I really need.

So if anyone out there has words of wisdom to share, I'd love to hear it. And if (dare I get my hopes up?) have a URL to share, I'll buy you a beer at TechEd in Orlando week after next.

Thanks in advance!

No words of wisdom to share, but I agree on the need for this kind of tool. We've been looking into rolling our own tools as well, but creating anything of general applicability would be a major project.|||

I don't know if there is a clever way of comparing packages. Perhaps you should open a suggestion in SQL Server connect site

http://connect.microsoft.com/site/sitehome.aspx?SiteID=68

[Microsoft follow-up]

|||

Thanks for the feedback, Rich and Rafael. I'll definitely post this as a suggestion in Connect, but...

Is no one out there (or more specifically, no one here on the SSIS forums) doing anything to compare or diff SSIS packages? I honestly wasn't expecting anyone to say "oh yeah, I use XXX tool, it does a great job" but I was expecting something...

To take this thread in a somewhat different direction, if I were to start a CodePlex project with the aim of developing a package diff utility based on the SSIS object model, would anyone else be interested in contributing? I'll bet I'm not the only SSIS ETL guy out there with a C# background...

|||

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

|||

jwelch wrote:

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

Thank you!

This was the CodePlex project I mentioned in my original post - I stumbled across it once, but could not find it again when I started this thread here.

|||

jwelch wrote:

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

So... what are you using to compare packages, today?

|||

I try not to

Usually I use a text difference tool and deal with the headaches of elements being in different order.

|||

jwelch wrote:

I try not to

Usually I use a text difference tool and deal with the headaches of elements being in different order.

Well, it's good to know that great minds do indeed think alike.

|||

Can I just add my voice to the need for such a tool. I still use DTS Compare for our 2000 installations and something similar for SSIS would be of great use.

compare

I am new SQL
I have XML files (jobs) that are generated from a third party app on a
weekly basis.
My SQL application loads the the xml files for production.

>From time to time changes will be made to the original xml files, which
requires a user to reload the job via the app.
However, the reporting process on these changes is somewhat anticquated
and sometimes these changes are missed and the job is not reloaded.
I am looking for a process to compare specfic dates values in the database
against either the file date on the xml files or the date that is
contained in the xml file and generate a report when the date is
in the DB is less then the file date
Any help is greatly appreciated.
Thank you
If you are using SQL Server 2005, look into the XQuery support that should
give you the comparison semantics...
Best regards
Michael
"steve" <steve@.discussions.microsoft.com> wrote in message
news:19713BEF-C2BD-46B8-A518-47656A51BA2C@.microsoft.com...
>I am new SQL
> I have XML files (jobs) that are generated from a third party app on a
> weekly basis.
> My SQL application loads the the xml files for production.
> requires a user to reload the job via the app.
> However, the reporting process on these changes is somewhat anticquated
> and sometimes these changes are missed and the job is not reloaded.
> I am looking for a process to compare specfic dates values in the database
> against either the file date on the xml files or the date that is
> contained in the xml file and generate a report when the date is
> in the DB is less then the file date
> Any help is greatly appreciated.
> Thank you
>

compare

I am new SQL
I have XML files (jobs) that are generated from a third party app on a
wly basis.
My SQL application loads the the xml files for production.

>From time to time changes will be made to the original xml files, which
requires a user to reload the job via the app.
However, the reporting process on these changes is somewhat anticquated
and sometimes these changes are missed and the job is not reloaded.
I am looking for a process to compare specfic dates values in the database
against either the file date on the xml files or the date that is
contained in the xml file and generate a report when the date is
in the DB is less then the file date
Any help is greatly appreciated.
Thank youIf you are using SQL Server 2005, look into the XQuery support that should
give you the comparison semantics...
Best regards
Michael
"steve" <steve@.discussions.microsoft.com> wrote in message
news:19713BEF-C2BD-46B8-A518-47656A51BA2C@.microsoft.com...
>I am new SQL
> I have XML files (jobs) that are generated from a third party app on a
> wly basis.
> My SQL application loads the the xml files for production.
>
> requires a user to reload the job via the app.
> However, the reporting process on these changes is somewhat anticquated
> and sometimes these changes are missed and the job is not reloaded.
> I am looking for a process to compare specfic dates values in the database
> against either the file date on the xml files or the date that is
> contained in the xml file and generate a report when the date is
> in the DB is less then the file date
> Any help is greatly appreciated.
> Thank you
>

Company Dimension

We deal with multiple vendors who provide us information via text/xml files. Vendor A may provide financial data, vendor b provides litigation data, vendor c provides ratings data. Our current structure has databases for each vendor with its own company table which basically makes all this data disconnected. Of course each vendor has its own proprietary company id to make records unique.

All of the data is based on companies so the grain of data would be at a company level. I would like to be able to link this information together by creating a dimensional model that has a single company table (DimCompany) and has facts populated based on the type of data we receive. Would this be the right sequence of events?

1. My initial load (historical) would have to look at all these data sources and create one company record in my DimCompany table. This table would then link to all other fact tables to provide a single view of company info. I would imagine this would have to be a fuzzy lookup since one company will be in all sources.

2. On subsequent loads (incremental) I would probably have to do a lookup of companies in the dimension via the proprietary code and add if the company wasn't there.

Any advice on tackling this issue would be greatly appreciated especially if SSIS was used in the process.

Hi jrp210,

Based on my interpretation of your schema, I would design DimCompany as you describe. One row per company, unique IDs, and you may wish to add surrogate keys - they will make your data mart / warehouse easier to scale.

I would also add a DimInformationType for the different types of data you receive.

Another option is to snowflake the InformationType table off of DimCompany. This is common when the referenced lookup table is relatively small and doesn't change often.

There are performance implications but in smaller data warehouses (~1 - 2G) you will probably never notice. And if you do, an index will likely clear up any performance issue.

I don't follow the need for a fuzzy lookup. These are expensive in SSIS and should only be used when necessary.

You are correct: incremental loading does require a lookup (or a merge join) to detect new records.

There's lots of good information online about how to do this. I suggest picking up one of the Kimball books for help in designing data warehouses - I particularly like the Data Warehoue Toolkit which is updated to include information on using SSIS for ETL.

Hope this helps,

Andy

|||

Here are my 2 cents:

Having a conformed 'Company' dimension is the way to 'join' all fact rows. Not sure about using a Fuzzy lookup as it may give you non-expected results; so evaluate yourself the margin of error your company can tolerate. In general you may want to asses the level of cleanness of your data, so you can anticipate the results

About how to organize the fact data; I would say that depends on the way the data is going to be used during analysis and reporting, and on the nature of the data. You may want to spend some time with the end users to get that feedback; draw a data model and review it with them again; this may take a few iterations.

Andy suggestion about Kimball’s book is also a good idea.

Good luck with that

|||

Thanks for the response. I did purchase the book you referenced. It has been very helpful but doesn't delve into the issue of trying to create one dimension table from multiple sources with different primary keys.

I will definitely be creating surrogate keys because more data sources will be introduced over the long haul.

I don't follow the DimInformationType table. Is this more of a helper table to map the surrogate company key to the proprietary key used by the vendor? For example:

The first time I run the historical load I will have to insert companies (take Microsoft for example) into my DimCompany table. This will be done by using vendor A's company info, vendor B, and so on. Microsoft's companyId in vendor A's system might be 123456, in vendor B's system it may be 789101. I want one instance of Microsoft in my DimCompany table so I will have to do a lookup to make sure it isn't already in the table before inserting. If it is not in the DimCompany table then I will add to DimCompany and then add to another table that has the surrogate key/vendor a key combination.

When its time to access vendor B's file, most of the companies will be in the DimCompany table. If not, follow the same procedure as above. If they are in the DimCompany table then I will have to add a row to the vendor b helper table with the surrogate key/vendor b key.

It all stems from the fact that each vendor has its own proprietary (different) key for the same company. I don't know how I would get around not using fuzzy logic or some sort of text mapping. Text mapping could be dangerous as well since the names may be slightly different.

|||

There are 2 other books from Kimball’s group:

The Data Warehouse ETL tool kit

The Microsoft Data Warehouse Toolkit

The first one cover how to conform dimension (coming from different sources).; the second one covers Kimball's warehouse methodology using SQL Server 2005 tools.

|||

jrp210 wrote:

The first time I run the historical load I will have to insert companies (take Microsoft for example) into my DimCompany table. This will be done by using vendor A's company info, vendor B, and so on. Microsoft's companyId in vendor A's system might be 123456, in vendor B's system it may be 789101. I want one instance of Microsoft in my DimCompany table so I will have to do a lookup to make sure it isn't already in the table before inserting. If it is not in the DimCompany table then I will add to DimCompany and then add to another table that has the surrogate key/vendor a key combination.

Hi jrp210,

That's different from what I understood previously. Don't feel bad, this happens to me a lot.

Maybe your schema looks like this:

CompanySK (surrogate key) CompanyName (business key)
1 Microsoft

VendorSK (surrogate key) VendorName (business key)
1 Vendor A
2 Vendor B

VendorCompanySK VendorCompany_CompanySK VendorCompany_VendorSK VendorCompany_ID
1 1 1 123456
2 1 2 789101

This is a snowflake that allows you to utilize Company and Vendor separately in facts, and also use VendorCompany in facts. There are foreign key relationships between DimCompany and DimVendorCompany, and DimVendor and DimVendorCompany.

Hope this helps,

Andy

|||

Andy,

My goal would be to create one company record from multiple sources. I don't necessarily need a vendor or vendorcompany table but will probably need their proprietary company id as an attribute in my company dimension table so that one could link back using their key.

I would assume something like this:

CompanySK
VendorA CompanyId
VendorB CompanyId
VendorC CompanyId
Company Name

For the most part one row should contain an Id for vendor a,b, and c but there are times when that is not the case. Is that why the company name would be the business/natural key?

|||

The first thing you need to do if to define the grain of your dimension; it looks to me like the grain is one row for each company (even when that company exists in several vendor data sources); so if you have more than one 'version' of a company, like in your example of Microsoft company, you would need some kind of auxiliary table to keep that 1:many relationship between the many rows/company in the source and the 1 row per company n your dimension.

BTW, this has nothing to do with SSIS...but I hope it helps

|||

You are right about the SSIS - probably more geared toward dimensional modeling/DW. But, I am using SSIS to do that so this is where I originally posted to.

Yes, grain of the dimension is the company. The really isn't more than one version of the company. It is the same company coming from different source (vendor) systems. The vendor systems have different unique keys in which they tag a company. Because of this there isn't one natural key to use across all. If I understand what you are saying is that this auxillary table would "create" the natural key that will be used in the dimension table?

|||

jrp210 wrote:

If I understand what you are saying is that this auxillary table would "create" the natural key that will be used in the dimension table?

That is right. You could create a surrogate key in the dimension and then that auxiliary table will keep the relationship between source system keys(many for a company) and the Dimension surrogate key (one per company).

|||

At first it makes sense but to initially load the database how would you keep the company unique in the DimCompany table without a key that would link them together? Or better yet put, which comes first loading the auxillary table or Dimension table?

I was assuming the auxillary table would be loaded first:
VendorId
VendorCompanyCode
CompanyName
etc.

But then I would have to create a unique key that would be used in the DimCompany table. There would have to be another table that then creates this key (identity column) that would be used in the DimCompany table.

Communicate XML between SP’s

Hi,
I have two databases and need to transfer data between them. The problem is
that I’m facing the 8000 characters limitation of a varchar and I cannot use
a text declaration within the trigger/SP itself. Therefore I had the
following idea:
SP 1:
CREATE PROCEDURE [DBO].[testXMLSelect] AS
declare @.idoc as int
--Load and parse the XML document in Memory
EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
for xml auto
exec testXMLInsert @.idoc
--now clear the XML document from memory
EXEC sp_xml_removedocument @.idoc
GO
SP 2
CREATE PROCEDURE [dbo].[testXMLInsert]
@.idoc as int
AS
insert into testtext (test)
select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
GO
My Idea was to parse the handle to the XML document in Memory from one to
the other SP, and later on to the other database (same server). Does anyone
have an idea how to solve this?
Regards,
Merijn
You cannot use a select statement as a parameter to a stored procedure...
assign the result to a variable and pass the variable.
Also, you may want to upgrade to SQL Server 2005 where you don't have the 8k
limit anylonger (and have XML data type and much more).
Best regards
Michael
"Merijn" <Merijn@.discussions.microsoft.com> wrote in message
news:482A7B95-4FC7-4C36-B4C9-BDFDAC76C31C@.microsoft.com...
> Hi,
> I have two databases and need to transfer data between them. The problem
> is
> that I'm facing the 8000 characters limitation of a varchar and I cannot
> use
> a text declaration within the trigger/SP itself. Therefore I had the
> following idea:
> SP 1:
> CREATE PROCEDURE [DBO].[testXMLSelect] AS
> declare @.idoc as int
> --Load and parse the XML document in Memory
> EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
> for xml auto
> exec testXMLInsert @.idoc
> --now clear the XML document from memory
> EXEC sp_xml_removedocument @.idoc
> GO
> SP 2
> CREATE PROCEDURE [dbo].[testXMLInsert]
> @.idoc as int
> AS
> insert into testtext (test)
> select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
> GO
> My Idea was to parse the handle to the XML document in Memory from one to
> the other SP, and later on to the other database (same server). Does
> anyone
> have an idea how to solve this?
> Regards,
> Merijn
>
|||On Feb 23, 8:42 am, Merijn <Mer...@.discussions.microsoft.com> wrote:
> Hi,
> I have two databases and need to transfer data between them. The problem is
> that I'm facing the 8000 characters limitation of a varchar and I cannot use
> a text declaration within the trigger/SP itself. Therefore I had the
> following idea:
> SP 1:
> CREATE PROCEDURE [DBO].[testXMLSelect] AS
> declare @.idoc as int
> --Load and parse the XML document in Memory
> EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
> for xml auto
> exec testXMLInsert @.idoc
> --now clear the XML document from memory
> EXEC sp_xml_removedocument @.idoc
> GO
> SP 2
> CREATE PROCEDURE [dbo].[testXMLInsert]
> @.idoc as int
> AS
> insert into testtext (test)
> select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
> GO
> My Idea was to parse the handle to the XML document in Memory from one to
> the other SP, and later on to the other database (same server). Does anyone
> have an idea how to solve this?
> Regards,
> Merijn
I have a requirment to share data between two databases and I'm hoping
you could outline to me the steps you have taken to get me started. I
won't run into the varchar limit in my application.
Thanks,
lq
|||On Feb 25, 9:54 am, "Lauren Quantrell" <laurenquantr...@.hotmail.com>
wrote:
> On Feb 23, 8:42 am, Merijn <Mer...@.discussions.microsoft.com> wrote:
>
>
> I have a requirment to share data between two databases and I'm hoping
> you could outline to me the steps you have taken to get me started. I
> won't run into the varchar limit in my application.
> Thanks,
> lq
Now that you mentioned the Server is same, but have 2 seperate DBs,
how about using DB Name of the 1st DB in your QUERY of 2nd DB;
something like this
In DB2, you have your query as
Create table #t (MyXMLColumn XML )
Insert INTO #t (MyXMLColumn)
Select <WhatEverXMLColumnNameYouWant> From
<DBName>.<ObjectOwner>.testtext where id=3
|||On Mar 4, 2:59 pm, prabh...@.gmail.com wrote:
> On Feb 25, 9:54 am, "Lauren Quantrell" <laurenquantr...@.hotmail.com>
> wrote:
>
>
>
>
>
> Now that you mentioned the Server is same, but have 2 seperate DBs,
> how about using DB Name of the 1st DB in your QUERY of 2nd DB;
> something like this
> In DB2, you have your query as
> Create table #t (MyXMLColumn XML )
> Insert INTO #t (MyXMLColumn)
> Select <WhatEverXMLColumnNameYouWant> From
> <DBName>.<ObjectOwner>.testtext where id=3- Hide quoted text -
> - Show quoted text -
In my case it's two different servers I cave to communicate using SOAP/
XML to send data from the SQL Server 2000 database to the remote
server.

Combining XQuery and data paging in SQL Server 2005

Folks,
I have a table in SQL Server 2005 that contains a row of type "XML" (for now
untyped - no schema behind it).
I can easily query that table and retrieve values from the XML - way !
I can also easily query that table and use the data paging mechanism based
on the ROW_NUMBER() function - way , too!
But I can't seem to combine the two...
Imagine I have books stored in my XML column:
<books>
<book id='123'>
<author>Smith</author>
<yearpublished>1999</yearpublished>
</book>
<book id='177'>
<author>Black</author>
<yearpublished>2002</yearpublished>
</book>
... (lots more books) .....
</books>
To query the table, I use something like:
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as 'YearPublished'
FROM BooksTable
Works fine. Now what I'd like to do is add a ROW_NUMBER() function over the
e.g. "YearPublished" attribute (stored in the XML) to get my books by year
published, in batches of 10 or whatever:
WITH BooksList as
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as 'YearPublished',
ROW_NUMBER() OVER(ORDER BY YearPublished) AS 'rownum'
FROM BooksTable
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20
Trouble is - this won't work, since MgmtStudio complains it doesn't know
about the "YearPublished" column.....
What am I missing? Can I still achieve this goal somehow?
Any hints are most welcome !!
Marc
(mscheuner -* AT *- gmail.com)Untested but try this
WITH BooksList as
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as
'YearPublished',
ROW_NUMBER() OVER(ORDER BY
BooksXML.value('(/book/yearpublished)[1]', 'int')) AS 'rownum'
FROM BooksTable
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20|||... or :-)
WITH x AS
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') AS Author,
BooksXML.value('(/book/yearpublished)[1]', 'int') AS YearPublished,
FROM BooksTable
), BooksList AS (
SELECT
*
,ROW_NUMBER() OVER (ORDER BY YearPublished) AS RowNum
FROM x
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20
Also, please consider not using single quotes (') for naming aliases, you
should use double quotes (") as that is the ANSI standard and using single
quotes will be debrecated.
HTH
/ Tobias

Combining XML Files using SSIS or T-SQL etc.

How can I combine all my xml files so it can be processed by SSIS in order
that I may create a table containing the imported xml files?Loop?
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
> How can I combine all my xml files so it can be processed by SSIS in order
> that I may create a table containing the imported xml files?
>|||What is Loop?
Could you please explain more in detail.
Thank you in advance for your assistance.
"Peter W. DeBetta" wrote:

> Loop?
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
>
>|||SSIS now has a Control Flow Item called Foreach Loop Container. You use this
to loop through all the xml files in a specified directory. You will also
need to create a Data Flow Task in the Foreach Loop Container. The samples
that come with SQL Server 2005 have an example of using this container
control, and you can find more info in BOL.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5EAFCB63-AC58-4592-A7DF-786AF5CABBBF@.microsoft.com...
> What is Loop?
> Could you please explain more in detail.
> Thank you in advance for your assistance.
> "Peter W. DeBetta" wrote:
>