Deployment 'utility' script using sqlcmd

Hi all,

I'm looking at creating a sample utility script that will invoking

scripts to deploy some SQL code. For example, a utlity script that will

run a SQL script, and on successful completion, execute the next script.

Having not used SQLCMD at all before, and being very new to SQL2005

(< 1 month) please guide me if there is a better way of invoking

this... For example, a way of avoiding the xp_cmdshell invocation!

The following code invokes a script, but I'm trying to find a way of

getting a return code back from sqlcmd, so I can progress and do the

next, or fail if the return code <> 0 (success).

[code]

--Process to create DB, Tables, and Stored Procedures

set nocount on

DECLARE

@.Error int,

@.ExecCommand varchar(512),

@.FullFilePath varchar(255)

--create the database

BEGIN TRY

SET @.FullFilePath =

'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF

Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB

TescoDNF_SSISPackageManager.sql'

SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '

SELECT @.FullFilePath

UNION

SELECT @.ExecCommand

EXEC (@.ExecCommand)

SELECT @.@.ERROR

SELECT @.Error

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

GOTO ErrorAbort

END CATCH
ErrorAbort:

[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set

of SQL scripts to, for example, a server. For example, some pseudo-code

Create Database

If Error abort

Create Table1

If Error abort

Create Table2

If Error Abort

Create Stored Procedure1

If Error Abort

ELSE Complete and report success

I do agree that this is something that could be (better) done in a

"proper" coding language like .Net, c# etc. but (a) it's just a simple

utility script (b) it teaches me moore about usage of SQLCMD and (c) I

do not have any skill in any normal programming language, hence I was

planning to write a quick deployment utility with a script.

The idea might be something as ugly as a table structure that has a

list of scripts registered in it, with some sequence logic - like

creating parent tables before children tables - and then a cursor (or a

better method if I can find it) that fetches a sqlcmd filename

execution command, executes it, and on success fetches the next one

based on the sequence logic.

I can probably do all of that in about 4 hours in T-SQL, if I can find

a way to confirm the successful execution of the previous command....|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files

and execute them, and not merge them all into a single large script.

So, I want utility script to do this:

Run external sql script

On failure abort, on Success

Run external sql script

On Failure abort, On Success

...

You being to see why I referred to a cursor?

The point is that the utility script wouldn't contain any of the client

SQL commands - it would fetch them by referring to the table, and

fetching the path to the SQL file, and building a SQLCMD to execute

that script

I guess, as you say, I could add a generic update ##SQLScriptTracker

table, then check it on the new execution, or abort. I had hoped for a

neater solution - i.e. SQLCMD being able to return a returncode that it

gets from a SQL file it ran....|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as

well, since with the batch file I have to add a file each time.

Thanks|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).

Deployment 'utility' script using sqlcmd

Hi all,
I'm looking at creating a sample utility script that will invoking scripts to deploy some SQL code. For example, a utlity script that will run a SQL script, and on successful completion, execute the next script.
Having not used SQLCMD at all before, and being very new to SQL2005 (< 1 month) please guide me if there is a better way of invoking this... For example, a way of avoiding the xp_cmdshell invocation!
The following code invokes a script, but I'm trying to find a way of getting a return code back from sqlcmd, so I can progress and do the next, or fail if the return code <> 0 (success).
[code]
--Process to create DB, Tables, and Stored Procedures
set nocount on
DECLARE
@.Error int,
@.ExecCommand varchar(512),
@.FullFilePath varchar(255)
--create the database
BEGIN TRY
SET @.FullFilePath = 'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB TescoDNF_SSISPackageManager.sql'
SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '
SELECT @.FullFilePath
UNION
SELECT @.ExecCommand
EXEC (@.ExecCommand)
SELECT @.@.ERROR
SELECT @.Error
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GOTO ErrorAbort
END CATCH

