Direct deployment issues

Hi

I have found article by Jamie T. (www.sqlis.com/26.aspx) where he explains how to use direct configuration.

I followed every step.

Before building project I made sure that CreateDeploymentUtility = True.

Once project was built I have copied files from Deployment folder to other PC to c:\PackageConfiguration (I have 3 files there: environment.dtsconfig, PersonAge.dtsx and jamie.SSISDeploymentManifest)

dtsConfig file is;

<?xml version="1.0" ?>

- <DTSConfiguration>

- <DTSConfigurationHeading>

<DTSConfigurationFileInfo GeneratedBy="me" GeneratedFromPackageName="PersonAge" GeneratedFromPackageID="{2612ABDA-E4FB-43DF-A1C1-44066426A798}" GeneratedDate="7/24/2007 9:46:41 AM" />

</DTSConfigurationHeading>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[Destination].Properties[InitialCatalog]" ValueType="String">

<ConfiguredValue>DataStore</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[Destination].Properties[ServerName]" ValueType="String">

<ConfiguredValue>MyPC\sql2k5local</ConfiguredValue>

</Configuration>

</DTSConfiguration>

However, I am doing something wrong as I was not able to deploy this to another PC.

I double click Manifest file and in Configuration Package - Property there are two entries: InitailCatalog and ServerName. Initial Catalog has Value=DataStore and ServerName = MyPC\sql2k5local. I change ServerName to be AlexPc\SQL2k5Local.

I have Validate Package after instalation so I get error saying that connection to Destination failed.

If I go to File System to installed package and say Run, package will run OK if I change all ConnectionManagers to point to AlexPc\SQL2k5Local

What is it that I am doing wrong?

I would recomend you to make the deplyment without the deployment utility. If you are using file based packages; you just have to copy the .dtsx and configuration files to the target location. At this point I am not clear wheter you are having problems with the deploymnet or with the package configurations.|||

Thanks Rafael!

it really is simple as that, no need to use utility

ok, next step for me is to try to use indirect configuration

|||

Glad you got it to work!

Here is thread you may find interesting about indirect configurations:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1125596&SiteID=1

|||

This is funny!
I was just looking at this. Issue is that link you had as solution does not work. I was just going to come here and ask you how can I get to that article.

So, please let me know if there is a link that works.

Also, I have found one yesterday on your blog (SSIS package configs using sql server table ...an alternative to Indirect method) but my comment on it is that screen shots were way too small. I was not able to see anything on those.

|||

Also, i have an issue with MSDB; your input would be greatly appreciated.

I am trying to deploy a package to SQL. However, when I open Integration services I can not expand MSDB folder. Error is; SQL Server specified in SSIS service configuration is not present or available.... Login time expired .....(MsDTSSrvr)

I have found post here that says MsDtsSrvr.ini.xml file needs to be changed from

<Name>MSDB</Name>
<ServerName>.</ServerName>

to

<Name>MSDB</Name>
<ServerName>MyServerName</ServerName>

I tried MyServerName - does not work. I also tried value from connecting to Database Engine ( MyServerName\SQL2K5LOCAL) - does not help either.

What am I doing wrong?

this is what I am using:

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: IDE Standard

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

|||

I have updated the link:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

I never have deployed the packages to the DB; but I know this forums has some threads that talks about not being able to expand the MSDB folder. USe the search capability of the forum

sql

dimension loading best practice?

It has been suggested that when loading dimensions, the first step should be to clear the current dimension table and reload in total from the data source. I use integer identity PK fields in all my dimension tables.

When loading my fact table I'm only loading rows that have been added or changed since the last SSIS run.

Wouldn't this introduce the possibility of mis-matched key relationships if, for some reason, the order of the rows in the dimension table changed and a fact row that doesn't get modified in this SSIS run pointed to a surrogate key in a dimension table that was changed?

I suppose in most cases a change in the OLTP that caused a reordering of the dimension table - say a customer was dropped - would show up as a modification in the fact load and cause the row to be updated. However if someone changed the indexing in the OLTP it might cause the customer order to chagne causing them to be loaded in a different sequence with different surrogate keys in the dimension table.

It just concerns me that a possibility for corruption seems to exist in this type of scenerio. It seems like one should load the dimensions and facts in the same manner.

Who suggests truncating dimensions before loading them? This is not good practice. New rows should be inserted and existing rows should be updated. If you need to track changes (Slowly Changing Dimension), then you should use the SCD wizard inside SSIS.|||

John:

I am a student of the Kimball method of data warehousing. In most of my experience I have never completely rebuilt a dimension during each update, for precisely the same reason you mention. In my experience most changes in dimensions are handled using the Slowly Changing Dimension process, and SSIS provides a very elegant tool to do this for you.

