bug: name change of connection manager is not propogated through package

create a package with a connection manager

reference the manager with a bunch of tasks

change the name of the manager

stand back in disbelief as you receive the following message

Error: 0xC004800B at Data Flow Task, DTS.Pipeline: Cannot find the connection manager with ID "SourceConnectionExcel" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "runtime connection "OleDbConnection" (8)" in the connection manager collection of "component "Source - 'DCD MANUAL$'" (1)". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.

SSIS? SSUX

What version of SSIS are you using? RTM? SP1? SP2?

I rename connection managers all of the time and never have a problem. SP1 and SP2 depending on which machine I'm on.

|||

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00

|||

You probably generated your package using the Import/Export wizard. The wizard use to reference connection managers in sources and destinations using the names instead of IDs. That would work better if you copy both componets and connections to another package but breaks when you try to rename connection managers.

The problem should be easy to correct: just open the component UI and select the connection again. Feel free to open a bug on the connect site as well.

Thanks,

Bob

BUG: Integration Services Project-can't click on "save Copy of Package As..."

Hi.

I found a possible bug. If I open/create a new Integration Services Project and then try to save a copy of the package to SQL Server I found that for the option to "save Copy of Package As..." is only available if I am in the package itself. If I click (highlight) on the package in the Solution explorer and then click on the File tab, the "save Copy of Package As..." option is not available.

I hope that I explained this well enough.

thanks.

Thanks. This is actually by design.

The File menu in VS relates to open files that are currently selected in the designer. Selecting an object in Solution folder and right-clicking does not open the file, so the File menu in VS is still relating to the currently open and selected object - if any.

Donald

Bug with Renaming Connection

I used the wizard to create a package that loads data from Access into SQL Server 2005. I then saved the package to the File System and added to my project.

I had two connections, one for SQL and one for Access. When I rename either one, the source or destination tasks in the Data Flow task reset to the other connection and/or I get an error that the old connection ID cannot be found.

I solved by opening the dtsx in Notepad and doing a global find/replace for the connection ID's. This is a real hassle.

Are you right-clicking on the connection manager and selecting rename? Or how are you renaming them?|||

That was how I tried to rename, by right-clicking. Also tried within the Properties and in the Package Explorer. Same thing.

|||

Do you want to know another bug. In the Import Data wizard that creates the package, I checked the Source box in the upper left which selected all the tables. I then went into Edit Mappings and checked to Delete Rows in Destination Tables.

However, when I execute the package, it doesn't delete the rows and instead appends the data. It might be because I am importing a lot of tables, because when I tried one table only, it worked. I believe it calls sp_executesql to execute the SQL statement and maybe the sql statement string is too long. Unfortunately, it doesn't warn that anything is incorrect, so one doesn't discover this until after testing. Don't mind bugs, but a bug like this without any kind of warning message is kind of dangerous.

|||

When building a package from the designer, renaming connections is fine, because actually the task stores the connection ID not the name.

I did a quick test with a Wizard built package and it varies, for example a snippet of the Exec SQL Task <SQLTask:SqlTaskData SQLTask:Connection="{5545ACCD-6E14-45EF-9112-2AC292C30609}" shows the use of the ID, so renames should keep working just fine, except that other stuff is doing it wrong.

For example an OLE-DB destination shows <connection id="48" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="DestinationConnectionOLEDB"/></connections> which uses the name.

If I then open the package, and open the UI and close it again for the OLE-DB component, the XMl is changed to this <connection id="48" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{5545ACCD-6E14-45EF-9112-2AC292C30609}"/></connections> which clearly will survive a rename.

So why not log it at http://connect.microsoft.com

By the way, stop using notepad if you have added the package to a project. Just right click the package in the Solution Explore and select View Code. You get a much nicer text editor with better find and replace, and it keeps it all nicely liked to the designer window for the package as well. Much easier for when you do want to hack package XML directly.

bug when running sp at package

Dear all

i have a stored procedure which run in package. I find out that the stored

procedure stop at the running after 1 seconds, but the pacakage reports the

success.

this stored procedure can run at query analyzer normally.

I tried to adjust the command time-out at the execute sql task properties

object to 9999 or 0 or 1800. However, I find out it is useless to extend the

executive time.

The case happens in two sql server 2000 which is installed in win 2k server

and nt server 5.0. So, I guess it is a sql server bug.

Please give me some suggestion. whether a sql server has this bug? how can i

fix it?

thx
alex

my code is as follow.

CREATE PROCEDURE usp_UpdateRGNUPC
AS
declare @.upcVar varchar(13), @.skuVar varchar(9), @.strStore varchar(7), @.tempSql varchar(1000)

