Running Packages in Management Studio

Question

Running Packages in Management Studio

ehlinger

Hall of Fame

Points: 3585
More actions
January 9, 2007 at 4:35 pm

#122227

We are new to SQL 2005 and currently in the testing phase. We have a good handle on most things so far but I am trying to recreate our packages that we have in v7 to run in SQL 2005. I am able to duplicate them in the Business Intelligence Develeopment Studio and when I run them in debug everything works just fine.
I've deployed them and have them on the "Production" server but when I attempt to execute them they won't run. I get errors in validation. The first two are: Error: The AcquireConnection method call ot the connection manager "SourceConnectionOLEDB" faile dwith error code 0xC0202009.
This is followed by: Error: component "Source - Query" (1) failed validation and returned error code 0xC020801C.
Not sure if this is relevant or not, but we have SQL 2005 running with SQL Authentication because we don't have AD running yet.
I've tried to execute the package with the source of file system, SQL Server and SSIS Package Store and get the same results regardless.
Any help or suggestions would be geatly appreciated. Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Agson Chellakudam Ten Centuries Points: 1387 More actions · Answer 1

Hi Ehlinger,

I was also facing a similar kind of problem while running a package from production environment, even though it is executed successfully from my development environment (BI studio on my local system).

My first workaround for this was, I just opened the packages in Production system BI Studio and reinitialized the connection parameters . Then saved the package and imported to the SQL Server/ Executed as file system.

This solution worked for me. But I am not sure whether this is the best workaround for the above problem.

Please let me know if this solution working for you

Regards,

Agson Chellakudam

ehlinger Hall of Fame Points: 3585 More actions · Answer 2

Agson,

Thank you. Not sure I usderstand why, but that seemed to work. Wished I didn't have to do the extra work but right now I'll settle for it working. Thank you again.

Now, I'm running up against another issue and that is getting the package run through a job. It fails and their isn't much for the error message. It says: Executed as user: SERVER\SYSTEM. The package execution failed. The step failed.

The package is actually set up to run with sa, but I can't find how to change that. In the job it says to run the steps as SQL Agent Service Account but that is the only option in the drop down. If I could change that to sa I think it may work.

Anyone have any direction?

Thanks.

Agson Chellakudam Ten Centuries Points: 1387 More actions · Answer 3

Hi Ehlinger,

I can give some insights for this problem.

Please check the Service startup login for the 'SQL Server Agent' and make sure that same login is present in 'SQL Server' with enough credentials.

I am using same 'Domain account' (Administrator) for starting both SQL Server Agent as well SQL Server Service. So that I am not facing any issues while running the packages from SQL Server Agent.

Regards,

Agson Chellakudam

Ruth Rozansky-396257 Valued Member Points: 74 More actions · Answer 4

What's the right thing to do that? I am facing the same problem but I can't resolve it the way it was described because we don't have BI installed on production server. Any input would be greatly appreciated.

Agson Chellakudam Ten Centuries Points: 1387 More actions · Answer 5

Hi Ruth,

I have followed two approaches to resolve this problem.

1. Used only domain accounts (windows authentication) for initializing the Connection managers (I have noticed that, this issue arising only when we are initializing 'password' property)

2. Used Script task to generate the connection string (Stored into a variable) . Here we can pass the 'password' also to the connection string.

Then Initialized ‘ConnectionString’ property of the Connection manager from above variable using ‘Expressions’.

Please let me know if any of this solution is working for you.

Regards,

Agson Chellakudam

Ruth Rozansky-396257 Valued Member Points: 74 More actions · Answer 6

Agson,

Thank you very much for your feedback.

1. I can not use Windows Authentication when connecting to Oracle. I must supply user id/password.

2. Could you specify how you use Script task?

Thank you, again,

Ruth

Agson Chellakudam Ten Centuries Points: 1387 More actions · Answer 7

Hi Ruth,

There is a better and simple approach wich we can avoid 'Script Task' (I used Script task along with some other requirements)

If your ServerName (Initial Catalog),LoginID and Password are static

1. Right click the connection manager --> Properties --> Click Expressions --> Select 'ConnectionString' property column --> Click Expression.

You will be prompted for an Expression window where we can provide the connection string.

Place the static connection string in this window. Click OK.

Example:-

Data Source=Mss83;Initial Catalog=Data Partition DB;Provider=SQLNCLI.1;uid=sa;pwd=samplepassword

Now this connection manager will be initialized using the ConnectionString during the execution.

If any of values in the connection string is dynamic and you wanted to initialize it from variables.

1. Right click the connection manager --> Click Expressions --> Select 'ConnectionString' from property column --> Click Expression.

You will be prompted for an Expression window.

Place the connection string for dynamic creation

Example: -

"Data Source=Mss83;Initial Catalog=Data Partition DB;Provider=SQLNCLI.1;uid=sa;pwd=" + @[User::password]

Note:- Here password is a variable in the package.

Regards,

Agson Chellakudam