Access Cube Detailed Info

Hello,

Could I write ActiveX Script in a DTS Package to return cube info (such as CubeName, CubeStatus, CubeSize, CubePartitions, CubeLastProcessed)? If that's
possible, I could then pump in the result into text file and import them into
a table for reporting purposes.

Please let me know if this is the best route to explore DSO objects or any alternative way of extracting this type of Cube data.

-Lawrence

Oops... this is a duplicate post due to network error. If you could respond to the previous posting, I would really appreciate it!

-Lawrence

Accesing package Variable in Conditional Component

Hello Experts,

I am trying to load data to a sql table from flat file..I have a script component which will validate the data. I am also firing the errros when data validation fails. Once I encounter an error I want the whole package to be aborted. What I have done is declared a variable called errorcount (Scope :Package) and I am incrmenting this variable everytime an error is raised and next I have a Conditional Split which will check if this variable count is greater than 0 if yes I will not update the table.This is the only way I think this will work.

error I am getting is :The collection of variables locked for read and write access is not available outside of PostExecute.

I understand they r in the same data flow thats the reason I get this error but how can I by pass it ?

MY dataflow-> Flat File Source-Script Comp-Condtional Split-(If errorCount==0)-SQL Table

How can I redo the dataflow so that this err is not raised.

I have tried to add an output column (blnContinue) and use that in conditional stmt but as this is stored at row level values which have blnContinue as true are getting processed, I dont want this I just want to stop the package execution even if a single validation fails

Pardon my long question

Appreciate your help in advance

You idea will not work for two reasons. 1 the limitation of accessing variables, which is really about performance. 2 if your error is the last row, and you have multiple buffers, the first buffer could have already moved downstream and been written to SQL, as at that point the split would have still been passing.

A better way may be to just throw and exception in the component when you encounter an error. Whilst you still have an issue with data moving downstream before all rows have been checked, but you could workaround this by using a transaction or a commit size of 0 to ensure all or nothing at the SQL end. You could do a similar method by using basic SQL transactions, avoiding DTC, and that way you get to issue the BEGIN TRAN and COMMIT TRAN, or of course when the variable says you have bad data, the ROLLBACK. SSIS Nugget: RetainSameConnection property of the OLE DB Connection Manager - (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx)

Another method would be to stage the data between the validation/cleansing and load. Then check the variable and decide if you want to go on a load SQL afterwards. Raw files are the cheapest phstical persistance, they have very little overhead, just the IO cost really.

|||

Appreciate your response

How do I specify Commit size of 0 to a SQL Destination?

|||For the SQL Server Destination it is called MaxInsertCommitSize, look in the properties grid, I could not see it in the component editor UI.|||

Hmmm...yeah but what if all the files get validated right and there will be some existing records which need to be updates and the otehrs need to be inserted...

If no errors found this is how process should end if found completly abort the process...

Struck ova here....arghhhhh

|||Now you're changing the requirements, but still the options can work. So options now available as I see it are the staging idea, validate everything first before you load into SQL in a second Data Flow task. The other option is to use a transaction. Can be quite expensive, but ultimately that is what they are there for, either a package transaction or the retain same connection method as linked above.

Accesing package Variable in Conditional Component

Hello Experts,

I am trying to load data to a sql table from flat file..I have a script component which will validate the data. I am also firing the errros when data validation fails. Once I encounter an error I want the whole package to be aborted. What I have done is declared a variable called errorcount (Scope :Package) and I am incrmenting this variable everytime an error is raised and next I have a Conditional Split which will check if this variable count is greater than 0 if yes I will not update the table.This is the only way I think this will work.

error I am getting is :The collection of variables locked for read and write access is not available outside of PostExecute.

I understand they r in the same data flow thats the reason I get this error but how can I by pass it ?

MY dataflow-> Flat File Source-Script Comp-Condtional Split-(If errorCount==0)-SQL Table

How can I redo the dataflow so that this err is not raised.

I have tried to add an output column (blnContinue) and use that in conditional stmt but as this is stored at row level values which have blnContinue as true are getting processed, I dont want this I just want to stop the package execution even if a single validation fails

Pardon my long question

Appreciate your help in advance

You idea will not work for two reasons. 1 the limitation of accessing variables, which is really about performance. 2 if your error is the last row, and you have multiple buffers, the first buffer could have already moved downstream and been written to SQL, as at that point the split would have still been passing.