declare varTempShop cursor
local
static
FOR select distinct Shop from strmst
OPEN varTempShop
fetch next from varTempShop into @.strStore
while @.@.FETCH_STATUS = 0
begin
declare varTemp cursor
FOR SELECT r.upc, r.sku
FROM rgnupc r
INNER JOIN strmst s ON r.rgnid = s.rgnid
WHERE s.Shop = @.strStore
OPEN varTemp
fetch next from varTemp into @.upcVar, @.skuVar
while @.@.FETCH_STATUS = 0
begin
set @.tempSql = 'select * from ' + @.strStore + '.dbo.invupc where upc = ''' + @.upcVar + ''''
exec(@.tempSql)

if (@.@.rowcount>0)
begin
set @.tempSql = 'Update ' + @.strStore + '.dbo.invupc set sku = ''' + @.skuVar + ''' where upc = ''' + @.upcVar + ''''
exec(@.tempSql)
end
else
begin
set @.tempSql = 'Insert into ' + @.strStore + '.dbo.invupc (upc, sku) values (''' + @.upcVar + ''',''' + @.skuVar + ''')'
exec(@.tempSql)
end

fetch next from varTemp into @.upcVar, @.skuVar
end
close varTemp
deallocate varTemp

fetch next from varTempShop into @.strStore
end
close varTempShop
deallocate varTempShop
GO

one sql server 2000 is service pack 3. another is service pack 4.

Alex

Bug in ProtectionLevel?

Hi all,

I am trying to start a package as a job and this does not work...

Referring to http://support.microsoft.com/?kbid=918760 I have checked the certain settings and I do not have set the ProtectionLevel to another setting then "DontSaveSensitive" - but my default setting in the template is "with encryption"

While starting the package from the package store using the technical user which is also used for the different service accounts all works fine.

After I created a NEW package and have set ProtectionLevel to DontSaveSensitive before doing any other step it works well also being started as a job...

BTW:
Copying objects from one package to another is really a big mess!
Is there hope that this issue will be solved shortly?

any hints why the new package works well?

cheers
Markus

Markus Fischer wrote:

Hi all,

I am trying to start a package as a job and this does not work...

Referring to http://support.microsoft.com/?kbid=918760 I have checked the certain settings and I do not have set the ProtectionLevel to another setting then "DontSaveSensitive" - but my default setting in the template is "with encryption"

While starting the package from the package store using the technical user which is also used for the different service accounts all works fine.

After I created a NEW package and have set ProtectionLevel to DontSaveSensitive before doing any other step it works well also being started as a job...

BTW:
Copying objects from one package to another is really a big mess!
Is there hope that this issue will be solved shortly?

What exactly is the issue?

If you want something improved then mentioning it on here will not get it done. you need to request it at Microsoft Connect.

Markus Fischer wrote:

any hints why the new package works well?

cheers
Markus

I'm guessing that you don't have any password stored in your package. Am i reght?

-Jamie

|||

Hi Jamie,

the "thing" is, that the package does not seem to recon my change of settings regarding the ProtectionLevel.

Otherwise the the fact that the first package does not run but the second does wouldn′t be explainable...

I do not have passwords stored in the package because I am using the Integrated Security in the connections.

Shouldn′t starting the package from the package store using the technical user proof, that the package with the technical user should also work started as a job?

cheers
Markus

|||

If you are using integrated security then there are no passwords stored in the package so it will run fine under DontSaveSensitive (assuming of course that the user has access to all the external sources).

Put simply, if there are no passowrds then there is nothing to be encrypted.

Being able to execute the package manually doesn't prove anything about running it from a job if SQL Server Agent is running as a different user.

-Jamie

|||

Jamie,

to summarise it:

- the technical user has access to all external sources
- there are no passwords used
- the technical user is the one also used for the service
- the technical user also is administrator

I did not have set the ProtectionLevel to DontSaveSensitive in the first steps, after doing it it also did not run as a job.

After building my package from scratch with first step setting the ProtectionLevel to DontSaveSensitive it now works well though I presume that there is a bug in this setting

Thanks for your assistance!

cheers
Markus

BUG in Integration Services

Problem

When you have a SSIS package that contains a connection from a data source, this connection is not updated when the data source changes based on a configuration change.

Situation :

A SSIS solution contains 3 configurations : Development, Test, Production. You can create those configurations in configuration manager of the solution.

The SSIS project contains one Data source. It doesn't really matter what type but I take SQL Server. The database server in development is SQL_DEV, in test is SQL_TEST and in production is SQL_PROD. Initially they are for all configurations the same. You can specify those values by changing the active configuration and then editing the Data source.

In the SSIS package (DTSX), you can create a connection manager based on a Data source.

If you change the Data source, the connection manager is also changed. If you change the Data source by changing the active configuration, the connection manager is not being updated.

If you think this isn't a big issue think big. We have 4 configuration, 10 shared Data sources and 25 DTSX packages. That would give a maximum of 1000 settings (4 x 10 x 25). Using this method it can be reduced to 40 (4 x 10). Of course this is a theoretical but it is very common to have the destination data source re-used on all packages, which still would be 100 settings (4 x 25)

Steps to reproduce

- create a new SSIS project

- In the solution explorer, create a new Data source named TestSource.

- In the connection managers window of Package.dtsx, create a new connection from a Data source.

- Make some changes in to TestSource.ds under the Data Sources. For example change the server or the database.

- Verify that those changes are also in the package.

- in the solution explorer, right click the solution and select configuration manager

- under active solution configuration, create a new configuration named test.

- Set the copy settings from : development

- Verify that Create new project configuration is checked.

- click OK and close.

- Notice that the active configuration is now Test

- Make some changes the Testsource.ds like a different server.

- Verify that those changes are also in the package.

- Make the development configuration as active.

- Notice that the Testsource.ds contains now the original settings.

- You will notice that the connection manager still contains the "test" settings and not the development settings.

- If you create a deployment utility it will still contains the wrong values.

with regards,

Constantijn Enders

Don't use Data Sources. Just use the connection managers and use package configurations to configure them.

Bug cannot export/import any dts package on sql 2000 from 2005 manag. studio

I am connectd to a 2000 database and select import and try to

generate a dts to pull in a flat file... the destination is also a 2000 database.. I used to do this using enterprise manager..

Nothing works you get the error:

The operation could not be completed.

The existsonsqlserver mthod has encountered an ole db error code 0x80004005 (client unable to esablish connection) the sql statement issued has failed.

I can connect fine an open query windows and look at all the tables etc...

You can file bugs here:

http://lab.msdn.microsoft.com/productfeedback/default.aspx

Thanks

Bug cannot export/import any dts package on sql 2000 from 2005 manag. studio

I am connectd to a 2000 database and select import and try to

generate a dts to pull in a flat file... the destination is also a 2000 database.. I used to do this using enterprise manager..

Nothing works you get the error:

The operation could not be completed.

The existsonsqlserver mthod has encountered an ole db error code 0x80004005 (client unable to esablish connection) the sql statement issued has failed.

I can connect fine an open query windows and look at all the tables etc...

You can file bugs here:

http://lab.msdn.microsoft.com/productfeedback/default.aspx

Thanks

Buffer overflow exception in SSIS

I am running a SSIS package which inserts records in 8 tables. After inserting about 280 records I get an error "Buffer overflow". Any help is greatly appreciated.

We'll need more info. What are the sources and destinations? What providers are being used? What transformation objects does the package use? What OS platform are you running on? etc.

Thanks

Donald Farmer

Buffer Exception

When running a package created on my local machine i get no errors at all but when i try to run the same package on the server i get an error specifying Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.

I have tried changing the defaultbuffersize of the data flow task but this makes no difference. I think that a buffer size for a particular column is being exceed but i cannot find anywhere to set this property.

Has anyone else struck this error?

What key things are different between your local development box and your server? Different locale settings, 32 to 64 bit perhaps?

Browser time out: when executing a DTS package

Hello All,
I am am having a strange problem. I am trying to execute a DTS package
from VB .NET (.aspx) page. The DTS package takes 21 minutes to execute.
When the aspx page sends a command to MS SQL server to execute this DTS
package it starts executing. After 8 minutes the .aspx page shows error
"Cannot Find Server" and errors out with a white screen. The
inetresting thing is the DTS package is still being executed in backend
and completes execution successfully.
I have no clue why this is happening because there are other DTS
packages that I am executing through the same .aspx page that takes
about 3 minutes to complete execution and are working absolutely fine.
By the way I have a stored procedure wrapper over every DTS package. So
when i have to execute a DTS package I execute a stored which inturn
executes a DTS package.
Thanks for reading my query. If you have any suggestions please let me
know.
Thank you in advance.The aspx page (or the command object, or whatever you are using to execute
the stored procedure) has a timeout property. When the timeout value is
reached the web page times out.
Can you increase the timeout for that one database call?
Keith Kratochvil
"Al-Pacino" <dipenshahis@.yahoo.com> wrote in message
news:1142435924.265991.289090@.e56g2000cwe.googlegroups.com...
> Hello All,
> I am am having a strange problem. I am trying to execute a DTS package
> from VB .NET (.aspx) page. The DTS package takes 21 minutes to execute.
> When the aspx page sends a command to MS SQL server to execute this DTS
> package it starts executing. After 8 minutes the .aspx page shows error
> "Cannot Find Server" and errors out with a white screen. The
> inetresting thing is the DTS package is still being executed in backend
> and completes execution successfully.
> I have no clue why this is happening because there are other DTS
> packages that I am executing through the same .aspx page that takes
> about 3 minutes to complete execution and are working absolutely fine.
> By the way I have a stored procedure wrapper over every DTS package. So
> when i have to execute a DTS package I execute a stored which inturn
> executes a DTS package.
> Thanks for reading my query. If you have any suggestions please let me
> know.
> Thank you in advance.
>|||Hi Keith,
I tried setting the time out for the command object but it still does
not work right.
It has the exact same behaviour.
Keith Kratochvil wrote:
> The aspx page (or the command object, or whatever you are using to execute
> the stored procedure) has a timeout property. When the timeout value is
> reached the web page times out.
> Can you increase the timeout for that one database call?
> --
> Keith Kratochvil
>
> "Al-Pacino" <dipenshahis@.yahoo.com> wrote in message
> news:1142435924.265991.289090@.e56g2000cwe.googlegroups.com...

Browser Time Out: when executing a DTS package

Hello All,

I am am having a strange problem. I am trying to execute a DTS package
from VB .NET (.aspx) page. The DTS package takes 21 minutes to execute.

When the aspx page sends a command to MS SQL server to execute this DTS

package it starts executing. After 8 minutes the .aspx page shows error

"Cannot Find Server" and errors out with a white screen. The
inetresting thing is the DTS package is still being executed in backend

and completes execution successfully.

I have no clue why this is happening because there are other DTS
packages that I am executing through the same .aspx page that takes
about 3 minutes to complete execution and are working absolutely fine.

By the way I have a stored procedure wrapper over every DTS package. So

when i have to execute a DTS package I execute a stored which inturn
executes a DTS package.

Thanks for reading my query. If you have any suggestions please let me
know.

Thank you in advance.Hi

Have you though about doing this through a job and not waiting?

John

"Al-Pacino" <dipenshahis@.yahoo.com> wrote in message
news:1142436273.071208.313680@.e56g2000cwe.googlegr oups.com...
> Hello All,
> I am am having a strange problem. I am trying to execute a DTS package
> from VB .NET (.aspx) page. The DTS package takes 21 minutes to execute.
> When the aspx page sends a command to MS SQL server to execute this DTS
> package it starts executing. After 8 minutes the .aspx page shows error
> "Cannot Find Server" and errors out with a white screen. The
> inetresting thing is the DTS package is still being executed in backend
> and completes execution successfully.
>
> I have no clue why this is happening because there are other DTS
> packages that I am executing through the same .aspx page that takes
> about 3 minutes to complete execution and are working absolutely fine.
>
> By the way I have a stored procedure wrapper over every DTS package. So
> when i have to execute a DTS package I execute a stored which inturn
> executes a DTS package.
>
> Thanks for reading my query. If you have any suggestions please let me
> know.
>
> Thank you in advance.

Bring File In As Binary Field

I want to have an SSIS package that processes a file in the normal insert, update style. But at the end I want to store the file as a binary field to another table for archive purposes. I am having trouble finding a good way to do this. Any samples, ideas, or articles would be appreciated.I haven't tried this, but my first thought would be reading the file into a variable through a script task, and then using that variable to write the blob to the destination table.|||That is the only method I could come up with as well.|||

Import Column transform is supposed to do this. Could you give it a try? It is not easy to configure as it deos not have a custom UI, but see if the BOL has some info.

Thanks.

|||It sounds like this might do what I need but books on line (like many things) is worthless for explanation of how/what to setup and use this component. Are you aware of other resources for this component?|||A quick Google search turned this up. See if it helps:

http://mgopinath.blogspot.com/search/label/Integration%20Services|||Thanks Phil... my google skills were poor as I was unable to find an article like that.|||

1Dave wrote:

Thanks Phil... my google skills were poor as I was unable to find an article like that.

ssis "import column"

Breakpoint in child-package triggered on first execution only.

I have a child package which is executed several times within the same SSIS ETL. I have placed a break point on one of the child package's tasks, set to trigger on a PreExecute() event. The first time the child package is invoked, the breakpoint is triggered. However, on each successive invocation the breakpoint is ignored. Does anybody know if this behaviour is normal? Thanks in advance!

This is a known issue and we are aware of the bug. Thanks