How to schedule an SSIS package that invokes a web service

Hey,

I have an SSIS package that invokes a web service and then updates a table. It runs fine as long as I am running it on the local machine. However, as soon as I save this package to the sql server, and try to schedule this as a job, it starts to fail. Now, the web service writes to an xml file and also uses an xsd and and an xsl file. When I save a dts package to the sql server, whats the proper way of referencing these files? I think this probably is what is making the package to fail, ut I am not sure.

Any help is greatly appreciated!!

Thanks!

You should use a configuration (right-click in the package and choose configurations) to set the ConnectionString property of the connection managers for the files. Or you could use expressions to set the connection strings (paths and filenames) based on variables. The variables can be set at runtime using the /SET option of DTEXEC.|||Also make sure you've configured SSIS logging, so you can find out why the package fails now or (once you fix the problem and go to production) if something goes wrong with scheduled package in production.|||That depends on where you want to keep them. I prefer to keep them in files on the disk. If your package is in SQL and you prefer to avoid the disk entirely, you can keep them in the database and just load them into variables via the Execute SQL task. The XML task and the XML Source component support receiving the XSD/XSLT from variables.
|||Thanks a lot for the suggestions. I will try them out and see how it works.|||

Hey,

Sorry for this delayed reply. Since I posted this question a lot of issues cropped up with my SQL server which eventually led to a total reinstallation of all apps on my pc. Anyway, I discovered that the problem I have been having was because of permission issues. I was able to fix that problem and just when I thought that I had everything going, I came across a new problem. After I save the SSIS package in sql server and create a job, the job starts failing. This is the error that I am getting:

-1073548540,0x,An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: Unable to connect to the remote server.

Any suggestions would be very helpful.

Thanks!

|||Could it be a authentication issue? Is there any security on the web service?|||

Guys!!Thanks a lot for all the suggestions. Really appreciate your help. Problem was a combination of many issues. One was related to 32bit/64bit differences, the other was authentication, and finally some syntax problems when invoking the web service. Seems it is working really well now.

Thanks again!

how to scale the paper size?

Hi All,
I have a report which is A3 size , when it print out i wanna to fit into A3
size.
Is that possible to do in reporting service?
Cheers
NickThere is a PageSize property on the report (under the Layout tab). Set this
to the appropriate width and height.
"Nick" wrote:
> Hi All,
> I have a report which is A3 size , when it print out i wanna to fit into A3
> size.
> Is that possible to do in reporting service?
> Cheers
> Nick|||Thanks, David,
how about if i wanna pring out to fit into A4 paper.
Cheers
Nick
"David" wrote:
> There is a PageSize property on the report (under the Layout tab). Set this
> to the appropriate width and height.
> "Nick" wrote:
> > Hi All,
> >
> > I have a report which is A3 size , when it print out i wanna to fit into A3
> > size.
> >
> > Is that possible to do in reporting service?
> >
> > Cheers
> >
> > Nick

how to run the Service Broker Samples

I am trying to run the Readme_HelloWorld sample on the SQL server 2005 Books online. But don't know where I can get the setup.sql, SendMessage.sql, ReceiveMessage.sql scripts.

I followed two examples on the blog, but they did not work at all. Here are the scripts:

/* example 1 */
create database TestSB
go
use TestSB
go
create queue TxQ
create queue RxQ
create message type Msg
create contract MsgContract(Msg SENT BY ANY)
create service TxSve on Queue TxQ
Create service RxSve on Queue RxQ
declare @.h uniqueidentifier
begin dialog conversation @.h
from service TxSve to Service 'RxSve'
on contract MsgContract;
send on conversation @.h Message type Msg ('<hello>World</hello>')
end conversation @.h with CLEANUP;

select *
from RxQ

/* example 2 */
use testsb
go

create message type HelloMessage
validation = NONE
go

/** 2. create contract **/
create contract HelloContract
(HelloMessage SENT by initiator)

/** 3. create queue **/
create queue SenderQueue
create queue ReceiverQueue

/** 4. create service **/
create service sender
on queue SenderQueue

create service receiver
on queue ReceiverQueue(HelloContract)

declare @.handle uniqueidentifier
declare @.message nvarchar(100)

begin
begin transaction;
begin dialog conversation @.handle
from service sender
to service 'receiver'
on contract HelloContract
set @.message = N'Hello, World';
send on conversation @.handle
message type HelloMessage(@.message)
end conversation @.handle
commit transaction
end
go

Receive convert(nvarchar(max), message_body) as message1
from ReceiverQueue

select convert(nvarchar(max), message_body) as message1
from ReceiverQueue

select *
from SenderQueue

