access datasource in script component

I'm trying to access a group of rows in a data source in my script component (transformation). Currently im using the connection manager in the script component, acquire the connection, and issue a query command to get the data. This will happen on every rows being pass to the script component from the data source. Is there a better way to access the a group of rows from the data source?

It depends what you want to do. Perhaps you could populate an in-memory store of the data to prevent the round-trip each time.

Are you sure that cannot be achieved with LOOKUP component?

-Jamie

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.

accented characters and ñ's

Hi,
I have a problem in a rss script, when i try to create a folder name that
contains accented charactes or ñ's, the folder is created WITHOUT these
characters.
Example:
rs.CreateFolder("�andú", "/", Nothing)
I run the script and then i go to the report manager and the folder created
is "and"
If i create the folder manually with the report manager it works OK, but i
need the script.
Even when i print this name through the console:
Console.WriteLine("�andú") ' Shows: and
This prints fine in a common vb.net console application, someone knows what
the problem ?
Thanks in advanceThis repros when you save the .RSS file as ANSI but when you save the .RSS
file as Unicode, it works.
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Diego Belay" <Diego Belay@.discussions.microsoft.com> wrote in message
news:B63EB827-B112-409E-BFF9-7BFE8B43E23C@.microsoft.com...
> Hi,
> I have a problem in a rss script, when i try to create a folder name that
> contains accented charactes or ñ's, the folder is created WITHOUT these
> characters.
> Example:
> rs.CreateFolder("Ñandú", "/", Nothing)
> I run the script and then i go to the report manager and the folder
> created
> is "and"
> If i create the folder manually with the report manager it works OK, but i
> need the script.
> Even when i print this name through the console:
> Console.WriteLine("Ñandú") ' Shows: and
> This prints fine in a common vb.net console application, someone knows
> what
> the problem ?
> Thanks in advance
>|||it works now, thanks!
"Brian Welcker [MS]" wrote:
> This repros when you save the .RSS file as ANSI but when you save the .RSS
> file as Unicode, it works.
>
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Diego Belay" <Diego Belay@.discussions.microsoft.com> wrote in message
> news:B63EB827-B112-409E-BFF9-7BFE8B43E23C@.microsoft.com...
> > Hi,
> > I have a problem in a rss script, when i try to create a folder name that
> > contains accented charactes or ñ's, the folder is created WITHOUT these
> > characters.
> >
> > Example:
> > rs.CreateFolder("�andú", "/", Nothing)
> >
> > I run the script and then i go to the report manager and the folder
> > created
> > is "and"
> > If i create the folder manually with the report manager it works OK, but i
> > need the script.
> >
> > Even when i print this name through the console:
> >
> > Console.WriteLine("�andú") ' Shows: and
> >
> > This prints fine in a common vb.net console application, someone knows
> > what
> > the problem ?
> >
> > Thanks in advance
> >
> >
>
>

about multiplication in sql

what is the value when running sql script?

select cast(cast(.0000006 as numeric(38,10)) * cast(1.0 as numeric(38,10)) as numeric(38,10))

the result is .0000010000.

why the result is not .0000006000?

It looks to be rounding it upto the nearest decimal place, as the result has one fewer zeros to the right of the decimal point. WHy it would do this I am not sure.|||

The reason the scale of the result has been reduced to 6 is interesting. You have asked SQL Server to multiply two numerics, each with 38 precision and 10 scale.

Conceptually, the result should be a numeric(77, 20). However, this is above SQL Server's maximum precision, so the result would need to be truncated.

An implementation decision was made to sacrifice some scale to gain more digits on the left side of the decimal point, up to a minimum of scale 6. So the numeric(77,20) is actually converted to numeric(38, 6). The same behavior happens for division.

That decision is based off the assumption that digits on the left side of the decimal point are usually more important than the ones on the right side. It can pay off in a different situation:

select cast(1234567890123456789012345678.0 as numeric(38,10)) * cast(10.0 as numeric(38,10))

Here, the extra scale was not actually important, so sacrificing it to get more digits on the left side of the decimal point paid off.

To keep SQL Server from guessing if the scale is needed or not, you need to type your numerics appropriately if possible. In your example:

select cast(cast(.0000006 as numeric(10,10)) * cast(1.0 as numeric(2,1)) as numeric(38,10))

Yields the appropriate results. The result of the multiplication is conceptually numeric(13,11) which is well within our limits.

I hope this helps, and let me know if this work around does not work for you.

-Mat

|||In addition to Matt's comments, please take a look at the "Precision, Scale, and Length" topic in Books Online. The url is mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_da-db_8rc5.htm. This topic explains the formula used to arrive at the resulting precision of arithmetic operations involving two numeric values.|||thanks for your help~|||

Thank you for your reply.

I can find 'tsqlref.chm' but not know how to go '/ts_da-db_8rc5.htm' .Can you tell me how to do.Thanks.

---

I know how to find.^_^

use the tool bar,GO-->URL.... to open 'Jump to URL'

|||If you have SQL Server 2000 Books Online installed then go to Go\Url menu option and paste the url. You can also search for this topic in MSDN.