A better way may be to just throw and exception in the component when you encounter an error. Whilst you still have an issue with data moving downstream before all rows have been checked, but you could workaround this by using a transaction or a commit size of 0 to ensure all or nothing at the SQL end. You could do a similar method by using basic SQL transactions, avoiding DTC, and that way you get to issue the BEGIN TRAN and COMMIT TRAN, or of course when the variable says you have bad data, the ROLLBACK. SSIS Nugget: RetainSameConnection property of the OLE DB Connection Manager - (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx)

Another method would be to stage the data between the validation/cleansing and load. Then check the variable and decide if you want to go on a load SQL afterwards. Raw files are the cheapest phstical persistance, they have very little overhead, just the IO cost really.

|||

Appreciate your response

How do I specify Commit size of 0 to a SQL Destination?

|||For the SQL Server Destination it is called MaxInsertCommitSize, look in the properties grid, I could not see it in the component editor UI.|||

Hmmm...yeah but what if all the files get validated right and there will be some existing records which need to be updates and the otehrs need to be inserted...

If no errors found this is how process should end if found completly abort the process...

Struck ova here....arghhhhh

|||Now you're changing the requirements, but still the options can work. So options now available as I see it are the staging idea, validate everything first before you load into SQL in a second Data Flow task. The other option is to use a transaction. Can be quite expensive, but ultimately that is what they are there for, either a package transaction or the retain same connection method as linked above.

Acces to a file that is locked by another process

I am writing a package to process perfmon logs. The issue I have come across is that the perfmon process holds onto the log file and SSIS fails because it wants to exclusive read access. Is there any way of getting SSIS to not take an exclusive read on the file.

I can read the file in notepad fine but not SSIS.

check by changing the isolation level of package by default its serialiazable

Abstract DTS

Hello
I have a DTS package that loads an Sql Server Database with data from MS
Access datasource. The package works fine but I want to be able to change
either the source or destination connection or both (for reuse purposes) and
still have the package run.
Note that regardless of the connection the schemas remain the same.
My problem is that the DTS task seems to use the full path to the db
including server. For instance locally a destination connection table might
be referenced as aaa.dbo.MyTable1 whereas if i try to point the connection
at another instance of the database on a different server the reference for
the connection might be bbb.dbo.MyTable1.
How do i abstract out the server name in eitheer/or both the
source/destination connection? I want to be able to load clients tables with
data by using the same DTS package and just changing the connection
properties.
Thanks
TMHi
Check out:
http://www.sqldts.com/default.aspx?201
Global variables can be passed from the command line.
John
"Toff McGowen" wrote:

> Hello
> I have a DTS package that loads an Sql Server Database with data from MS
> Access datasource. The package works fine but I want to be able to change
> either the source or destination connection or both (for reuse purposes) a
nd
> still have the package run.
> Note that regardless of the connection the schemas remain the same.
> My problem is that the DTS task seems to use the full path to the db
> including server. For instance locally a destination connection table migh
t
> be referenced as aaa.dbo.MyTable1 whereas if i try to point the connection
> at another instance of the database on a different server the reference fo
r
> the connection might be bbb.dbo.MyTable1.
> How do i abstract out the server name in eitheer/or both the
> source/destination connection? I want to be able to load clients tables wi
th
> data by using the same DTS package and just changing the connection
> properties.
> Thanks
> TM
>
>|||Excellent thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:85674B0C-13A0-481D-A7D3-241AD7824971@.microsoft.com...
> Hi
> Check out:
> http://www.sqldts.com/default.aspx?201
> Global variables can be passed from the command line.
> John
> "Toff McGowen" wrote:
>
change
and
might
connection
for
with

about connections and Login

Hey guys,

I have created dtsx packages and i have one master package which would run couple of child packages.

1. I have all on my local machine.I want others who r working with me access these.So what do i need to do?for them to access...

2.what would be the best way in executing these packages.

the way it is set now is i had 3 connections set for each package.

a.Source connection

b.Destination Connection.

c.One more connection(which is used with in the process of execution)

Can i set connections in common for all the packages in one location.

3.i also want to set some logging settings to my master package too.and i want this package to run everyday automatically at a particular time how can this be done.

How can i do that.

please let me know.

sureshv wrote:

1. I have all on my local machine.I want others who r working with me access these.So what do i need to do?for them to access...

What you mean, to edit them? just use VSS or place them in a shared location. To execute them, place them in a shared location and then set up a Job (SQl Agent) to run them.

sureshv wrote:

2.what would be the best way in executing these packages.

Via SQL Agent job would be my preferred method

sureshv wrote:

Can i set connections in common for all the packages in one location.

No, but you can use package configuration instead.

sureshv wrote:

3.i also want to set some logging settings to my master package too.and i want this package to run everyday automatically at a particular time how can this be done.

To schedule them, again, use a SQL Agent Job. For logging just enable package logging.