select *
from Receiverqueue

Any assistance or WORKING samples are greatly appreciated.

create database TestSB

go

use TestSB

go

create queue TxQ

create queue RxQ

create message type Msg

create contract MsgContract(Msg SENT BY ANY)

create service TxSve on Queue TxQ

Create service RxSve on Queue RxQ ([MsgContract]);

go

declare @.h uniqueidentifier

begin dialog conversation @.h

from service TxSve to Service 'RxSve'

on contract MsgContract

with encryption = off;

send on conversation @.h Message type Msg ('<hello>World</hello>');

end conversation @.h;

receive cast(message_body as XML), * from RxQ;

The sample has a number of problems:

- the target service must be bound to the contract used, the fix is to add the ([MsgContract]) clause to the second CREATE SERVICE.

- dialogs with encryption require a database master key. Encryption is not needed within a SQL instance, fix is to add the ENCRYPTION = OFF to the BEGIN DIALOG statement.

- one should never use END CONVERSATION ... WITH CLEANUP, see http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx

- This one I didn't fix, but one should not use the fire-and-forget message pattern, see http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx

HTH,
~ Remus

|||Thank you very much, Remus. It works.

How to run Reporting service in MS-Access environment?

Hi

Please someone tell me, is it possbile to run Reporting service in MS-Access environment?
I mean to say i have to use MS-Access as user interface to design and generate report
(instead of Visual studio IDE) and the Sql server 2005 should be Report Server.
If it is possible then how can i implement it.

Thanks and Regards

Altaf Nizamuddin

Hi Altaf,

It is possible to design reports in Access, and you can import it with BIDS with some restrictions. Well, i never tried, but if you can import the report viewer control in Access and you write a custom code behind, then you should be able to render the report. Again, I never tried it, just an idea.

Regards,

Janos

ps.: maybe I'll try it

|||http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/migratereports.mspx|||

Hi Adolf,

Thanks a lot but it will never work in my sceneraio,

The article explains "How to Migrate Access reports to reporting services", but i want to run Reporting service in MS-Access environment, I mean to say i have to use MS-Access as user interface to design and generate report (instead of Visual studio IDE) and the Sql server 2005 should be Report Server.

Thanks and Regards

Altaf Nizamuddin

|||So just design it in Access and migrate it.

Personally I'd just take the hit on learning how to create reports in RS.sql

How to run multiple store procedures in SQL Reporting Service

Can we run two store procedures in the Query String under the Dataset section
of SQL Reporting Service 2005? It seems like the Query String only allow one
store procedure. Ww want to use a separate store procedure to open a
symmetric key before we run the second store procedure to retrieve the
sensitive data.What about writing a thrid procedure calling the two others ?
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--sql

How to run Microsoft full-text Search service on a separate machine(otherthanSQLServer)

How to run Microsoft full-text Search service on a separate
machine(otherthanSQLServer)
Is it possible to create Microsoft Search Service for full-text search
on separate machine instead of running it on SQL Server machine? If so,
how? Can you point me to any best practice to architect it?
I am looking for possible architectures to improve performance of MS
full-text Search Services by taking MS Search services out from SQL
Server machine and implement it on separate machine.
I'd appreciate any help you could provide on it.
Brian,
No, it is not possible to the "Microsoft Search" (mssearch.exe) service
running on another machine without SQL Server 2000. However, you can use the
procedures documented in the following Kb article 240867 (Q240867) "INF: How
to Move, Copy, and Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867 to come close
to "propagating" the Full-text Indexing of the FT Catalogs on another
server. Note, this is also not possible with SQL Server 2005.
If you're trying to properly tune a server for Full-text Search, I can help,
but I need you to provide more info on your server's configuration as well
as software and database configurations. Below are SQL FTS resources, you
should review:
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<brianshaw03@.yahoo.com> wrote in message
news:1129605910.538413.210580@.g14g2000cwa.googlegr oups.com...
> How to run Microsoft full-text Search service on a separate
> machine(otherthanSQLServer)
> Is it possible to create Microsoft Search Service for full-text search
> on separate machine instead of running it on SQL Server machine? If so,
> how? Can you point me to any best practice to architect it?
> I am looking for possible architectures to improve performance of MS
> full-text Search Services by taking MS Search services out from SQL
> Server machine and implement it on separate machine.
> I'd appreciate any help you could provide on it.
>
|||Hi John/All
I am trying to understand the best configuration for Full text search.
We are running full text search with 4GB RAM on SQL server 2000 on a
windows 2003 enterprise server. I have the
/3G option on in boot.ini. The VM setting is set at 2048 to 4095.
Typically SQL server acquires close to 3 GB RAM (about 2.87 GB) and the
rest "appears" available with task manager. While running full text
search queries we see mssearch.exe taking about 20MB RAM and it page
faults heavily.
I am not sure if this is an indicator of MSSearch running out of
memory. Also I am not sure as about 1GB memory is "available", why
MSSearch does not consume more memory?
I read it in the manual that the optimal configuration for full text
search
is
1. The virtual memory size to at least 3 times the physical memory
installed
in the computer.
2. The SQL Server max server memory server configuration option to 1.5
times
the physical memory (half the virtual memory size setting).
Do I have to set the above settings? If I had more virtual memory , I
am likely to have more page faults by my understanding. Is that true?
What are the right settings and what am I doing wrong?
Matt
John Kane wrote:[vbcol=seagreen]
> Brian,
> No, it is not possible to the "Microsoft Search" (mssearch.exe) service
> running on another machine without SQL Server 2000. However, you can use the
> procedures documented in the following Kb article 240867 (Q240867) "INF: How
> to Move, Copy, and Backup Full-Text Catalog Folders and Files" at
> http://support.microsoft.com/default...b;EN-US;240867 to come close
> to "propagating" the Full-text Indexing of the FT Catalogs on another
> server. Note, this is also not possible with SQL Server 2005.
> If you're trying to properly tune a server for Full-text Search, I can help,
> but I need you to provide more info on your server's configuration as well
> as software and database configurations. Below are SQL FTS resources, you
> should review:
> SQL Server 2000 Full-Text Search Resources and Links
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
>
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> <brianshaw03@.yahoo.com> wrote in message
> news:1129605910.538413.210580@.g14g2000cwa.googlegr oups.com...
sql

