query equivalent: perform

Showing posts with label perform. Show all posts

Monday, March 26, 2012

Looping through sysDatabases to perform maintenance

from sql 2000:
I need to generate some metrics on the size of transaction logs on all
databases for a given server. I was thinking I could loop through
sysDatabases to get the name of every database. However, when I try to do
something like:
...
While @.@.Fetch_Status = 0 Begin
Use @.cDBName
--Do some stuff here...
Fetch next From curDBList Into @.cDBName
End
the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
where I can dynamically specifiy the name of the DB with the USE command?
thanks in advance.mystical potato (mysticalpotato@.discussions.microsoft.com) writes:
> I need to generate some metrics on the size of transaction logs on all
> databases for a given server. I was thinking I could loop through
> sysDatabases to get the name of every database. However, when I try to do
> something like:
> ...
> While @.@.Fetch_Status = 0 Begin
> Use @.cDBName
> --Do some stuff here...
> Fetch next From curDBList Into @.cDBName
> End
> the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
> where I can dynamically specifiy the name of the DB with the USE command?
The easiest is probably to to use sp_MSforeachdb. Here is a fairly
stupid example:
sp_MSforeachdb 'SELECT ''?'', COUNT(*) FROM sysobjects'
Note that sp_MSforeachdb is not a documented function, and thus not
supported. Nevertheless, it's fairly popular.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
A different approach may be to use the Perfmon counters to track this,
especially if you wish to pinpoint a sudden increase. A step further along
the system monitoring approach would be to look into using MOM to gather and
report this for you.
John
"mystical potato" <mysticalpotato@.discussions.microsoft.com> wrote in
message news:D5DE41B3-6065-422E-8C02-DFFE9167C213@.microsoft.com...
> from sql 2000:
> I need to generate some metrics on the size of transaction logs on all
> databases for a given server. I was thinking I could loop through
> sysDatabases to get the name of every database. However, when I try to do
> something like:
> ...
> While @.@.Fetch_Status = 0 Begin
> Use @.cDBName
> --Do some stuff here...
> Fetch next From curDBList Into @.cDBName
> End
> the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
> where I can dynamically specifiy the name of the DB with the USE command?
> thanks in advance.|||Try using:
dbcc sqlperf(logspace)
AMB
"mystical potato" wrote:

> from sql 2000:
> I need to generate some metrics on the size of transaction logs on all
> databases for a given server. I was thinking I could loop through
> sysDatabases to get the name of every database. However, when I try to do
> something like:
> ...
> While @.@.Fetch_Status = 0 Begin
> Use @.cDBName
> --Do some stuff here...
> Fetch next From curDBList Into @.cDBName
> End
> the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
> where I can dynamically specifiy the name of the DB with the USE command?
> thanks in advance.

Friday, March 23, 2012

Loop with calculation

I have a table which contains numerical data in a field called active_en_del. I need to loop through this table and perform a calculation where row 2 minus row 1 = and store into another field; row 3 - row 2 = and store into another field etc. How would I perform this? Thanks

I am not sure which DB you are using. You also say that you want to store the difference in different "field"s. I am not sure if by fields, you mean columns or what. Using this below approach, you can get all the results in rows:

Assuming sql server 2005, you can get rownumbers for all the rows using row_number() over(order by id)

and then do a self join.

Code Snippet

select a.col1-b.col1 from

(select col1, row_number() over(order by id) as rowid
from tblData)
a,

(select col1, row_number() over(order by id) as rowid
from tblData)

where a.rowid=b.rowid+1

|||My appology for not specifying. Yes it is sql 2005 and it a calculation on the same column. Thanks for the above information.sql

Wednesday, March 21, 2012

Loop through each record and then each field within each record

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.

Something like this maybe using a cursor or something else:

For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next

So below, all I need to do is figure out how to loop through each column for the current record in the cursor

AS

DECLARE Create_Final_Table CURSOR FOR

SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1

OPEN Create_Final_Table

FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2

WHILE @.@.FETCH_STATUS = 0
BEGIN

@.Chapter = chapter for this record

