Executing SSIS with SQL package Config

Question

Executing SSIS with SQL package Config

Kwisatz78

SSChampion

Points: 12478
More actions
March 1, 2010 at 9:41 am

#221050

Hi there - I have created a package using an environment variable and SQL package config. I tested this locally and was able to change the config in my SQL table which would affect how the package worked through BIDS. I have now buitlt hte package and deployed it to my test instance.
I have created the necessary envrionemt variable and run the manifest file to import it into MSDB. I have also changed all the config in my SSIS config table to tie up with my test instance. When I come to run the package now in the Execute package Utility i can see under Connection Managers all the old details from my local machine, and if I try to execute the package it fails (presumablyt because its not picking up the new config)
Can anyone tell me why I am seeing these old values and how to correctly execute the package?
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I can think of a few things. First, what logging are you doing? If you aren't doing any stop, go back and add some, this is hard enough with logging.

Ideas:

1. Environmental variable was added to user and not system.

2. Environmental variable was added but existing sessions just don't see it yet, restart SQL/Agent/SSIS.

3. Config file is not in the place the package is looking for it.

Just my first thoughts.

CEWII

Kwisatz78 SSChampion Points: 12478 More actions · Answer 2

hi thanks for your reply - it seems I had put in the wrong information into the environment variable, once I re-installed the package the config was picking up.

I do have a question in regards to logging however and also the behavious of packages when deployed.

Currently I am using a text log provide with everything selected. In my Debug window when I get an error it gives the infromation about that error, but that is not then logged in my text file, why is this?

For example in my Debug window I can see errors like this:

Error: 0xC001602A at ISRCTN_ApplyNumber, Connection manager "CCT FTPcm": An error occurred in the requested FTP operation. Detailed error description: Receiving file "/INPUT/030210_ISRCTN_OUTPUT_funded.xls".

File "C:\Projects\ISRCTN\Spreadsheets\030210_ISRCTN_OUTPUT_funded.xls" already exists.

But that doesn't get logged in my text log.

Also when I run the package as part of a job it just run continously and doesn't fail - I have encrypted the sensitive data do i need to put in a password for the job to work? I would have expected it to fail and tell me a password was required.

Basically I have 2 issues thanks for any help.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Ok, first issue, logging. Yo may need to look at the logging of that FTP component explicitly, you should be able to set options on it alone. I'm wondering if there isn't something set for it in the logging config.

Second issue, encryption. I'm reading a bit into what you have said, what I think you are saying is that the package is configured to encrypt sensitive information. What this means is that the user who edited and saved the package is the user who has to run it. Since this is VERY rare in my environment I set it to not save any sensitive data and read what it needs from config files. I also rarely use passwords and instead depend on trusted connectivity where I can. It SHOULD fail to run, but the error it gives will not be need password or bad password, but something along the lines of could't decrypt sensitive info (its been a while so I can't remember the exact wording). I am a little concerned that it runs without failing. Not sure about that..

CEWII

Kwisatz78 SSChampion Points: 12478 More actions · Answer 4

Hi thanks again for the reply.

In regards to the FTP logging I will look more deeply into the component itself.

As for the executing of the job, I eventually setup the job to run a SSIS package and supplied the decryption password in the configurations tab, this seemed to work.

As for you solutions you don't save the sensitive data and instead pick up the values from a package config elsewhere? How do you then secure the config? I suppose if you are using a SQL package config you lock down access to that table?

Just quizzing some best pratices basically.

Thanks

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

I have taken a higher level approach to dtsConfig files. The directory in which they are kept is secured, there are only a few users who have rights to it. When I can I don't ever use passwords and opt for trusted security. I know this isn't always possible, for most SQL server to SQL server connections it usually is, I would strongly recommend it. I have not used the SQL configuration source much and never when a password was needed. I would be hesitant to store passwords in clear text in a SQL table.

CEWII