Accessing a DSN through a Sql job

I am trying to execute an SSIS package (which accesses a dbf file through an odbc connection) through a Sql job, but the package log reports an error of "Disk or netowrk error". When I execute the package in the IDE, the package executes fine. When I run the manifest on the DB server, I can execute the package with no errors. But, when I create the job, and try to execute the job, it fails. I thought at first that the user didn't have privileges to the directory that the file existed in, but that isn't the case.

Can anyone shed some light on how to accomplish what I am trying to accomplish? It seems like this would be a common use of SSIS, but I cannot seem to get it to work.

Thanks in advance for any assistance you can provide!

Craig

This seems to be security issue, as your package can execute fine within the IDE, but not through the SQL Agent. I'd check the SQL Agent user account, along with the proxy details on the job.

|||

Thank you for your reply, Deniz.

Do you know of any web sites that I can get more information on setting up the Sql Agent account permissions? I granted that user admin privileges, but still receive the same error.

Craig Browder

craigster1976@.msn.com

Accessing (writing into) Terdata by ado.net and SSIS

Hello everybody,

my name is Christof and I#m currently trying to write into our Terdata Warehouse by SSIS. In the Management Studio we can perfectly access Terdata - reading from it - but we actually see no way writing back Data into that system. Is there any hidden feature, any configuration menu that we had′nt seen so far?.

In the software documentation we found a tip "ADO.NET" as Data Destination is′nt imlemented for now - please wait until ....

I′m asking me if someone has had the same problem before and could help me out with a little tip.

thx in advance!

Christof

Moved to the SSIS forum. It doesn't sound like you're having a problem with ADO.NET itself, but rather how to get SSIS to do what you want, so I think this is the best place to start.

Thanks,
Sarah

Accessing (writing into) Terdata by ado.net and SSIS

Hello everybody,

my name is Christof and I#m currently trying to write into our Terdata Warehouse by SSIS. In the Management Studio we can perfectly access Terdata - reading from it - but we actually see no way writing back Data into that system. Is there any hidden feature, any configuration menu that we had′nt seen so far?.

In the software documentation we found a tip "ADO.NET" as Data Destination is′nt imlemented for now - please wait until ....

I′m asking me if someone has had the same problem before and could help me out with a little tip.

thx in advance!

Christof

Good luck with non-SQL Server destinations.

Read through here for tips: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106559&SiteID=1

Access to integration services

Hi Guys,

I have a quick question, Can i access the SSIS services of a Server from a remote server using Sql Server management studio.

I'm trying to access a third DB server and i can access it using Sql server management studio with sa and password but the integration services aren't there and when i try to explicitly connect to it, it is not connecting

Any ideas?

P.S: Can any of you recommend a very good book as I've done some work with it but it seems i might be the one doing more work with BI from now on as the organisation is not going to appoint anyone else in this position.

Thank you

Gemma

Do you have the SSIS service running on the server? Do you get any error messages?|||

Gemma wrote:

P.S: Can any of you recommend a very good book as I've done some work with it but it seems i might be the one doing more work with BI from now on as the organisation is not going to appoint anyone else in this position.

This is a good book. Especially for doing ETL with SSIS.

http://www.amazon.com/Expert-Server-Integration-Services-Programmer/dp/0470134119/
|||

If you want something that is more of a reference, the Kirk Haselden Microsoft SQL Server 2005 Integration Services is really good. The Wrox Programmer to Programmer SQL Server 2005 Integration Services is also decent. (They cover nearly the same topics, you will only need one or the other)

I'd agree that Expert SSIS is a good book (through about half of it now). The Expert SSIS is more of a walk through while explaining some of the theories and providing tips and best practices. It often tells the reader to refer to the SQL Server 2005 Integration Services book for more detailed information on the various tasks / transforms.

If you are looking for a reference on Datawarehousing / B.I. in relation to SQL 2005 SSIS / Analysis Services / Reporting etc, The Microsoft Data Warehouse Toolkit by Joy Mundy and Warren Thornthwaite is pretty good. If you are just looking for Datawarehousing / B.I. theory then you should probably read The Data Warehouse Toolkit Second Edition by Ralph Kimball.

I also have the Delivering Business Intelligence with Microsoft SQL Server 2005 (Larson) and Practical Business Intelligence with SQL Server 2005 (Hancock) but have not had a chance to read through them as of yet....

|||

Gemma wrote:

Hi Guys,

I have a quick question, Can i access the SSIS services of a Server from a remote server using Sql Server management studio.

I'm trying to access a third DB server and i can access it using Sql server management studio with sa and password but the integration services aren't there and when i try to explicitly connect to it, it is not connecting

Any ideas?

We were having this problem until earlier this week (we are in the beginning phases of using SSIS ourselves). We were able to resolve the access denied issue (if that is what you are having) by following the steps in the following article: http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

|||

Thanks to all of you, who contributed. I have made a note of your suggested books.

Can any of you also tell me a very good t-sql book as I'm really really bad in t-sql?

Thanks again

Gemma

|||

You'll want two: Inside SQL Server 2005: T-SQL Programming and T-SQL Querying. You will need to read the Querying before the programming as the programming book builds off of ideas presented in the first book.