For each column in current record <-- not sure how to code this part is what I'm referring to

do some stuff here using sql for the column I'm on for this row

Next

Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record

Case 13

Insert Record
Insert Record
Insert Record

Case 11

Insert Record
Insert Record
Insert Record

Case 12

Insert Record
Insert Record
Insert Record

END

close Create_Final_Table
deallocate Create_Final_TableI need to essentially do 2 loops.Light fuse...One loops through each record and then inside each record row...stand back...I want to perform an insert on each column...cover ears...DECLARE Create_Final_Table CURSOR FOR

SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1

OPEN Create_Final_Table

FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2

WHILE @.@.FETCH_STATUS = 0
BEGIN

@.Chapter = chapter for this record

For each column in current record <-- not sure how to code this part is what I'm referring to

do some stuff here using sql for the column I'm on for this row

Next

Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record

Case 13

Insert Record
Insert Record
Insert Record

Case 11

Insert Record
Insert Record
Insert Record

Case 12

Insert Record
Insert Record
Insert Record

END

close Create_Final_Table
deallocate Create_Final_Table
KABOOM!!!!!!!!!!

Why are you doing this?|||Are you trying to normalize this beast? If so, I'd do one insert operation per column in the original table. Fast, easy, clear, simple... What's not to like?

-PatP

Loop through each record and then each field within each record

Next
Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record
Case 13
Insert Record
Insert Record
Insert Record
Case 11
Insert Record
Insert Record
Insert Record
Case 12
Insert Record
Insert Record
Insert Record
END
close Create_Final_Table
deallocate Create_Final_Table

Also, if you think there is a better way to do this, let me know.

Are you inserting from EBN_TEMP1 into multiple tables? If so then you can just use series of INSERT...SELECT statements. You need to reference the column you need in each SELECT statement.|||

I have to take every record from my select, cycle through each. So let's say I cycle to the first record in my cursor. I need to then cycle through each field in that row and take that field and do something with it.

Then move on to the next row, cycle through it's fields one by one and so on till I have done this for every row in my cursor. I just don't know how to cycle and reference each column in a unique row after each iteration of my cursor's rows

What I'll be doing wtih each colum is taking the value and inserting it into another table with some other values I'll specify in a select.

|||There must be a way to do a loop to go through each field in a cursor row, but I haven't come up with any and have searched internet forever. This is shocking that nobody has ever brought this up. All they talk about is looping through a cursor's rows or just rows in general, not how to take a row and loop through to do something with every single column (field) in the row. I have a good reason for this need so please don't ask why if you're tempted to.|||

I'm not trying to be rude whatsoever but to me that's inefficient to create multiple inserts and selects. But of course you probably didn't know that those selects and inserts would be inserting the same values, only the field value is changing in the statement at each iteration. So that's why I don't want to basically rewrite the same insert and select. I just need to loop through each and move in the value to a parameter in my insert statement

|||

SQL is not a procedural language so it is best to approach the problem with a set oriented mindset. And this is often hard to do. So if you can perform the operation efficiently using DMLs alone it is much more efficient for the engine and it is also easier for you to maintain the code. Let's take an example. (You have to provide some examples as to what you are doing in the insert. You didn't answer my question about whether you are inserting into multiple tables)

insert into t1 (f1, f2)

select f1, f2 -- any computations on the columns can be done here

from tbl

....

insert into t1 (f3, f4)

select f3, f4 -- any computations on the columns can be done here

from tbl

....

So there is nothing like looping through each column. There simply isn't any construct in TSQL or similar procedural languages in RDBMSes. On the other hand if you want to unpivot the results then you can do that using UNPIVOT operator in SQL Server 2005 or use SQL again. To use SQL to unpivot the operation of converting columns to rows then you can do something like below:

-- traditional SQL way

select f1

from tbl

...

union all

select f2

from tbl

....

-- another less obvious method

select case c.c when 1 then f1 when 2 then f2 end as f

from tbl

cross join (select 1 union all select 2) as c(c)

