customize template.ini to upgrade from msde to express edition?

I need to create the script to upgrade the current named instance of sql 2000 desktop engine to sql server 2005 express edition. I am having difficulty preparing the template.ini and would appreciate help.

[Options]
INSTANCENAME=MY_INSTANCE
SQLBROWSERACCOUNT=NT AUTHORITY\NETWORK SERVICE
SECURITYMODE=SQL
SAPWD=music
UPGRADE=SQL_Engine
SQLBROWSERAUTOSTART=1
RSCANINSTALLDEFAULT=0
RSCONFIGURATION=FilesOnly
RSSQLLOCAL=0

I get an error that this file is invalid..Any clues? I am trying to upgrade existing instance of msde 2k to sql express 2005 and uses sql authentication

http://www.microsoft.com/sql/editions/express/upgrade.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx

http://msdn2.microsoft.com/en-us/ms143491.aspx

|||

When a parameter includes space characters, you need quote with " or '. For example, the browser account should be specified as SQLBROWSERACCOUNT="NT AUTHORITY\NETWORK SERVICE " or SQLBROWSERACCOUNT='NT AUTHORITY\NETWORK SERVICE'

Note, this account is localized on some localized operating system. You need to use the localized version if required. In addition, MSDE already has its SP4. So please use MSDE SP4 directly.

The following are two command lines that may be useful to you.

1. Install MSDE SP4.

start /wait setup.exe /qb INSTANCENAME=msde4instance SAPWD="<password>" SECURITYMODE=SQL

2. Upgrade to SQL 2005 SP1.

start /wait setup.exe UPGRADE=SQL_Engine INSTANCENAME=msdesp4instance SAPWD=""<password>" SECURITYMODE=SQL

Customising Dimension Security

