Losing config file between child packages and *$##@!- Passwords!

I have a parent package that uses Execute Package Task against three child packages. They are all setup to use the same configuration file, and the packages are all located in the same folder. Purpose of the configuation is to use the same connection strings for each child package.

I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.

Also, the password-removal issue is killing me in development. Everytime I touch the config. file, it removes the password from the connection string. I have "protection level" set to "don't save sensitive" - I'd appreciate help with this too.

thanks

Mark Challen wrote:

I have a parent package that uses Execute Package Task against three child packages. They are all setup to use the same configuration file, and the packages are all located in the same folder. Purpose of the configuation is to use the same connection strings for each child package.

I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.

Also, the password-removal issue is killing me in development. Everytime I touch the config. file, it removes the password from the connection string. I have "protection level" set to "don't save sensitive" - I'd appreciate help with this too.

thanks

When you have it set to "don't save sensitive" that's what's supposed to happen. It's not saving sensitive information.|||As far as your paths go, you have to use absolute paths. So, you might be better off using an environment variable or something to store the path to the config file.

Relative paths have been requested: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253232|||

Phil Brammer wrote:

When you have it set to "don't save sensitive" that's what's supposed to happen. It's not saving sensitive information.

Do you happen to know which setting will allow the saving of a password so I can work on the packages in development without having to reset the password everytime I touch the configuration? I can understand removing the password when building the solution for deployment, having it auto-magically disappear while staying in the designer it makes no sense.

|||http://msdn2.microsoft.com/en-us/library/ms141747.aspx|||

Mark Challen wrote:

I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.

In my current project I am using that technique; removing the path of the config file from the package configuration and it has worked fine in both ways when called from the aprent package or standalone....

Config file has to be in same folder than the apckages though.

|||

Thanks for the link Phil. Doesn't answer the question, and I'm getting increasingly frustrated by the lack of control with SSIS. Not to get off on a rant, but I personally would rather be responsible for setting security, not having MS dictate that I encrypt everything.

Anyhow, I have found a workaround - once the config. file is established on the parent I set it's "Read-only" property to true in the file system. A low-tech approach, but it seems to work.