If you do not want to repeat the query multiple times then you can define a view or inline table-valued function or temporary table or table variables and use it instead. So there are many ways to avoid duplication of code. Best is to describe your problem rather than showing procedural code since there are many ways to perform the same set of operations in SQL much more efficiently and elegantly.

|||

My insert will look lik this and all go into one table because that table will end up being the flat file I create

Insert into table1 'a1', 'b1', @.ColumnName, @.ColumnValue, 'IO'

so for each column in the row, I have to insert it as a separate record into my final table.

Yes, this is inefficient but I have to do this for our stupid ERP system which whose UI only can map updates based on individual field records from a flat file....don't ask me why, it's retarted. they will take my flat file an use it in conjunctiuon with the ERP Import GUI to do so, I just have to create the flat file. Before the process was:

1) receive txt comma delimited file from our vendor

2) Parse it out into an MS Access Table

3) Create an individual record for each column in each row and include the AccountID with it and some other static values

4) save it as a fixed length flat file

Now I'm automating this process for them using SQL Server 2005 Integration Services. My flow is like this:

1) Use Flat File Source to import the comma delimmeted txt file (650,000 records)

2) Use Conditional Split to determine which records to filter out

3) Use OLE DB Destination Editor to move in the records to a table

4) Use a SQL TASK to code the splitting out of each field of each row into a new record in my final table. The final table will be used to create the fixed length flat file in the end.

#4 is what I'm trying to do. I have to include the following fields for each record in my final table:

AccountID, 'a1', 'b1', ColumnName, ColumnValue

So in other words for each row in my table that the OLE DB added my records to, I then have to split out each column for each row into a final table including the account D for every row.

I hope this makes sense, it's not as confusing as it seems.

|||

so expanding on my last post, this may give you a sense:

Let's say the OLE DB moves my records into a table initially for step 3. The table now looks something like this:
Acct # Zip Phone Addr
11223 23232 333-444-5555 6556 Duns Rd.
12345 34343 222-444-3333 1000 Aspire Blvd.
I need to create a record using the Acct # and column for each column as well as append some other values like this into a final table. That final table will be a flat file in the end, I just need to figure out how to get this done first.
11223 23232 othervalue1 othervalue2
11223 333-444-5555 othervalue1 othervalue2
11223 6556 Duns Rd. othervalue1 othervalue2
12345 34343 othervalue1 othervalue2
12345 222-444-3333 othervalue1 othervalue2
12345 1000 Aspire Blvd. othervalue1 othervalue 2

|||If you are using SSIS then there is really no reason to denormalize the data in SQL Server. You can just do it in SSIS. Look at the foreach loop container in SSIS. This should allow you to loop through each column. If you have more questions about SSIS please post in the SQL Server Integration Services forum.|||ok, so then if I use the for each, how do I add my sql statement and have it refer to each column for the row I'm on?|||No. You get the data from the table as is and then perform the transformation on the client side. This is easier to do. For example, if you get a datareader for the results then you can use the columns collection with foreach container and loop through each column. If you post the question in the SSIS forum you will get more solutions.|||thanks so much|||

I am following your advice on an SSIS package I have that must evaluate each record. The issue I am having is that the dataReader destination is far slower then the recordset destination. Problem is I can not figure out how to get data from the record set.

Loop through each record and then each field within each record

Also, if you think there is a better way to do this, let me know.

Are you inserting from EBN_TEMP1 into multiple tables? If so then you can just use series of INSERT...SELECT statements. You need to reference the column you need in each SELECT statement.|||

What I'll be doing wtih each colum is taking the value and inserting it into another table with some other values I'll specify in a select.

|||

insert into t1 (f1, f2)

select f1, f2 -- any computations on the columns can be done here

from tbl

....

insert into t1 (f3, f4)

select f3, f4 -- any computations on the columns can be done here

from tbl

....

-- traditional SQL way

select f1

from tbl

...

union all

select f2

from tbl

....

-- another less obvious method

select case c.c when 1 then f1 when 2 then f2 end as f

from tbl

cross join (select 1 union all select 2) as c(c)

|||

My insert will look lik this and all go into one table because that table will end up being the flat file I create

