Lineage ID Errors

I am running an SSIS package and I keep getting this error when it gets to a the dataflow task. I have a bit of an idea of what it means, but I can't figure out how to fix it. IF anybody could explain this error and fixes for it that would be of immense help. Thank you.

Error: 0xC004701C at Load Server Security, DTS.Pipeline: input column "Server" (1434) has lineage ID 1421 that was not previously used in the Data Flow task.
Error: 0xC004706B at Load Server Security, DTS.Pipeline: "component "OLE DB Destination 1" (1420)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error: 0xC004700C at Load Server Security, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Load Server Security: There were errors during task validation.The first error simply says that you have a column in the data flow, Server, that isn't used.

The second error looks to me like there were database changes to that table and now the OLE DB destination needs to be updated. Double click on the OLE DB destination and select the mappings tab. If no changes need to be made, simply click OK.|||

Possible reason could be if your destination table structure or the column data type changed.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=102011&SiteID=1

Thanks

|||You should have a triangle with an exclaimation mark on it. Double click on that component and then mappings, if applicable, to fix it.

Limiting log growth during DTS Package

SQL Server 2000 SP4. I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing and date verification
and loads to a SQL Server DB. Most of the tables are full refresh and
a few are incremental.

Main DW: DwSQL
Staging Area: DwLoadAreaSQL

The DW is about 60 Gigs. The Staging Area is about 80 Gigs. This is
all good.

However, the log file for the staging area is 50 Gigs and I'm trying
to find ways to not require such a large log file. I tried adding a
few "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in the
DTS package but figured out that because it's 1 DTS package it's all 1
transaction. I've thought about breaking it up into multiple DTS
packages and truncating the log between running them but was hoping to
avoid this. To be clear, I know how to shrink DB's and Log
Files...that's not the issue.

Any Ideas? Thanks.On Jul 24, 3:49 pm, davisutt <davis...@.aol.comwrote:

Quote:

Originally Posted by

SQL Server 2000 SP4. I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing and date verification
and loads to a SQL Server DB. Most of the tables are full refresh and
a few are incremental.
>
Main DW: DwSQL
Staging Area: DwLoadAreaSQL
>
The DW is about 60 Gigs. The Staging Area is about 80 Gigs. This is
all good.
>
However, the log file for the staging area is 50 Gigs and I'm trying
to find ways to not require such a large log file. I tried adding a
few "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in the
DTS package but figured out that because it's 1 DTS package it's all 1
transaction. I've thought about breaking it up into multiple DTS
packages and truncating the log between running them but was hoping to
avoid this. To be clear, I know how to shrink DB's and Log
Files...that's not the issue.
>
Any Ideas? Thanks.

Make sure both databases are in bulked log recovery mode. You should
have a step at the end of your DTS to run CHECKPOINT, backup truncate
the log. Also, manaually shrink the log file to your desired log size.

Hope it helps...

MNDBA|||On Jul 28, 4:55 pm, kmounkh...@.gmail.com wrote:

Quote:

Originally Posted by

On Jul 24, 3:49 pm, davisutt <davis...@.aol.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

SQL Server 2000 SP4. I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing and date verification
and loads to a SQL Server DB. Most of the tables are full refresh and
a few are incremental.

>

Quote:

Originally Posted by

Main DW: DwSQL
Staging Area: DwLoadAreaSQL

>

Quote:

Originally Posted by

The DW is about 60 Gigs. The Staging Area is about 80 Gigs. This is
all good.

>

Quote:

Originally Posted by

However, the log file for the staging area is 50 Gigs and I'm trying
to find ways to not require such a large log file. I tried adding a
few "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in the
DTS package but figured out that because it's 1 DTS package it's all 1
transaction. I've thought about breaking it up into multiple DTS
packages and truncating the log between running them but was hoping to
avoid this. To be clear, I know how to shrink DB's and Log
Files...that's not the issue.

>

Quote:

Originally Posted by

Any Ideas? Thanks.

>
Make sure both databases are in bulked log recovery mode. You should
have a step at the end of your DTS to run CHECKPOINT, backup truncate
the log. Also, manaually shrink the log file to your desired log size.
>
Hope it helps...
>
MNDBA- Hide quoted text -
>
- Show quoted text -

Thanks. I think that's what I was looking for. I changed the
recovery mode and shrunk the log size to about 20% of what it was.
I'll run the process and see what kind of growth occurs.

Limiting Content of Progress Tab

Is there any way to limit the content that is placed into the Progress Tab during execution of a package in debug mode?

My problem is that I have a complex package that puts lots of info in there. This package is called in a loop from another package. Each time the package is called, the new info is added to the existing info in the Progress Tab. Eventually the instance of Dev Studio hangs when it gets too much content in that screen.

The only solution would be if I can limit the output content of that screen or turn it off.

All suggestions appreciated.

Dolllar Dude wrote:

Is there any way to limit the content that is placed into the Progress Tab during execution of a package in debug mode?

My problem is that I have a complex package that puts lots of info in there. This package is called in a loop from another package. Each time the package is called, the new info is added to the existing info in the Progress Tab. Eventually the instance of Dev Studio hangs when it gets too much content in that screen.

The only solution would be if I can limit the output content of that screen or turn it off.

All suggestions appreciated.

My word, that isn't good.My only suggestion is that you run the package without debugging (i.e. CTRL-F5).

Microsoft need to be made aware of this problem. [Microsoft follow-up]

-Jamie

|||

Jamie