The issue of the requirement for an absolute path for the config. file is going to be a showstopper for me (no way am I setting an environment variable; I don't see the thinking behind that - how do I establish the variable in the first place?). I will probably execute my child packages independently from the command line from my own custom wrapper code, passing in my connection strings on the command line. Too bad, I wanted to use the Configuration feature and I spent a lot of time trying to make it work. This seems to be endemic to SSIS (like checkpointing) - a cool feature at first glance, but try to use it and you hit a brick wall. </Rant>

Rafael Salas wrote:

In my current project I am using that technique; removing the path of the config file from the package configuration and it has worked fine in both ways when called from the aprent package or standalone....

Config file has to be in same folder than the apckages though.

Are you sure the package is successfully locating the config file and overriding the connection strings, or is it defaulting back to your original connection manager settings in the child?

|||

Mark Challen wrote:

The issue of the requirement for an absolute path for the config. file is going to be a showstopper for me (no way am I setting an environment variable; I don't see the thinking behind that - how do I establish the variable in the first place?).

You set up the environment variable by way of the system properties in Windows. Once it's set up, you simply reference it thereby making your configurations portable. This is a good way, in my opinion to handle changes across environments that have different configurations (file paths, etc...)

For me, I use SQL Server based configurations and environment variables. I've pretty much got everything setup so that I never have to make changes to a package when promoting it from development into QA and finally into production. Even though file paths may change (commonly C: drive to D: drive, perhaps), the packages continue to work with little interaction. This helps given that we can't promote to production -- another group does that. (Thank you Sarbanes-Oxley!)|||

Mark Challen wrote:

Are you sure the package is successfully locating the config file and overriding the connection strings, or is it defaulting back to your original connection manager settings in the child?

I am 100% sure that it works; actually it is very easy to see if it is not; each time a configuration does not take place; a warning is generated and can be seen in the progress tab of BIDS or in the logging table/file when logging is enable. I rather prefer using indirect configurations; but I have gotten a client where Env variable are not an option....

Lookup concurrency issue in packages running simultaneously in parallel

I have a system of SSIS packages in which several packages perform the same lookup on the same table. E.g., i have PackageA, PackageB and PackageC all doing a lookup on TableA. All of these packages are spawned by the same PackageD and run frequently. In some cases, there is an issue with concurrency on these lookups. I get the following exception :

"

The ProcessInput method on component "LKP Lookup SecurityID" (6658) failed with error code 0xC004702C. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

"

The hex code of this exception corresponds to the following description : "DTS_E_BUFFERNOTLOCKED. This buffer is not locked and cannot be manipulated." That's as much as i could find on this.

My suspision is that the SSIS engine somehow figures that the lookup in these distinct packages is the same one and builds a shared version of the lookup table in memory. Then there is some sort of a multi-threading issue in accessing this shared memory which leads to the exception above.

Has anyone experienced this? Can someone shed some light on this?

Thanks a lot

-Alex

Just out of curiosity, did you copy-n-paste the lookups across the packages?

Are you using SQL in the lookup (you should be!) or are you simply choosing the table?|||

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

i am using the default setup for the lookup, without messing with SQL. The table is very small (<300 rows) and is loaded into memory entirely. What advantage would i get from modifying SQL? Thanks

|||

chianuri wrote:

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

i am using the default setup for the lookup, without messing with SQL. The table is very small (<300 rows) and is loaded into memory entirely. What advantage would i get from modifying SQL? Thanks

Well, I'm just curious to know if you keep things set up the way you do, but RECREATE each lookup (sounds like it would be a quick task) in each package, what happens then?

As far as using SQL, it is always preferred to use SQL in any component, if possible. Numerous reasons, but the two primary ones are so that you can select just the columns you need, and can filter the rows ahead of time using the power of the SQL Server engine versus SSIS.

For your issue, I was just curious to know what happens if you replace the table selection with SQL. If the same thing happens, then what happens if you alias the lookup table differently in each lookup. (select ... from table a ; select ... from table b ; etc...)|||

chianuri wrote:

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

The fact that you are using a template and your data-flow exists in that template makes me *very* suspicious. Perhaps there is a handle that is replicated across those multiple packages. It could be that you have stumbled upon a bug.

Would it be possible for you to post a repro package? Perhaps one that works (i.e. fails) when run against AdventureWorks?

Thanks

Jamie

|||

Phil Brammer wrote:

As far as using SQL, it is always preferred to use SQL in any component, if possible. Numerous reasons, but the two primary ones are so that you can select just the columns you need, and can filter the rows ahead of time using the power of the SQL Server engine versus SSIS.

Phil is absolutely correct. Don't assume that selecting from the dropdown is the same as issuing a "SELECT * FROM...". As explained here:

SELECT *... or select from a dropdown in an OLE DB Source component?

http://blogs.conchango.com/jamiethomson/archive/2006/02/21/SSIS_3A00_-SELECT-_2A002E002E002E00_-or-select-from-a-dropdown-in-an-OLE-DB-Source-component_3F00_.aspx

-Jamie

|||

Phil Brammer wrote:

Well, I'm just curious to know if you keep things set up the way you do, but RECREATE each lookup (sounds like it would be a quick task) in each package, what happens then?

Actually, it's not going to be that easy in my case as i have a fairly large number of packages in total ( >30), and many of them share lookups. I just gave an oversimplified account of the system in my original description of the problem.

Phil Brammer wrote:

As far as using SQL, it is always preferred to use SQL in any component, if possible. Numerous reasons, but the two primary ones are so that you can select just the columns you need, and can filter the rows ahead of time using the power of the SQL Server engine versus SSIS.

For your issue, I was just curious to know what happens if you replace the table selection with SQL. If the same thing happens, then what happens if you alias the lookup table differently in each lookup. (select ... from table a ; select ... from table b ; etc...)

This suggestion seems to be more manageable in my case (as i won't need to recertify all the metadata downstream from the lookup once i replace it). I'll keep you posted on what happens. I'm still curious as to what exactly causes the error in the first place.

I'm wondering if SP2 fixes this problem since it does fix another issue with the lookup component...

I'd appreciate if anyone else who has experienced anything of this sort or has more details on 0xC004702C (DTS_E_BUFFERNOTLOCKED) responded to this post.

|||

Jamie Thomson wrote:

chianuri wrote:

Phil,

i used a template package that had the lookup defined. I generated a new package ID for each of the derived packages.

The fact that you are using a template and your data-flow exists in that template makes me *very* suspicious. Perhaps there is a handle that is replicated across those multiple packages. It could be that you have stumbled upon a bug.

Would it be possible for you to post a repro package? Perhaps one that works (i.e. fails) when run against AdventureWorks?

Thanks

Jamie

Thanks for the reply Jamie. I'll put something together and post it, but it is difficult to replicate this bug since it happens non-deterministically (at least it looks that way on the surface).

|||

chianuri wrote:

Thanks for the reply Jamie. I'll put something together and post it, but it is difficult to replicate this bug since it happens non-deterministically (at least it looks that way on the surface).

I'll see if I can recreate it as well.

If you need somewhere to post your package, you can contact me or very likely Jamie as well. (Don't want to volunteer him outright! )

I know we are all interested to know if this is a bug and if so, is there a workaround.

Thanks,
Phil|||

Phil Brammer wrote:

chianuri wrote:

Thanks for the reply Jamie. I'll put something together and post it, but it is difficult to replicate this bug since it happens non-deterministically (at least it looks that way on the surface).

I'll see if I can recreate it as well.

If you need somewhere to post your package, you can contact me or very likely Jamie as well. (Don't want to volunteer him outright! )

I know we are all interested to know if this is a bug and if so, is there a workaround.

Thanks,
Phil

Thanks for your replies, Phil.

I started converting my lookups to SELECTs, as both you and Jamie suggested and will post the results here.

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

|||

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.|||

Phil Brammer wrote:

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.

Yes, this is exactly what i'm talking about! I knew someone must've experienced this problem before....

Would you happen to have an update on that request from the SSIS team? Are they planning for a hotfix anytime soon?

|||

chianuri wrote:

Phil Brammer wrote:

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.

Yes, this is exactly what i'm talking about! I knew someone must've experienced this problem before....

Would you happen to have an update on that request from the SSIS team? Are they planning for a hotfix anytime soon?

No. No update because they don't feel there's an issue, until now, perhaps. You should visit my Connect submission and post your feedback, with detailed notes.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=241901|||

Phil Brammer wrote:

chianuri wrote:

Phil Brammer wrote:

chianuri wrote:

Separately, i took a look at the underlying XML for the packages and looks like the lookup components that clash have the same value for the id element (the integer one - i'm assuming the GUID componentID refers to the lookup component's class id, rather than then the instance). That might be the reason for the clash - they all originated from the same template so there was nothing done to them that would change these ids (just as both you and Jamie were guessing).

And I've raised this issue before, but was told that even though the tasks/components have the same GUID, the package's GUID is different and hence should ensure uniqueness. I've always been skeptical of that claim. So pretty much since then, I have not done much copy-n-pasting.

Yes, this is exactly what i'm talking about! I knew someone must've experienced this problem before....

Would you happen to have an update on that request from the SSIS team? Are they planning for a hotfix anytime soon?

No. No update because they don't feel there's an issue, until now, perhaps. You should visit my Connect submission and post your feedback, with detailed notes.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=241901

done... hope they respond to it

|||

I hope they respond. This definately looks like a bug.

I've never liked the fact that they don't generate new IDs for all the package objects even though I didn't have a real good reason for it. it just didn't "smell" very good. How hard is it to loop over the executables collection and components collections and change all the IDs?

Good spot guys!! Although shame we can't get a repro.

-Jamie

Lookup Component Feature

Has anyone seen this strange behaviour?

I have a package which loads the fact data from Stage into Warehouse database. This packages normally handles early arriving facts. In that package I use lookup to check the dims which exists, and where they don't I populate the dimension and use the surrogate key to load the facts. This works fine.

I had a request to load 7 years worth of historical data. Instead of re-writing the package I took the package which handles early arriving facts and deleted the section which handles early arriving facts. I knew all the dimensions already exists and I don’t want to hinder the performance when I load millions of rows. During testing I found something very interesting.

If you have configured error path in the lookup component and removed the error path later, the package will NOT fail (won't produce error) even if the lookup can't find matching values.

Correct Behaviour Example 1:
[1] Stage fact table has 2 records, with product code 1 and 2.
[2] Warehouse Product table has only product code 1.
[3] Source - Lookup - Destination in the data flow task. Error port on lookup is not configured.
[4] From source we read 2 records, and the package will fail at lookup as it can't find Product Code 2.

Correct Behaviour Example 2:
[1] Stage fact table has 2 records, with product code 1 and 2.
[2] Warehouse Product table has only product code 1.
[3] Source - Lookup - Destination in the data flow task. Error port on lookup is configured to go to RowCount.
[4] From source we read 2 records, and the package will run successfully. It will put one record into warehouse table and send the invalid record into RowCount.

Incorrect Behaviour Example 3:
[1] Stage fact table has 2 records, with product code 1 and 2.
[2] Warehouse Product table has only product code 1.
[3] Source - Lookup - Destination in the data flow task. Delete the configured error port from lookup.
[4] From source we read 2 records, and the package will run successfully. It will put one record into warehouse table and discard the other.

My understanding if the error port is NOT configured as shown in example 2, it should fail as shown in example 1.

Am I missing a point or is this suppose to be a correct behaviour or is it a bug?

Thanks

Sutha

If you want Lookup to fail, set error disposition to Fail Component, instead of redirecting the row to error output - the error disposition controls the component fail/not-fail behavior.

If nothing is connected to error path, the redirected rows are lost, but this is not signalled as failure - there are legitimate reasons to have packages where you don't care about such rows, and want to continue package without signalling an error.