Insert into table1 'a1', 'b1', @.ColumnName, @.ColumnValue, 'IO'

so for each column in the row, I have to insert it as a separate record into my final table.

1) receive txt comma delimited file from our vendor

2) Parse it out into an MS Access Table

3) Create an individual record for each column in each row and include the AccountID with it and some other static values

4) save it as a fixed length flat file

Now I'm automating this process for them using SQL Server 2005 Integration Services. My flow is like this:

1) Use Flat File Source to import the comma delimmeted txt file (650,000 records)

2) Use Conditional Split to determine which records to filter out

3) Use OLE DB Destination Editor to move in the records to a table

4) Use a SQL TASK to code the splitting out of each field of each row into a new record in my final table. The final table will be used to create the fixed length flat file in the end.

#4 is what I'm trying to do. I have to include the following fields for each record in my final table:

AccountID, 'a1', 'b1', ColumnName, ColumnValue

So in other words for each row in my table that the OLE DB added my records to, I then have to split out each column for each row into a final table including the account D for every row.

I hope this makes sense, it's not as confusing as it seems.

|||

so expanding on my last post, this may give you a sense:

Monday, March 19, 2012

Lookup transformation using effective dates

Hi,

I need to perform a lookup based on a business key and a date in the source that must be between an effective from and effective to date in the reference table. I've been able to achieve this by updating the SQL caching command on the advanced tab but the performance is very slow. I have 6 lookups of this type in the data flow with a source SQL statement that returns approx 1 million rows and this package takes over 90 minutes to run.

The caching SQL command now looks like this

select * from
(select * from [ReferenceTable]) as refTable
where [refTable].[Key] = ? and ? BETWEEN [refTable].[StartDate] AND [refTable].[EndDate]

and I've set up the parameters so that the business key is the first parameter and the source date is the second.

I have another lookup in the flow that does a straight equality comparison using 2 columns and the Progress tab shows that this lookup is cached (even though I haven't enabled it on the Advanced tab of the transformation editor) but none of the other lookups (using the date) appear to be cached, even though I have enabled them to be.

Can anyone suggest how I can improve the performance?

Thanks.

Hi,

When u use 'caching SQL command', caching can be either partial or none. In the 'none' mode each time it will execute the sql command for input. In the 'partial' mode, it will only cache the previously executed sql command results, so it won't cache any data at the outset.

Other alternative approach is, join ur input with the [reference table] using the key (don't use date). U will give multiple records. Use a conditional split to compare the date with start and end date. The output will be what u want.

|||

Thanks for the tip. Initially, the caching was partial so I would have expected the lookup speed to increase as the process went on ,as more and more of the target reference records were loaded, but this didn't seem to be the case.

I've now changed the package so that it joins directly onto the reference table and the speed has increased dramatically.

Thanks.

Lookup transformation

Hi Everyone,

I'm trying to perform a lookup transformation. But the deal is, I have this one value that I am passing into the transformation, but I would like to gather all values that match the value I put in....does the lookup transformation do this? I tried it, and it appears as if it only returns one value for the one input. After the lookup, I have an access OLE DB destination setup...so I can capture all those values that corresponds to that one value I passed into the lookup. Does anyone have any ideas on how I can go about this?

Thanks!

Not quite sure what you want to do here. The LOOKUP works on a row at a time in isolation from all other rows.

Do you mean you want to return multiple values from your lookup cache? it doesn't do that, it only returns one value. The first one that it sees.

It sounds like you want to be looking at the MERGE JOIN component.

-Jamie

Monday, March 12, 2012

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

Monday, March 26, 2012

Looping through sysDatabases to perform maintenance

Friday, March 23, 2012

Loop with calculation

Wednesday, March 21, 2012

Loop through each record and then each field within each record

Loop through each record and then each field within each record

Loop through each record and then each field within each record

Monday, March 19, 2012

Lookup transformation using effective dates

Lookup transformation

Monday, March 12, 2012

Lookup concurrency issue in packages running simultaneously in parallel

query equivalent

Blog Archive

About Me