ErrorAbort:
[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set of SQL scripts to, for example, a server. For example, some pseudo-code
Create Database
If Error abort
Create Table1
If Error abort
Create Table2
If Error Abort
Create Stored Procedure1
If Error Abort
ELSE Complete and report success
I do agree that this is something that could be (better) done in a "proper" coding language like .Net, c# etc. but (a) it's just a simple utility script (b) it teaches me moore about usage of SQLCMD and (c) I do not have any skill in any normal programming language, hence I was planning to write a quick deployment utility with a script.
The idea might be something as ugly as a table structure that has a list of scripts registered in it, with some sequence logic - like creating parent tables before children tables - and then a cursor (or a better method if I can find it) that fetches a sqlcmd filename execution command, executes it, and on success fetches the next one based on the sequence logic.
I can probably do all of that in about 4 hours in T-SQL, if I can find a way to confirm the successful execution of the previous command....
|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files and execute them, and not merge them all into a single large script.
So, I want utility script to do this:
Run external sql script
On failure abort, on Success
Run external sql script
On Failure abort, On Success
...
You being to see why I referred to a cursor?
The point is that the utility script wouldn't contain any of the client SQL commands - it would fetch them by referring to the table, and fetching the path to the SQL file, and building a SQLCMD to execute that script
I guess, as you say, I could add a generic update ##SQLScriptTracker table, then check it on the new execution, or abort. I had hoped for a neater solution - i.e. SQLCMD being able to return a returncode that it gets from a SQL file it ran....
|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as well, since with the batch file I have to add a file each time.
Thanks
|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).

sql

Deployment Utility or Import Package?

In reference to the question raised in this thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1460591&SiteID=1

Since I'm not able to create a deployment utility, when a config file is shared among multiple packages and also I cannot get the permission from Sys Admins to use Env. Variables I'm struck.

Now I'm thinking of importing the package to Sql Server from the file system. Is there any caveats in this approach? especially regarding the config files?

[edit]
Also, do I need any special permissions to view the Integeration Services node in Management Studio? We are using Integerated Authentication, neither do I'm able to run sp_start_job sp in the msdb database.
[/edit]

Thanks

You might want to look at DTUTIL for deployment. It's a little more work than just building the project in SSIS, but it is more flexible.

With the config files, you just need to be sure they are accessible from the package once it is in SQL Server.

Deployment Utility error

I am getting the following error when trying to run a deployment utility. The server only has .net framework 2.0 and sql server 2005 (rtm) installed.
If it's any help, this server was recently upgraded from the june ctp to rtm.
This is a production server, so a solution involving as little disturbance to it's current state would be preferrable.
Thanks in advance for any suggestions.
-Jeremy
TITLE: Package Installation Wizard
Unexpected error occurred.
ADDITIONAL INFORMATION:
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. (Microsoft.SqlServer.ManagedDTS)
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. (Microsoft.SqlServer.ManagedDTS)
BUTTONS:
OK

Looks like the SSIS is not corectly installed on the machine. Can you uninstall SQL Server and reinstall it?

HTH,
Ovidiu

|||unfortunately, no. I'm not even sure that would have any affect, as we just installed it an hour before this error, and I saw no point in the install process where it could have gone awry.|||

We're having the exact same issue with a WinForm app deployed via ClickOnce.

What's the deal here?

Deployment Utility error

I am getting the following error when trying to run a deployment utility. The server only has .net framework 2.0 and sql server 2005 (rtm) installed.
If it's any help, this server was recently upgraded from the june ctp to rtm.
This is a production server, so a solution involving as little disturbance to it's current state would be preferrable.
Thanks in advance for any suggestions.
-Jeremy
TITLE: Package Installation Wizard
Unexpected error occurred.
ADDITIONAL INFORMATION:
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. (Microsoft.SqlServer.ManagedDTS)
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. (Microsoft.SqlServer.ManagedDTS)
BUTTONS:
OK

Looks like the SSIS is not corectly installed on the machine. Can you uninstall SQL Server and reinstall it?

HTH,
Ovidiu

|||unfortunately, no. I'm not even sure that would have any affect, as we just installed it an hour before this error, and I saw no point in the install process where it could have gone awry.|||

We're having the exact same issue with a WinForm app deployed via ClickOnce.

What's the deal here?

Deploying SSIS Package

This maybe simple question but I can not seem to figure it out. If you use the deployment utility to deploy your package how do you specify a different SSIS directory to install it to. For instance when I'm in SSIS under stored packages > MSDB > I created a folder called package 1. I want to install to that folder. The only way I can do it is to manually import the package from SSIS.Try File->"Save Copy of xxxx as" when in a package.

That's another option for ya.|||

I guess it is a known limitation:

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

Deploying report with RS utility with different datasource folder