Thanks for you input. Yes I agree that MS need to know.

In general I like the idea of placing status information into the Progress Tab, however there does need to be some way of turning off or controlling the content.

So far the only other work around is to split the package up into more/smaller packages. It's messy but it works.

I'll check your suggestion to see what sort of content is generated when I run it with CRTL-F5.

Also, FYI, I'm running this on a 4 way Xeon with 4 Gb of RAM.

Thanks for your help.

|||

A further thought.

I had hoped that the good folk at MS might be watching this kind of thread ...

|||

Hi Dolllar Dude,

could you record your request on the connect site? I think we should look into this.

Thanks,

Bob

|||Dollar Dude,
That site is: http://connect.microsoft.com/sqlserver/feedback

Post back here with the link to your submission when done.

Thanks,
Phil|||

I have posted the issue to the Connect site. Here is the link.

Thanks for your comments all.

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

|||

Excellent. And detailed repro steps as well. Very rare. Thanks dollar dude.

-Jamie

|||Thanks for reporting the problem and sharing detailed repro steps. We will investigate this issue and update you what the cause is. And if a fix is needed, we will let you know when it will be available.|||

Hi MS, can you give me an update on the status of this issue pls? Have you been able to replicate the issue? If it hasn't been tested, can you pls indicate when you think this might make the list.

Thanks

Dolllar.

|||We will get to this sometime in August. Thanks for your patience. We will keep you posted with our findings.|||

Thanks Runying

Actually I was pretty surprised that you weren't aware of this issue. Are you aware of the two below? If not I'll log a couple of issues in Connect to show/reproduce.

1 - Convert Data Task incorrectly converts NUMERIC(15) to String or Unicode String - sometimes.

2 - Lookup Task sometimes fails to find the record when using NUMERIC(15) field to lookup.

Dolllar

|||

Dolllar Dude wrote:

Thanks Runying

Actually I was pretty surprised that you weren't aware of this issue. Are you aware of the two below? If not I'll log a couple of issues in Connect to show/reproduce.

1 - Convert Data Task incorrectly converts NUMERIC(15) to String or Unicode String - sometimes.

2 - Lookup Task sometimes fails to find the record when using NUMERIC(15) field to lookup.

Dolllar

It is a known issue that SSIS can choke on NUMERIC fields without a scale. Though I couldn't find a Connect posting for it.

Limiting a users resource ie CPU, disk and/or memory

We are currently using Great Plains. This accounting package runs on SQL Server and we have performance problems running integration manager. The result is that users can not get work done when integration manager is running.
Is there any way I can limit the user running integration manager to a certain amount of CPU time and allocate the rest to the other users?
In Oracle, this is easy - I could just use resource plans, but I don't know if there is a way to do this with SQL Server 2K.
TIA
No, SQL Server does not support the ability to limit the amount of =
resources an application or a connections uses.
--=20
Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =
news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =
SQL Server and we have performance problems running integration manager. =
The result is that users can not get work done when integration manager =
is running.
>=20
> Is there any way I can limit the user running integration manager to a =
certain amount of CPU time and allocate the rest to the other users?
>=20
> In Oracle, this is easy - I could just use resource plans, but I don't =
know if there is a way to do this with SQL Server 2K.
>=20
> TIA

Limiting a users resource ie CPU, disk and/or memory

We are currently using Great Plains. This accounting package runs on SQL Se
rver and we have performance problems running integration manager. The resu
lt is that users can not get work done when integration manager is running.
Is there any way I can limit the user running integration manager to a certa
in amount of CPU time and allocate the rest to the other users?
In Oracle, this is easy - I could just use resource plans, but I don't know
if there is a way to do this with SQL Server 2K.
TIANo, SQL Server does not support the ability to limit the amount of =
resources an application or a connections uses.
--=20
Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =
news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =
SQL Server and we have performance problems running integration manager. =
The result is that users can not get work done when integration manager =
is running.
>=20
> Is there any way I can limit the user running integration manager to a =
certain amount of CPU time and allocate the rest to the other users?
>=20
> In Oracle, this is easy - I could just use resource plans, but I don't =
know if there is a way to do this with SQL Server 2K.
>=20
> TIA

Limiting a users resource ie CPU, disk and/or memory

We are currently using Great Plains. This accounting package runs on SQL Server and we have performance problems running integration manager. The result is that users can not get work done when integration manager is running
Is there any way I can limit the user running integration manager to a certain amount of CPU time and allocate the rest to the other users
In Oracle, this is easy - I could just use resource plans, but I don't know if there is a way to do this with SQL Server 2K
TIANo, SQL Server does not support the ability to limit the amount of =resources an application or a connections uses.
-- Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =SQL Server and we have performance problems running integration manager. = The result is that users can not get work done when integration manager =is running.
> > Is there any way I can limit the user running integration manager to a =certain amount of CPU time and allocate the rest to the other users?
> > In Oracle, this is easy - I could just use resource plans, but I don't =know if there is a way to do this with SQL Server 2K.
> > TIA

Limitations in term of number of tasks and number of columns

Hi,

I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.

To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.

Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?

If not, then do you have any idea why it's so slow ?

Thanks in advance for any answer.
Two things. One, the XML on your package has to be extremely large and cumbersome for the engine to work with. I would imagine this is one source of your slowness. If you can break your package up into smaller packages, that would be much better and likely easier to support.

Two, you might have some success in working offline via the "Work Offline" switch under the SSIS menu in BIDS. That is, perhaps some of the slowness is in validating your data flows against the connections.