(Although I only bought them a little less than a month ago and haven't read them yet, when I did research those were the two that came with the highest recommandations)

|||

Thank you

Ta

Gemma

Access To Dts.Variables Causes Exception

Hey all...I'm pretty new to SSIS packages and things are coming along nicely. My problem is accessing variables in script tasks.

I've created two variables (package scope, strings, readonly = false) - aDetailFiles and strDetailFile

Within a Foreach Loop I loop through a folder. I've added a script task and within that I try the following:

Dts.Variables("User::aDetailFiles").Value = Dts.Variables("User:trDetailFile").Value.ToString() + "|" + Dts.Variables("User::aDetailFiles").Value.ToString()

Everytime this script executes I get the following errors:

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_feac87c947ce4431a4fee0ba0e13631d.ScriptMain.Main() in dts://Scripts/ScriptTask_feac87c947ce4431a4fee0ba0e13631d/ScriptMain:line 25

I have searched this error message but the only thing I found was to set the ReadWrite variable property but I don't know where to set that.

Expresion property set as false also.

Any ideas? Thanks.

Right Click the script task, press F4, this will show the properties of the script task, there you will have properties called ReadOnlyVariables, ReadWriteVariables. You can set the variables name in this based on your requirement.
These variables will be available in the script, else they dont have visibility inside the script task and an error will be thrown at run time.

Thanks

Access Denied when trying to execute a ssis task

Hi,

I sometimes come accross this error when I attempt to execute an isolated task in the control flow. What is funny is that I am still able to debug the package.

It eventually resolves after a while. What could it be?

Thanks

Philippe

TITLE: Microsoft Visual Studio

Access Denied. (Exception from HRESULT: 0x80030005(STG_E_ACCESSDENIED))

BUTTONS:

OK

Are you using source control?

You usually get this error if you execute something that isn't checked out.

-Jamie

|||

Yep, I do use VSS 6

I have a hard time understanding a few things with VSS, i.e. I was running a package fine in BIDS but it failed in SSMS, The package seemed checked-in but the solution was checked out in another location under the same userid.

I did force the undo check out and it eventually went fine. Now, SSMS is running the version I want.

I yet have to understand exactly how VSS Works. Right now, I am confused.

Philippe

|||The point is that you shouldn't have to check an SSIS package out of source control (we use TFS here) in order to execute it. This mis-feature is still present 15 months after Philippe's message, and still very frustrating!|||

I agree.

Have you reported this at Connect?

-Jamie

|||

Hi,

I have dropped VSS. I now use my company "official" source control, CVS, along with a couple third party tools like Tortoise, Smart CVS and CVSSCC which gives me some integration right from dev tools.

All that source control stuff is still really not user friendly, because of that I keep multiple backups of my stuff.

This has saved my life a couple times.

I thought the point of using any source control was to free the developer of any concern about these things and let him focus on development. I was wrong.

My IT chose CVS only because it is free, not because it is good, on the top of that they run an outdated unix version on a very old box.

I have a hard time with this while it is supposed to be "safer".

I need to be really carefull with this, not feeling safe anyhow.

Does any one has a success story to share as far as source control of any kind is concerned?

- Multiple developers on the same project

- Deployment of specifc versions by a dba team

- roll back to previous versions

- branches management

- never loosing any file/version on your working folder

- no fuss with roots/modules

- no issues with caps/no caps version fo the same module

- always get the project to the right root/module

- able to easilly do spring cleaning of the repository

Thanks,

Philippe

Access Denied when trying to execute a ssis task

Hi,

I sometimes come accross this error when I attempt to execute an isolated task in the control flow. What is funny is that I am still able to debug the package.

It eventually resolves after a while. What could it be?

Thanks

Philippe

TITLE: Microsoft Visual Studio

Access Denied. (Exception from HRESULT: 0x80030005(STG_E_ACCESSDENIED))

BUTTONS:

OK

Are you using source control?

You usually get this error if you execute something that isn't checked out.

-Jamie

|||

Yep, I do use VSS 6

I have a hard time understanding a few things with VSS, i.e. I was running a package fine in BIDS but it failed in SSMS, The package seemed checked-in but the solution was checked out in another location under the same userid.

I did force the undo check out and it eventually went fine. Now, SSMS is running the version I want.

I yet have to understand exactly how VSS Works. Right now, I am confused.

Philippe

|||The point is that you shouldn't have to check an SSIS package out of source control (we use TFS here) in order to execute it. This mis-feature is still present 15 months after Philippe's message, and still very frustrating!|||

I agree.

Have you reported this at Connect?

-Jamie

|||

Hi,

I have dropped VSS. I now use my company "official" source control, CVS, along with a couple third party tools like Tortoise, Smart CVS and CVSSCC which gives me some integration right from dev tools.

All that source control stuff is still really not user friendly, because of that I keep multiple backups of my stuff.

This has saved my life a couple times.

I thought the point of using any source control was to free the developer of any concern about these things and let him focus on development. I was wrong.

My IT chose CVS only because it is free, not because it is good, on the top of that they run an outdated unix version on a very old box.

I have a hard time with this while it is supposed to be "safer".

I need to be really carefull with this, not feeling safe anyhow.

Does any one has a success story to share as far as source control of any kind is concerned?

- Multiple developers on the same project

- Deployment of specifc versions by a dba team

- roll back to previous versions

- branches management

- never loosing any file/version on your working folder

- no fuss with roots/modules

- no issues with caps/no caps version fo the same module

- always get the project to the right root/module

- able to easilly do spring cleaning of the repository

Thanks,

Philippe