Running SSIS Packages in Management Studio

We are using SQL Server 2005 Standard edition.We have the development studio installed on a developer’s workstation.The SSIS package is accessing network folders using UNC and the tasks in packages are accessing SQL Server with a SQL Server id.

The SSIS package runs fine on the workstation. We deploy the SSIS package as a SSIS store on SQL Server.I receive Error 18456 Severity 14 State 8 error when I run the SSIS package as a SQL Agent job.

When I run the SSIS package from the Integration Services Management Studio, I get The AcquireConnection method call to the connection manager "dboSQLabm" failed with error code 0xC0202009.

I’m sure this is a permission based problem since it runs fine on my workstation.I tried logon onto the SQL Server with my id (I’m an admin to the server).I tried running the agent with my windows id.It looks like I’m getting problem connecting to the SQL Server tables since the logs are properly written to the network drive.

In SQL DTS 2000, all we needed to do was give the SQL agent id permission to the network folder. I tried to search the forums, but none of the solutions seem to work for my situation.

Do you use config files and do not save sensitive? This should take care of this issue.

There are quite a few posts about it in this forum

Philippe

|||The problem I was getting occurred whether I used a config file or not. I had the package set to EncryptSensitiveWithUserKey. I created another thread to get production deployment suggestions. We don't want our DBAs using BIDS to edit SQL connection manager settings when they migrate SSIS packages to production.|||

Then don't let them. Use configurations and the deployment wizard.

-Jamie

Running SQL Scripts against SQL 2005

Our developers generate a large number of SQL scripts that get applied with each application update. Is there an easy way to select one script after the other and run them against a particular database?

Right now it appears I have to open them one by one (since the window selector is useless for actually picking a script) by scanning through the directory for the next script, confirming the connection (thankfully I'm using Integrated Security), Changing the current database (since it always defaults to master) and finally run the script. And I'm ending up doing this for every script. Hoping there's a better way to do this.

Thanks,

Larry

Management studio accepts a subset of sqlcmd statements. The one you want to use is :r. This reads the contents of the file and puts it in line with the query you have.

i.e.

:r c:\sql\myfirstscript.sql

:r c:\sql\mysecondscript.sql

go

:r c:\sql\mythirdscript.sql

in this example the first 2 scripts are combined and run. If they contain GOs in then each batch will be executed as normal.

The point to note is that the :r does execute the file but reads the file into the query. So make sure each file ends in GO or you seperate your :r statements by go.

TO use sqlcmd mode click on the icon on the toolbar with a window AND a red exclamation mark. Or go to Query and select SQLCMD mode

Running SQL Mail in developers edition

We have SQL Mail running on our servers, but Im often offline during
development (although my machine is on the internet). Can SQL Mail be
configured to run locally in SQL Server 200 Developers Edition? The
configuration docs for pop accounts are confusing in that regard (at least to
me), and I was hoping for some advice - if this is even possible.
Thanks, Mark
Hi
If you setup SQL Server to run under a domain account and use Outlook 2000
you should be ok see http://support.microsoft.com/kb/263556/. You should be
able to log out (but not shutdown!) and emails will still be processed.
John
"MarkAurit" wrote:

> We have SQL Mail running on our servers, but Im often offline during
> development (although my machine is on the internet). Can SQL Mail be
> configured to run locally in SQL Server 200 Developers Edition? The
> configuration docs for pop accounts are confusing in that regard (at least to
> me), and I was hoping for some advice - if this is even possible.
> Thanks, Mark

Running SQL Mail in developers edition

We have SQL Mail running on our servers, but Im often offline during
development (although my machine is on the internet). Can SQL Mail be
configured to run locally in SQL Server 200 Developers Edition? The
configuration docs for pop accounts are confusing in that regard (at least to
me), and I was hoping for some advice - if this is even possible.
Thanks, MarkHi
If you setup SQL Server to run under a domain account and use Outlook 2000
you should be ok see http://support.microsoft.com/kb/263556/. You should be
able to log out (but not shutdown!) and emails will still be processed.
John
"MarkAurit" wrote:
> We have SQL Mail running on our servers, but Im often offline during
> development (although my machine is on the internet). Can SQL Mail be
> configured to run locally in SQL Server 200 Developers Edition? The
> configuration docs for pop accounts are confusing in that regard (at least to
> me), and I was hoping for some advice - if this is even possible.
> Thanks, Mark

Running SQL Mail in developers edition

We have SQL Mail running on our servers, but Im often offline during
development (although my machine is on the internet). Can SQL Mail be
configured to run locally in SQL Server 200 Developers Edition? The
configuration docs for pop accounts are confusing in that regard (at least t
o
me), and I was hoping for some advice - if this is even possible.
Thanks, MarkHi
If you setup SQL Server to run under a domain account and use Outlook 2000
you should be ok see http://support.microsoft.com/kb/263556/. You should be
able to log out (but not shutdown!) and emails will still be processed.
John
"MarkAurit" wrote:

> We have SQL Mail running on our servers, but Im often offline during
> development (although my machine is on the internet). Can SQL Mail be
> configured to run locally in SQL Server 200 Developers Edition? The
> configuration docs for pop accounts are confusing in that regard (at least
to
> me), and I was hoping for some advice - if this is even possible.
> Thanks, Mark