I am not sure what your fact is, but unless you have scheduled a regular roll-out of data from your warehouse, say you only wanted to maintain a specific time period of data in your warehouse, you should not really be deleting rows from your dimension. If you have a very large dimension that you wish to purge occassionally I suggest that you devise a process that will account for the fact relationships that exist with the dimension records that are marked for deletion.

On a regular basis though it is not typical to completely refresh dimensions during each update.

|||

Thanks! I thought it came from the Kimball video posted here but I could have misunderstood something I saw on the screen that was being used for something else or it might have been from one of my books. It seemed too easy to be workable. However it's not that big a deal to treat dimension loading the same as facts.

I'm lucky in that I don't have any slowly changing data (yet) so I can treat all my modifications as simple updates. And every row in my OLTP has a CreateDate, ChangeDate, DeleteDate attached to it.

|||The only thing I truncate in my data warehouses are aggregated fact tables. The detail fact tables undergo inserts only, while aggregates (monthly data, for instance) are just roll ups of the detail tables. Instances like this are perfect for truncations since they need to be rebuilt every night.|||

Now that I'm looking at incremental updates of my dimensions I forsee another problem.

My sales date comes from the same OLTP table as most of my facts. I don't have a separte date table. So to load the date dimension I'm going to read the same rows as I would for loading my fact table. My OLTP row will alert me that the row has been modified but NOT whether the date column has been modified.

Also, I assume I'm going to be picking up several thousand sales each day so there will be several duplications. Is this why some designs, including some from microsoft, leave the date field in the fact table and let SSAS generate date dimensions from there?

|||

You do not need to have a data source of a Time Period table. You can very simply create a time period dimension using SSIS or a stored procedure for a time period that spans the entire history and then several years into the future, and the appropriate grain of your fact.

Again, it is typical that you have a very robust Time Period dimension that has all the attributes users would need to slice the data along.

|||

John:

I suggest you start a new thread on this topic of Time Dimension as you have already marked this post as being answered. This way others will look at the post.

Dimension Creation Problem

I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very necessary
question and many just like it. How do I design my dimensions so that I can
sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.You can either use a view or design your dim tables to accomodate
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Fischer" <jfischer@.bi-vetmedica.com> wrote in message
news:AC8E6672-7EAB-4DCB-A9AB-38F02E2A4ACC@.microsoft.com...
> I am currently developing data warehouse design documentation for our
company as our first step in creating a data warehouse. During the design I
ran into an issue:
> Our users want to answer this question: "What are our Export sales?"
> Now that is a tricky question and I'll explain why...Export sales is the
combination of sales for all products with the Product Type of Export and
any sales (for any product) that are of type Export. I am stumped on how to
accomidate this very necessary question and many just like it. How do I
design my dimensions so that I can sum based on 2 different dimensions into
one answer?
> I am sorry if this is a simple question, but for some reason it has me
scratching my head.|||I think you need two dimension tables - salestype and product(which contains
product type field).
You also need one fact table - sales with foreign key to product and one to
salestype.
so when you want to ask question "What are the Export Sales", you can query
fact table sales where salestype =
export and product type = export.
-- Jason Fischer wrote: --
I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very neces
sary question and many just like it. How do I design my dimensions so that
I can sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.

differences in SSIS file locations in SQL Server Agent step

When adding an SSIS step to a SQL Server Agent job, when selecting the location of a config file, the dialog lets you select from the database server you're working with. If selecting the location of the package itself (when the source is File System), the dialog lets you select from the machine where Management Studio is sitting instead of from the database server. Is that intentional? And if so, why? Should I just use a fully qualified file name for the package location rather than one using a drive letter?
Hmmm... Yeah, I don't like that. Though I always deploy by storing the packages in SQL Server versus the file system.

Let's let some of the other guys chime in.... I do know that some of the folks around here never use the SSIS step type in Agent and instead use the Command Line type. That allows them to build the DTEXEC statement the way they need it to be.|||

Phil Brammer wrote:

I do know that some of the folks around here never use the SSIS step type in Agent and instead use the Command Line type. That allows them to build the DTEXEC statement the way they need it to be.

That's might be the way I go. In production we'll be using Control-M anyway, so I might as well just have the command-line all prepped and ready to go for that.
|||

Phil Brammer wrote:

Let's let some of the other guys chime in.... I do know that some of the folks around here never use the SSIS step type in Agent and instead use the Command Line type. That allows them to build the DTEXEC statement the way they need it to be.

And it lets us get the full output of DTEXEC, which is handy for troubleshooting.