HI, I'm having problems specifying customised security in the Advanced tab (allowed members). I'm fairly new to mdx script and am trying to restrict access to a STAFF dimension which contains a loginname member which I want to restrict access to based on username matching the loginname DIM. Have tried the following which didn't work:
[STAFF_DIM].[Login Name].members(MID( USERNAME, INSTR(1,USERNAME, "\") + 1, 128 ) )

I read somewhere that this can only be used on leaf layer members (this is the first of 3 layers), also should I be using the filter function? Any help with trying to setup would be great help.
Before using this expression for dimension security, you may want to do some experimentation first, by creating a simple MDX query including this expression, and verifying that it evaluates to the expected member...

Hope this helps

Custom Script Destination - "wrote 0 rows" Problems

I have created a custom script destination using an article found on TechNet. The script executes fine and completes, but with one small problem. The last package execution log entry states "wrote 0 rows". This is a problem because I use this number to verify that all of the database records have been properly transferred.

I have monitored that database when the script is executing and have verified that the data is being transferred property.

Is there some function that I should be calling with each row prcessed in the custom script destination to "tally" the row count? This way the script component will display the proper row count.

Any suggestions would be appreciated.

You could increment a counter variable in the ProcessInputRow sub and then in the PostExecute sub, assign the value of that variable back to a package-level variable. Then you can, if desired, use an Execute SQL task to insert the contents of the package variable into an audit table. Of course, you could just use the package variable in an expression as well.

I assume you are using this method solely because you are using an ODBC destination? (Just checking)

|||

That is a good work around and I think I will implement something like that if I am unable to find a solution.

I still would like to know the "correct" fix on how to output the row count.

Thanks for the post and I will be sure to post any updates if I find anything out.

--Ryan

|||The way to update the row count in that informational message is to call IncrementPipelinePerfCounter on IDTSComponentMetadata90 with the DTS_PIPELINE_CTR_ROWSWRITTEN flag.|||

Ryan Teribery wrote:

I have created a custom script destination using an article found on TechNet. The script executes fine and completes, but with one small problem. The last package execution log entry states "wrote 0 rows". This is a problem because I use this number to verify that all of the database records have been properly transferred.

I have monitored that database when the script is executing and have verified that the data is being transferred property.

Is there some function that I should be calling with each row prcessed in the custom script destination to "tally" the row count? This way the script component will display the proper row count.

Any suggestions would be appreciated.

A custom script destination will not do this by default. The provided destination components have this functionality embedded within them and you would need to embed the same in the script component.

Use:

Me.ComponentMetadata.FireInformation(...)

in your script component to pass out the information that you require. You should use this code in the PostExecute() method.

Counting the number of rows shouldn't be a problem. Just increment a private variable within the ProcessInputRow() method.

Regards

Jamie

|||

Ryan Teribery wrote:

That is a good work around and I think I will implement something like that if I am unable to find a solution.

it is not a workaround. This is the correct way to do it.

Ryan Teribery wrote:

I still would like to know the "correct" fix on how to output the row count.

The technique that Phil and I have described IS the correct way to do this. Phil's differs slightly because he uses an Execute SQL Task to write a record where mine throws an event which gets caught by your log provider. I think my way is better but then, I'm biased (Phil won't mind me saying that)

|||

The embedded functionality in the provided transforms (e.g. OLE DB Dest adapter) is the use of the IncrementPipelinePerfCounter method I mentioned earlier. Here's how this works: when that method is called by your destination component, the data flow engine tracks the row count your component passed to it (so you still need to track the row count internall). When the data flow finishes its execution (i.e. after the Cleanup phase), the data flow engine will fire DTS_I_ROWS_WRITTEN with the row count on behalf of the component, resulting in the "Destination Component wrote X rows" message. Your component does not need to post its own message. Also note that the perf counters for rows written will be updated as well, which may be good or bad depending on what you want.

As mentioned by others, there are other ways to handle this. Hope this additional info will be useful in helping you decide how you need to implement this.

custom report name, not rdl filename

I m trying to deploy reports using rs.exe & rss script. I would like to provide custom Report Name for each report I am deploying (not same as report rdl filename) for more meaningful listing under Report Manager. Is there a way to implement the same?

Thanks

Can you rename the filename to be more meaningful, and use quotations around the filename?

cheers,

Andrew

|||

Hi Andrew, I guess thats the only good option we have for now...

It would have been nice if there were a property for it (ex. ReportName seperate from ReportFilename)

Thanks

|||In Report Manager go to the properies tab of the report...you can change the name. If you create a linked report, you can name the linked reports to anything you want. This is the name that will appear in your reports for ReportFilename|||

jamvir,

I wanted it to named before deployment. I have 100+ of reports being deployed in 3 environments with bi-weekly release cycle. Naming them each time in Report Manager would be a tedious work.

Custom functions

In the Reporting Services 2005, is it possible to introduce custom functions
in addition to all the built-in VB Script functions? Something similiar to
introducing assemble in T-SQL. Thanks, BingHi,
You can have two types of functions
1. Code in Report properties. which can be used across reports.
2. .Net Assembly so that you can write n no of functions and refer in your
reports.
So if you have a single function to be used across all reports. use first
otherwise second.
Amarnath
"Bing Bing Yu" wrote:
> In the Reporting Services 2005, is it possible to introduce custom functions
> in addition to all the built-in VB Script functions? Something similiar to
> introducing assemble in T-SQL. Thanks, Bing|||Could you post a sample for using the Code in Report? Always wondering what
to do with it since it really looks like a free-entry box. Thanks!!
"Amarnath" wrote:
> Hi,
> You can have two types of functions
> 1. Code in Report properties. which can be used across reports.
> 2. .Net Assembly so that you can write n no of functions and refer in your
> reports.
> So if you have a single function to be used across all reports. use first
> otherwise second.
> Amarnath
> "Bing Bing Yu" wrote:
> > In the Reporting Services 2005, is it possible to introduce custom functions
> > in addition to all the built-in VB Script functions? Something similiar to
> > introducing assemble in T-SQL. Thanks, Bing

Custom error msg breaks my error handler

Hi,

I added this line of code to my error handler script, in red.

User:criptError is a package-level string variable

User:criptError has a value assigned to it when another script encounters an error condition (which I define)

I added "User:criptError" as a read-only variable to the error handler script task.

Public Sub Main()

Dim messages As Collections.ArrayList

Try

messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)

Catch ex As Exception

messages = New Collections.ArrayList()

End Try

messages.Add(Dts.Variables("SourceName").Value.ToString)

messages.Add(Dts.Variables("ErrorDescription").Value.ToString())

messages.Add(Dts.Variables("scriptError").Value.ToString)

Dts.Variables("errorMessages").Value = messages

Dts.TaskResult = Dts.Results.Success

End Sub

Now, when I run the package, and an it encounters an error, it task just hangs, that is, it stays yellow instead of turning red... if I remove the line in red above, it works ok again.

Why would this line cause a problem?

Thanks

Make sure you are unlocking the scriptError variable in the first task. It might help if you post the script from that task.|||

Hi,

What do you mean by "unlocking the scriptError variable"?

Thanks

|||Dts.Variables.Unlock()|||

I will try that. Why is unlocking necessary? I've never run into this before.

Thanks

|||If the first task is raising an error, the error handling task can be fired before the variable is unlocked in the first task. Make sure that you unlock it in the error handler in the first script.|||

This is the part of my code that seems to be causing the problem. I am trying to set the "scriptError" variable to the error message below. As you can see, I am setting the Dts.TaskResult to "failure" BEFORE assigning the value to my variable. The "failure" causes execution to go to my Error Handler script task. That's where it hangs.

Perhaps I should put the the "failure" AFTER the variable assignment.

I don't understand all this "unlocking" stuff quite yet. What do you mean by "unlock it in the error handler"? Perhaps that doesn't apply here, I don't know.

Thanks

Else

Dts.TaskResult = Dts.Results.Failure

Dts.Variables("scriptError").Value = "The extracts in " & CStr(Dts.Variables("Folder").Value) & " are not current! Data NOT loaded."

End If

|||Adding

Dts.Variables.Unlock()

right after the variable is written to (but not before) works!

Custom error msg breaks my error handler

Hi,

I added this line of code to my error handler script, in red.

User:criptError is a package-level string variable

User:criptError has a value assigned to it when another script encounters an error condition (which I define)

I added "User:criptError" as a read-only variable to the error handler script task.

Public Sub Main()

Dim messages As Collections.ArrayList

Try

messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)

Catch ex As Exception

messages = New Collections.ArrayList()

End Try

messages.Add(Dts.Variables("SourceName").Value.ToString)

messages.Add(Dts.Variables("ErrorDescription").Value.ToString())

messages.Add(Dts.Variables("scriptError").Value.ToString)

Dts.Variables("errorMessages").Value = messages

Dts.TaskResult = Dts.Results.Success

End Sub

Now, when I run the package, and an it encounters an error, it task just hangs, that is, it stays yellow instead of turning red... if I remove the line in red above, it works ok again.

Why would this line cause a problem?

Thanks

Make sure you are unlocking the scriptError variable in the first task. It might help if you post the script from that task.|||

Hi,

What do you mean by "unlocking the scriptError variable"?

Thanks

|||Dts.Variables.Unlock()|||

I will try that. Why is unlocking necessary? I've never run into this before.

Thanks

|||If the first task is raising an error, the error handling task can be fired before the variable is unlocked in the first task. Make sure that you unlock it in the error handler in the first script.|||

This is the part of my code that seems to be causing the problem. I am trying to set the "scriptError" variable to the error message below. As you can see, I am setting the Dts.TaskResult to "failure" BEFORE assigning the value to my variable. The "failure" causes execution to go to my Error Handler script task. That's where it hangs.

Perhaps I should put the the "failure" AFTER the variable assignment.

I don't understand all this "unlocking" stuff quite yet. What do you mean by "unlock it in the error handler"? Perhaps that doesn't apply here, I don't know.

Thanks

Else

Dts.TaskResult = Dts.Results.Failure

Dts.Variables("scriptError").Value = "The extracts in " & CStr(Dts.Variables("Folder").Value) & " are not current! Data NOT loaded."

End If

|||Adding

Dts.Variables.Unlock()

right after the variable is written to (but not before) works!

Custom error msg breaks my error handler

Hi,

I added this line of code to my error handler script, in red.

User:criptError is a package-level string variable

User:criptError has a value assigned to it when another script encounters an error condition (which I define)

I added "User:criptError" as a read-only variable to the error handler script task.

Public Sub Main()

Dim messages As Collections.ArrayList

Try

messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)

Catch ex As Exception

messages = New Collections.ArrayList()

End Try

messages.Add(Dts.Variables("SourceName").Value.ToString)

messages.Add(Dts.Variables("ErrorDescription").Value.ToString())

messages.Add(Dts.Variables("scriptError").Value.ToString)

Dts.Variables("errorMessages").Value = messages

Dts.TaskResult = Dts.Results.Success

End Sub

Now, when I run the package, and an it encounters an error, it task just hangs, that is, it stays yellow instead of turning red... if I remove the line in red above, it works ok again.

Why would this line cause a problem?

Thanks

Make sure you are unlocking the scriptError variable in the first task. It might help if you post the script from that task.|||

Hi,

What do you mean by "unlocking the scriptError variable"?

Thanks

|||Dts.Variables.Unlock()|||

I will try that. Why is unlocking necessary? I've never run into this before.

Thanks

|||If the first task is raising an error, the error handling task can be fired before the variable is unlocked in the first task. Make sure that you unlock it in the error handler in the first script.|||

This is the part of my code that seems to be causing the problem. I am trying to set the "scriptError" variable to the error message below. As you can see, I am setting the Dts.TaskResult to "failure" BEFORE assigning the value to my variable. The "failure" causes execution to go to my Error Handler script task. That's where it hangs.

Perhaps I should put the the "failure" AFTER the variable assignment.

I don't understand all this "unlocking" stuff quite yet. What do you mean by "unlock it in the error handler"? Perhaps that doesn't apply here, I don't know.

Thanks

Else

Dts.TaskResult = Dts.Results.Failure

Dts.Variables("scriptError").Value = "The extracts in " & CStr(Dts.Variables("Folder").Value) & " are not current! Data NOT loaded."

End If

|||Adding

Dts.Variables.Unlock()

right after the variable is written to (but not before) works!

Custom Class for Function

I have several fuctions that I would like to share between my different script tasks in my SSIS package. I assume I do this by creating a custom class, but I cant quite figure it out. Can someone please point me in the right direction?

Thank You!!

Check Books Online under Programming Integration Services.|||

I didn't see any instruction for how to do this in the Books Online. If it is there, I can't find it. Please help, I would like to figure out how to do this.

Thank You.

|||

AspUser123 wrote:

I didn't see any instruction for how to do this in the Books Online. If it is there, I can't find it. Please help, I would like to figure out how to do this.

Thank You.

Assuming you know how to create an assembly, this explains how you can reference it from your SSIS scripts.

http://msdn2.microsoft.com/en-US/library/ms136007.aspx

Custom Class for Function

I have several fuctions that I would like to share between my different script tasks in my SSIS package. I assume I do this by creating a custom class, but I cant quite figure it out. Can someone please point me in the right direction?

Thank You!!

Check Books Online under Programming Integration Services.|||

I didn't see any instruction for how to do this in the Books Online. If it is there, I can't find it. Please help, I would like to figure out how to do this.

Thank You.

|||

AspUser123 wrote:

I didn't see any instruction for how to do this in the Books Online. If it is there, I can't find it. Please help, I would like to figure out how to do this.

Thank You.

Assuming you know how to create an assembly, this explains how you can reference it from your SSIS scripts.

http://msdn2.microsoft.com/en-US/library/ms136007.aspx