How does one specify a different data source folder path when deploying a
report to a report server (2005) using RS scripting? I started with the
PublishSampleReport.rss script sample from microsoft and need to publish a
report (i.e. rs.CreateReport... ) to the report server that references the
shared data source in a different folder than the default "/Data Sources"
(i.e. /ApplicationName/ReleaseName/Data Sources). Any ideas? You can
change in Visual Studio 2005 the TargetDataSourceFolder in the project
properties for deployment, but how do you do it in script?I think something like this should do the trick (it's worked for me):
Private Sub SetItemDataSources()
Dim Item As String = "/My Reports/MyReport"
Dim DataSources(0) As Microsoft.SqlServer.ReportingServices2005.DataSource
DataSources(0) = New Microsoft.SqlServer.ReportingServices2005.DataSource
DataSources(0).Name = "MyDataSource"
Dim Item1 As Microsoft.SqlServer.ReportingServices2005.DataSourceReference
= New Microsoft.SqlServer.ReportingServices2005.DataSourceReference
Item1.Reference = "/Data Sources/MyDataSource"
DataSources(0).Item = Item1
RS.SetItemDataSources(Item, DataSources)
End Sub
I find it pretty useful to use the "script" option in in SQL Server
Management Studio (connected to Reporting Services rather than the Database
Engine) when I want to see how to make a particular change using RS. That's
how I got the above example.
Regards.
"vnapoli" wrote:
> How does one specify a different data source folder path when deploying a
> report to a report server (2005) using RS scripting? I started with the
> PublishSampleReport.rss script sample from microsoft and need to publish a
> report (i.e. rs.CreateReport... ) to the report server that references the
> shared data source in a different folder than the default "/Data Sources"
> (i.e. /ApplicationName/ReleaseName/Data Sources). Any ideas? You can
> change in Visual Studio 2005 the TargetDataSourceFolder in the project
> properties for deployment, but how do you do it in script?
>
>

deploying report models

Hi Friends
I've quick question ,we have a .net application and we want develop a report builder utility for our end users.I found sql server's built in Report builder is very useful so i created a report model which is easy enough for our end users to select different data items to create their own reports.
My questions is how can i deploy this from our production machine to client site ?what are the requirements etc.,

I have looked web for any references but in vain.
Any help with this regard is much appreciated.
Thank you

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Note that a deployed model contains the DSV definition it is based on inside the smdl document itself. Until you deploy it, the DSV is *not* “plugged” into the smdl file, however….So don’t just create a model using the Model Designer, and then expect to be able to take the resulting smdl and bring it to the client site. You must first deploy it in order to get Visual Studio to merge the DSV into a complete smdl document.

|||Thank you very much Russell.
I understand export and upload options you mentioned but i did not understand your 2nd point. ie

>>You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

so does it mean that uploading SMDL file at the site will not be enough ,will it?
Thanks for your help on this. I really appreciate if you have some sample code or some links abt these deploying issues.

I looked MS help but they just talk abt deploy option from solution explorer and they dont talk abt deploying on completly different machine.|||As long as you've also uploaded the data source that the DSV in your model uses, just upload the model (smdl) and you'll be fine.|||Thanks Russell.That makes sense.

one more question on this one ,i'll upload data source also thats no problem but how can i change connection string easily. as you know sql server names can be different. we've no of client sites where we need to deploy our reporting models. our database name will be same but as you know server names can be different with each site.

can connection string be taken from a config file ?
Thanks for your help|||You can set the connection string for the model manually in Report Manager (or Management Studio). Bring up the model properties and go to the datasource tabs. If you want to do it programmatically / through script, you will want to call SetItemDataSources (http://msdn2.microsoft.com/en-us/library/ms160404(en-US,SQL.90).aspx).|||Thanks Brian
Will give it a try mate|||

Russell Christopher - msft wrote:

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Russel, Why cant i find "Export " option on report manager page ? or am i missing something ?
anyway i managaed to find .smdl file on my machine (where i created the report model) and on production server i used "upload File" to import my report model and it worked fine and users could create reports fine.
but today i changed report model as users needed few more entities and i tried to deploy again the updated report model with same steps as above. But all existing reports created prior to this stopped working!! i get runtime errors.. but users can create new ones though.
am i doing anything wrong ?
Thanks for your help again. am still trying to get my head around on deploy issues.|||

"Export" is actually labeled "Edit" in Report Manager. It's a very small hyperlink on the General property tab of your report.

I'm not exactly sure why the previously published reports stopped working. Did they work after you reset the data source for each report?

|||Thank you very much Russell
finally i successfully did "export" at client machine.it worked nicely.Thanks you so much for you help on this .
BTW the other issue (ie, previous reports not working) i'll need to do little more testing and i'll see what happens if change report model and re-deploy again on the same client machine.|||

Russell,

I'd like some more information on your point 2, using the CreateModel() method. I know how to reference the ReportingServices2005 web service, and then instantiate it's class. Then I can do (for instance) rs.CreateModel(), but how do I load the smdl into an array of bytes? I've not done that. Do you have an online resource that can explain this process?

And I know that you can use Report Viewer in Visual Studio to avoid sending a user to a report server to view reports. Is this also an option with ad hoc reports? If I use the BI to create a Report Model, and then export it, can I now direct our users to a web page/site that I create (that I can have more control over) in order to avoid sending them to the Report Server?

Thanks in advance.

Marvin Hoffman

|||

FileStream stream = File.OpenRead(@."c:\MyReport.smdl");
definition = new Byte[stream.Length];
stream.Read(definition, 0, (int) stream.Length);
stream.Close();
//Now use CreateModel with "defintion" variable

|||

Hi,

This is what i came up with while searching for a way to create model programaticaly.

Uploading a Report Model

Dim DataSourceName As String = "/Adventure Works"

Dim ModelName As String = "/Adventure Works Model"

Dim MyServer As String = "MyReportServer"

Dim rs As New ReportingService2005

rs.Url = "http://" + MyServer + "/reportserver/reportservice2005.asmx"

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim props = Nothing

Dim modelPath As String = "./Adventure Works Model.smdl"

Dim fs As FileStream

fs = File.OpenRead(modelPath)

Dim modelDefinition As Byte() = New [Byte](fs.Length) {}

fs.Read(modelDefinition, 0, CInt(fs.Length))

fs.Close()

Try

rs.CreateModel("Adventure Works Model", "/", modelDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Creating Data Source

Dim dsDefinition As New DataSourceDefinition

dsDefinition.Extension = "OLEDB-MD"

dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated

dsDefinition.ConnectString = "data source=" + MyServer + ";initial catalog=Adventure Works DW"

dsDefinition.ImpersonateUserSpecified = True

dsDefinition.Enabled = True

dsDefinition.EnabledSpecified = True

Try

rs.CreateDataSource("Adventure Works", "/", False, dsDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Associating Report Model with Data Source

Dim ds() As DataSource

ds = rs.GetItemDataSources(ModelName)

Dim dsref As New DataSourceReference

dsref.Reference = DataSourceName

ds(0).Item = dsref

Try

rs.SetItemDataSources("/Adventure Works Model", ds)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

deploying report models

Hi Friends
I've quick question ,we have a .net application and we want develop a report builder utility for our end users.I found sql server's built in Report builder is very useful so i created a report model which is easy enough for our end users to select different data items to create their own reports.
My questions is how can i deploy this from our production machine to client site ?what are the requirements etc.,

I have looked web for any references but in vain.
Any help with this regard is much appreciated.
Thank you

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Note that a deployed model contains the DSV definition it is based on inside the smdl document itself. Until you deploy it, the DSV is *not* “plugged” into the smdl file, however….So don’t just create a model using the Model Designer, and then expect to be able to take the resulting smdl and bring it to the client site. You must first deploy it in order to get Visual Studio to merge the DSV into a complete smdl document.

|||Thank you very much Russell.
I understand export and upload options you mentioned but i did not understand your 2nd point. ie

>>You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

so does it mean that uploading SMDL file at the site will not be enough ,will it?
Thanks for your help on this. I really appreciate if you have some sample code or some links abt these deploying issues.

I looked MS help but they just talk abt deploy option from solution explorer and they dont talk abt deploying on completly different machine.|||As long as you've also uploaded the data source that the DSV in your model uses, just upload the model (smdl) and you'll be fine.|||Thanks Russell.That makes sense.

one more question on this one ,i'll upload data source also thats no problem but how can i change connection string easily. as you know sql server names can be different. we've no of client sites where we need to deploy our reporting models. our database name will be same but as you know server names can be different with each site.

can connection string be taken from a config file ?
Thanks for your help|||You can set the connection string for the model manually in Report Manager (or Management Studio). Bring up the model properties and go to the datasource tabs. If you want to do it programmatically / through script, you will want to call SetItemDataSources (http://msdn2.microsoft.com/en-us/library/ms160404(en-US,SQL.90).aspx).|||Thanks Brian
Will give it a try mate|||

Russell Christopher - msft wrote:

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Russel, Why cant i find "Export " option on report manager page ? or am i missing something ?
anyway i managaed to find .smdl file on my machine (where i created the report model) and on production server i used "upload File" to import my report model and it worked fine and users could create reports fine.
but today i changed report model as users needed few more entities and i tried to deploy again the updated report model with same steps as above. But all existing reports created prior to this stopped working!! i get runtime errors.. but users can create new ones though.
am i doing anything wrong ?
Thanks for your help again. am still trying to get my head around on deploy issues.|||

"Export" is actually labeled "Edit" in Report Manager. It's a very small hyperlink on the General property tab of your report.

I'm not exactly sure why the previously published reports stopped working. Did they work after you reset the data source for each report?

|||Thank you very much Russell
finally i successfully did "export" at client machine.it worked nicely.Thanks you so much for you help on this .
BTW the other issue (ie, previous reports not working) i'll need to do little more testing and i'll see what happens if change report model and re-deploy again on the same client machine.|||

Russell,

I'd like some more information on your point 2, using the CreateModel() method. I know how to reference the ReportingServices2005 web service, and then instantiate it's class. Then I can do (for instance) rs.CreateModel(), but how do I load the smdl into an array of bytes? I've not done that. Do you have an online resource that can explain this process?

And I know that you can use Report Viewer in Visual Studio to avoid sending a user to a report server to view reports. Is this also an option with ad hoc reports? If I use the BI to create a Report Model, and then export it, can I now direct our users to a web page/site that I create (that I can have more control over) in order to avoid sending them to the Report Server?

Thanks in advance.

Marvin Hoffman

|||

FileStream stream = File.OpenRead(@."c:\MyReport.smdl");
definition = new Byte[stream.Length];
stream.Read(definition, 0, (int) stream.Length);
stream.Close();
//Now use CreateModel with "defintion" variable

|||

Hi,

This is what i came up with while searching for a way to create model programaticaly.

Uploading a Report Model

Dim DataSourceName As String = "/Adventure Works"

Dim ModelName As String = "/Adventure Works Model"

Dim MyServer As String = "MyReportServer"

Dim rs As New ReportingService2005

rs.Url = "http://" + MyServer + "/reportserver/reportservice2005.asmx"

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim props = Nothing

Dim modelPath As String = "./Adventure Works Model.smdl"

Dim fs As FileStream

fs = File.OpenRead(modelPath)

Dim modelDefinition As Byte() = New [Byte](fs.Length) {}

fs.Read(modelDefinition, 0, CInt(fs.Length))

fs.Close()

Try

rs.CreateModel("Adventure Works Model", "/", modelDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Creating Data Source

Dim dsDefinition As New DataSourceDefinition

dsDefinition.Extension = "OLEDB-MD"

dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated

dsDefinition.ConnectString = "data source=" + MyServer + ";initial catalog=Adventure Works DW"

dsDefinition.ImpersonateUserSpecified = True

dsDefinition.Enabled = True

dsDefinition.EnabledSpecified = True

Try

rs.CreateDataSource("Adventure Works", "/", False, dsDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Associating Report Model with Data Source

Dim ds() As DataSource

ds = rs.GetItemDataSources(ModelName)

Dim dsref As New DataSourceReference

dsref.Reference = DataSourceName

ds(0).Item = dsref

Try

rs.SetItemDataSources("/Adventure Works Model", ds)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

deploying report models

Hi Friends
I've quick question ,we have a .net application and we want develop a report builder utility for our end users.I found sql server's built in Report builder is very useful so i created a report model which is easy enough for our end users to select different data items to create their own reports.
My questions is how can i deploy this from our production machine to client site ?what are the requirements etc.,

I have looked web for any references but in vain.
Any help with this regard is much appreciated.
Thank you

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Note that a deployed model contains the DSV definition it is based on inside the smdl document itself. Until you deploy it, the DSV is *not* “plugged” into the smdl file, however….So don’t just create a model using the Model Designer, and then expect to be able to take the resulting smdl and bring it to the client site. You must first deploy it in order to get Visual Studio to merge the DSV into a complete smdl document.

|||Thank you very much Russell.
I understand export and upload options you mentioned but i did not understand your 2nd point. ie

>>You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

so does it mean that uploading SMDL file at the site will not be enough ,will it?
Thanks for your help on this. I really appreciate if you have some sample code or some links abt these deploying issues.

I looked MS help but they just talk abt deploy option from solution explorer and they dont talk abt deploying on completly different machine.|||As long as you've also uploaded the data source that the DSV in your model uses, just upload the model (smdl) and you'll be fine.|||Thanks Russell.That makes sense.

one more question on this one ,i'll upload data source also thats no problem but how can i change connection string easily. as you know sql server names can be different. we've no of client sites where we need to deploy our reporting models. our database name will be same but as you know server names can be different with each site.

can connection string be taken from a config file ?
Thanks for your help|||You can set the connection string for the model manually in Report Manager (or Management Studio). Bring up the model properties and go to the datasource tabs. If you want to do it programmatically / through script, you will want to call SetItemDataSources (http://msdn2.microsoft.com/en-us/library/ms160404(en-US,SQL.90).aspx).|||Thanks Brian
Will give it a try mate|||

Russell Christopher - msft wrote:

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Russel, Why cant i find "Export " option on report manager page ? or am i missing something ?
anyway i managaed to find .smdl file on my machine (where i created the report model) and on production server i used "upload File" to import my report model and it worked fine and users could create reports fine.
but today i changed report model as users needed few more entities and i tried to deploy again the updated report model with same steps as above. But all existing reports created prior to this stopped working!! i get runtime errors.. but users can create new ones though.
am i doing anything wrong ?
Thanks for your help again. am still trying to get my head around on deploy issues.|||

"Export" is actually labeled "Edit" in Report Manager. It's a very small hyperlink on the General property tab of your report.

I'm not exactly sure why the previously published reports stopped working. Did they work after you reset the data source for each report?

|||Thank you very much Russell
finally i successfully did "export" at client machine.it worked nicely.Thanks you so much for you help on this .
BTW the other issue (ie, previous reports not working) i'll need to do little more testing and i'll see what happens if change report model and re-deploy again on the same client machine.|||

Russell,

I'd like some more information on your point 2, using the CreateModel() method. I know how to reference the ReportingServices2005 web service, and then instantiate it's class. Then I can do (for instance) rs.CreateModel(), but how do I load the smdl into an array of bytes? I've not done that. Do you have an online resource that can explain this process?

And I know that you can use Report Viewer in Visual Studio to avoid sending a user to a report server to view reports. Is this also an option with ad hoc reports? If I use the BI to create a Report Model, and then export it, can I now direct our users to a web page/site that I create (that I can have more control over) in order to avoid sending them to the Report Server?

Thanks in advance.

Marvin Hoffman

|||

FileStream stream = File.OpenRead(@."c:\MyReport.smdl");
definition = new Byte[stream.Length];
stream.Read(definition, 0, (int) stream.Length);
stream.Close();
//Now use CreateModel with "defintion" variable

|||

Hi,

This is what i came up with while searching for a way to create model programaticaly.

Uploading a Report Model

Dim DataSourceName As String = "/Adventure Works"

Dim ModelName As String = "/Adventure Works Model"

Dim MyServer As String = "MyReportServer"

Dim rs As New ReportingService2005

rs.Url = "http://" + MyServer + "/reportserver/reportservice2005.asmx"

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim props = Nothing

Dim modelPath As String = "./Adventure Works Model.smdl"

Dim fs As FileStream

fs = File.OpenRead(modelPath)

Dim modelDefinition As Byte() = New [Byte](fs.Length) {}

fs.Read(modelDefinition, 0, CInt(fs.Length))

fs.Close()

Try

rs.CreateModel("Adventure Works Model", "/", modelDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Creating Data Source

Dim dsDefinition As New DataSourceDefinition

dsDefinition.Extension = "OLEDB-MD"

dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated

dsDefinition.ConnectString = "data source=" + MyServer + ";initial catalog=Adventure Works DW"

dsDefinition.ImpersonateUserSpecified = True

dsDefinition.Enabled = True

dsDefinition.EnabledSpecified = True

Try

rs.CreateDataSource("Adventure Works", "/", False, dsDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Associating Report Model with Data Source

Dim ds() As DataSource

ds = rs.GetItemDataSources(ModelName)

Dim dsref As New DataSourceReference

dsref.Reference = DataSourceName

ds(0).Item = dsref

Try

rs.SetItemDataSources("/Adventure Works Model", ds)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

deploying report models

Hi Friends
I've quick question ,we have a .net application and we want develop a report builder utility for our end users.I found sql server's built in Report builder is very useful so i created a report model which is easy enough for our end users to select different data items to create their own reports.
My questions is how can i deploy this from our production machine to client site ?what are the requirements etc.,

I have looked web for any references but in vain.
Any help with this regard is much appreciated.
Thank you

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Note that a deployed model contains the DSV definition it is based on inside the smdl document itself. Until you deploy it, the DSV is *not* “plugged” into the smdl file, however….So don’t just create a model using the Model Designer, and then expect to be able to take the resulting smdl and bring it to the client site. You must first deploy it in order to get Visual Studio to merge the DSV into a complete smdl document.

|||Thank you very much Russell.
I understand export and upload options you mentioned but i did not understand your 2nd point. ie

>>You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

so does it mean that uploading SMDL file at the site will not be enough ,will it?
Thanks for your help on this. I really appreciate if you have some sample code or some links abt these deploying issues.

I looked MS help but they just talk abt deploy option from solution explorer and they dont talk abt deploying on completly different machine.|||As long as you've also uploaded the data source that the DSV in your model uses, just upload the model (smdl) and you'll be fine.|||Thanks Russell.That makes sense.

one more question on this one ,i'll upload data source also thats no problem but how can i change connection string easily. as you know sql server names can be different. we've no of client sites where we need to deploy our reporting models. our database name will be same but as you know server names can be different with each site.

can connection string be taken from a config file ?
Thanks for your help|||You can set the connection string for the model manually in Report Manager (or Management Studio). Bring up the model properties and go to the datasource tabs. If you want to do it programmatically / through script, you will want to call SetItemDataSources (http://msdn2.microsoft.com/en-us/library/ms160404(en-US,SQL.90).aspx).|||Thanks Brian
Will give it a try mate|||

Russell Christopher - msft wrote:

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Russel, Why cant i find "Export " option on report manager page ? or am i missing something ?
anyway i managaed to find .smdl file on my machine (where i created the report model) and on production server i used "upload File" to import my report model and it worked fine and users could create reports fine.
but today i changed report model as users needed few more entities and i tried to deploy again the updated report model with same steps as above. But all existing reports created prior to this stopped working!! i get runtime errors.. but users can create new ones though.
am i doing anything wrong ?
Thanks for your help again. am still trying to get my head around on deploy issues.|||

"Export" is actually labeled "Edit" in Report Manager. It's a very small hyperlink on the General property tab of your report.

I'm not exactly sure why the previously published reports stopped working. Did they work after you reset the data source for each report?

|||Thank you very much Russell
finally i successfully did "export" at client machine.it worked nicely.Thanks you so much for you help on this .
BTW the other issue (ie, previous reports not working) i'll need to do little more testing and i'll see what happens if change report model and re-deploy again on the same client machine.|||

Russell,

I'd like some more information on your point 2, using the CreateModel() method. I know how to reference the ReportingServices2005 web service, and then instantiate it's class. Then I can do (for instance) rs.CreateModel(), but how do I load the smdl into an array of bytes? I've not done that. Do you have an online resource that can explain this process?

And I know that you can use Report Viewer in Visual Studio to avoid sending a user to a report server to view reports. Is this also an option with ad hoc reports? If I use the BI to create a Report Model, and then export it, can I now direct our users to a web page/site that I create (that I can have more control over) in order to avoid sending them to the Report Server?

Thanks in advance.

Marvin Hoffman

|||

FileStream stream = File.OpenRead(@."c:\MyReport.smdl");
definition = new Byte[stream.Length];
stream.Read(definition, 0, (int) stream.Length);
stream.Close();
//Now use CreateModel with "defintion" variable

|||

Hi,

This is what i came up with while searching for a way to create model programaticaly.

Uploading a Report Model

Dim DataSourceName As String = "/Adventure Works"

Dim ModelName As String = "/Adventure Works Model"

Dim MyServer As String = "MyReportServer"

Dim rs As New ReportingService2005

rs.Url = "http://" + MyServer + "/reportserver/reportservice2005.asmx"

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim props = Nothing

Dim modelPath As String = "./Adventure Works Model.smdl"

Dim fs As FileStream

fs = File.OpenRead(modelPath)

Dim modelDefinition As Byte() = New [Byte](fs.Length) {}

fs.Read(modelDefinition, 0, CInt(fs.Length))

fs.Close()

Try

rs.CreateModel("Adventure Works Model", "/", modelDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Creating Data Source

Dim dsDefinition As New DataSourceDefinition

dsDefinition.Extension = "OLEDB-MD"

dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated

dsDefinition.ConnectString = "data source=" + MyServer + ";initial catalog=Adventure Works DW"

dsDefinition.ImpersonateUserSpecified = True

dsDefinition.Enabled = True

dsDefinition.EnabledSpecified = True

Try

rs.CreateDataSource("Adventure Works", "/", False, dsDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Associating Report Model with Data Source

Dim ds() As DataSource

ds = rs.GetItemDataSources(ModelName)

Dim dsref As New DataSourceReference

dsref.Reference = DataSourceName

ds(0).Item = dsref

Try

rs.SetItemDataSources("/Adventure Works Model", ds)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Deploy Problem

I created a package, created an XML Config file, created a deployment utility and then deployed to server.

Changed Connection Managers Data Source and Initial catalog properites during deplyment. Config file in the deployment folder have the right data source and initial catalog properties value.

But when i run the package..I see wrong connection string(still showing dev server name) even though I changed during these properties during deployement and shows it right in configuration file in deployment folder.

From where the package is reading connection properties....very confusing

I will appreciate any help!!!

Amar

Don't use the data source and initial catalog properties. Use the ConnectionString property instead. At least, that's worked good for me.|||

Thanks Phil for you reply.

I have used Data Source and Initial Catalog properites in another pacakge and it worked fine...but not this time;)

|||

I even tried ConnectionString property..but still not working. I am missing something here

|||Are you sure that when you deploy the SSIS package can find the *correct* config file? It's not grabbing values from a duplicate config file sitting somewhere, is it?|||

SSIS package should use config file in deployment folder...i dont think SSIS is doing that. I cannot find any other config file.

Anything else i should check.

|||

When you right-click on the background of the control flow, do you have the XML Configuration file listed under "Package Configurations"? If yes, what directory does it point to?

Are you receiving any errors when you open up the package on the second server?

|||

Yes XML configuration file is listed under Package configurations. I am not specifying any directory path..just providing name of the config file..so that the config file is placed in deployment folder.

No..I am not getting any error on the second server.Just the connection string is wrong.

|||

Amar Khaira wrote:

Yes XML configuration file is listed under Package configurations. I am not specifying any directory path..just providing name of the config file..so that the config file is placed in deployment folder.

No..I am not getting any error on the second server.Just the connection string is wrong.

Here's the deal. I believe you *need* to specify a path. Here's why. When I opened up and saved a new XML configuration file without a full path, my config file got stored in: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE.

Then I deployed the package, and opened the package from the deployment folder (C:\Documents and Settings\Phil Brammer\My Documents\Visual Studio 2005\Projects\Test01\Test01\bin\Deployment), and the values were picked up from the IDE folder, not the deployment folder.

Check into that and use absolute paths, where possible.|||I can confirm Phil's answer, we ran into a similar problem recently. To use XML config files without the full path, you have to make sure the current working directory is the same as the directory to package is located in. If you are running DTEXEC directly, you could use a batch file to change the directory (CD) before calling DTEXEC. If you are running it from SQL Agent, the only work-around we found was to have Agent call a batch file to run DTEXEC instead of running it directly.|||

I specified path while creating XML config file...but it still does not work after deployment.I am deploying the package in File System.

But here is what i noticed. When I open package from deployment folder by clicking C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Some_FolderName\Package_Name.dtsx it shows right connection properties.

But I try to run the package from IS by right clicking the package->Connection Manager it shows wrong server name.

I am doing something terribly wrong. Please HELP!!

Amar

|||Unless you have the full SSIS client installed, when you right-click and execute a package, it is using your local machine's properties, not the remote machine's.|||I have full SSIS Client Installed.

Dependency file locations for a SSIS package

Hi all,

I've have a simple question. After running the SSIS deployment utility to install a couple of SSIS packages into a SQL Server, is there a way of determining programmatically (at some later stage) where the dependency files (in my case a simple xml configuration file) for the SSIS packages were installed (if the user chose not to install it into the default location).

Thanks,

Johan

Johanw581996 wrote:

Hi all,

I've have a simple question. After running the SSIS deployment utility to install a couple of SSIS packages into a SQL Server, is there a way of determining programmatically (at some later stage) where the dependency files (in my case a simple xml configuration file) for the SSIS packages were installed (if the user chose not to install it into the default location).

Thanks,

Johan

Absolute paths are required when pointing to things external to the package (XML configuration files, etc...), so the path should never change from when the package was developed.