How to retain content of TempDB even if SQL Service restart?

Gurus,
I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreated.
Is there any option / way to retain the content of TEMPDB even if SQL Server
/ SQL Server Services restarted?
Help much appreciated.
Thanks
Javed Iqbal
On Mon, 30 Jul 2007 12:32:01 -0700, Javed Iqbal
<JavedIqbal@.discussions.microsoft.com> wrote:

>Gurus,
>I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreated.
>Is there any option / way to retain the content of TEMPDB even if SQL Server
>/ SQL Server Services restarted?
>Help much appreciated.
>Thanks
>Javed Iqbal
No.
Roy Harvey
Beacon Falls, CT
|||> Is there any option / way to retain the content of TEMPDB even if SQL
> Server
> / SQL Server Services restarted?
Yes, re-create the content manually in a startup procedure.
Or, store it in a "real" user database.
Aaron Bertrand
SQL Server MVP
|||Thanks Aaron,
This is what I was looking for...
Thanks a lot
Regards
Javed Iqbal
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, re-create the content manually in a startup procedure.
> Or, store it in a "real" user database.
> --
> Aaron Bertrand
> SQL Server MVP
>
>

How to retain content of TempDB even if SQL Service restart?

Gurus,
I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreated.
Is there any option / way to retain the content of TEMPDB even if SQL Server
/ SQL Server Services restarted?
Help much appreciated.
Thanks
Javed IqbalOn Mon, 30 Jul 2007 12:32:01 -0700, Javed Iqbal
<JavedIqbal@.discussions.microsoft.com> wrote:
>Gurus,
>I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreated.
>Is there any option / way to retain the content of TEMPDB even if SQL Server
>/ SQL Server Services restarted?
>Help much appreciated.
>Thanks
>Javed Iqbal
No.
Roy Harvey
Beacon Falls, CT|||> Is there any option / way to retain the content of TEMPDB even if SQL
> Server
> / SQL Server Services restarted?
Yes, re-create the content manually in a startup procedure.
Or, store it in a "real" user database.
--
Aaron Bertrand
SQL Server MVP|||Thanks Aaron,
This is what I was looking for...
Thanks a lot
Regards
Javed Iqbal
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Is there any option / way to retain the content of TEMPDB even if SQL
> > Server
> > / SQL Server Services restarted?
> Yes, re-create the content manually in a startup procedure.
> Or, store it in a "real" user database.
> --
> Aaron Bertrand
> SQL Server MVP
>
>

How to retain content of TempDB even if SQL Service restart?

Gurus,
I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreate
d.
Is there any option / way to retain the content of TEMPDB even if SQL Server
/ SQL Server Services restarted?
Help much appreciated.
Thanks
Javed IqbalOn Mon, 30 Jul 2007 12:32:01 -0700, Javed Iqbal
<JavedIqbal@.discussions.microsoft.com> wrote:

>Gurus,
>I know if SQL Server / SQL Server Services are restarted, TEMPDB is recreat
ed.
>Is there any option / way to retain the content of TEMPDB even if SQL Serve
r
>/ SQL Server Services restarted?
>Help much appreciated.
>Thanks
>Javed Iqbal
No.
Roy Harvey
Beacon Falls, CT|||> Is there any option / way to retain the content of TEMPDB even if SQL
> Server
> / SQL Server Services restarted?
Yes, re-create the content manually in a startup procedure.
Or, store it in a "real" user database.
Aaron Bertrand
SQL Server MVP|||Thanks Aaron,
This is what I was looking for...
Thanks a lot
Regards
Javed Iqbal
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, re-create the content manually in a startup procedure.
> Or, store it in a "real" user database.
> --
> Aaron Bertrand
> SQL Server MVP
>
>

How to restrict evil create scripts?

Hi,

For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.

For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.

Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

Regards,

WardWard,

I would say as long as your permissions are set right you don't have to
worry. If your user only have rights in their own databases they
shouldn't be able to accessother user databases. Xp_Cmdshell which
would be able to delete files or run other OS commands is (by default)
only available to members of the sysadmin role.
So I would say create a empty datbase and grant the user only db_owner
or ddl_admin rights within the database.

Markus|||Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.
>
For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.
>
Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

First of all, which version of SQL Server including service pack do you
have?

As M.Bohse said, run the scripts as a user who only have access in that
database, although in that database he need some privs. Very important:
make sure that cross-database chaining is turned off, and that the
database is not set as trustworthy on SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

Thanks for the tips!

We are running SQL Server 2005.

Ward

Erland Sommarskog wrote:

Quote:

Originally Posted by

Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

>For a service I'm working on I need to ask the user for their database
>create script. It's used to re-create the users database schema in a
>temporary database on a in-house server in an automated fashion.
>>
>For security reasons, I need to be sure that the create script can only
>create tables, columns etc and not things like snooping in other
>databases and/or formatting the server.
>>
>Can you give me pointers about what the minimum grants are to let good
>script execute successfully and evil scripts fail?

>
First of all, which version of SQL Server including service pack do you
have?
>
As M.Bohse said, run the scripts as a user who only have access in that
database, although in that database he need some privs. Very important:
make sure that cross-database chaining is turned off, and that the
database is not set as trustworthy on SQL 2005.
>
>

|||Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

Hi Erland,
>
Thanks for the tips!
>
We are running SQL Server 2005.

In that case you should grant CONTROL on the dbo schema for the database
user. Maybe they are creating other scheams, but then they need to include
GRANTs for that.

You should also use

select name, is_trustworthy_on, is_db_chaining_on
from sys.databases

to make sure that the databases are not marked as trustworthy, or available
for DB-chaning.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to restrict evil create scripts?

Hi,
For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.
For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.
Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?
Regards,
Ward
Ward Bekker
"Asp.Net Discussions for the Professional Developer"
http://www.dotnettaxi.com
"Free .Net 2.0 C# to/from VB.Net Code Converter"
http://www.dotnettaxi.com/Tools/Converter.aspxI would consider using Virtual Server isolation.
Set up a virtual server, save copies of the vhd/vmc files (set the file
properties to read only), and then each time you have such a script, use a
virtualized environment and 'evil' scripts won't have anything to sniff
around it.
When finished, just delete the VM.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ward Bekker" <ward@.NospaaMequanimity.nl> wrote in message
news:eb7es3$c9a$1@.news.cistron.nl...
> Hi,
> For a service I'm working on I need to ask the user for their database
> create script. It's used to re-create the users database schema in a
> temporary database on a in-house server in an automated fashion.
> For security reasons, I need to be sure that the create script can only
> create tables, columns etc and not things like snooping in other databases
> and/or formatting the server.
> Can you give me pointers about what the minimum grants are to let good
> script execute successfully and evil scripts fail?
> Regards,
> Ward
>
> --
> Ward Bekker
> "Asp.Net Discussions for the Professional Developer"
> http://www.dotnettaxi.com
> "Free .Net 2.0 C# to/from VB.Net Code Converter"
> http://www.dotnettaxi.com/Tools/Converter.aspx|||Hi Arnie,
Interesting approach. Is great as a "second line" of defense. SQL Server
can have advanced security configuration, so I was wondering what could
be done in that level. Do you have any idea's about how that?
Thank you,
Ward
Arnie Rowland wrote:
> I would consider using Virtual Server isolation.
> Set up a virtual server, save copies of the vhd/vmc files (set the file
> properties to read only), and then each time you have such a script, use a
> virtualized environment and 'evil' scripts won't have anything to sniff
> around it.
> When finished, just delete the VM.
>