Remove duplicate records after importing via SSIS

Hello all,
I have some phone logs that I would like to import into table on a daily or
periodic basis. I would like to be able to elliminate any duplicate records
that it imports when it appends it to the table. Is there some T-SQL that I
can run that would help with this situation? I DO have one particular
field that has a unique ID for each record, so it *should* be pretty easy, if
I only knew what I was doing. ;-)
--
SketchySince you have a unique key and existing data, set up the insert using a not
exists clause
insert realtable
select ..
from holdingtable ht
where not exists (select * from realtable rt where rt.keyfield =ht.keyfield)
TheSQLGuru
President
Indicium Resources, Inc.
"sketchy" <sketchy@.discussions.microsoft.com> wrote in message
news:AD538EA5-A55B-45C1-9992-D0AAFD0AD0C6@.microsoft.com...
> Hello all,
> I have some phone logs that I would like to import into table on a daily
> or
> periodic basis. I would like to be able to elliminate any duplicate
> records
> that it imports when it appends it to the table. Is there some T-SQL that
> I
> can run that would help with this situation? I DO have one particular
> field that has a unique ID for each record, so it *should* be pretty easy,
> if
> I only knew what I was doing. ;-)
> --
> Sketchy|||On Thu, 23 Aug 2007 07:22:30 -0700, sketchy
<sketchy@.discussions.microsoft.com> wrote:
>Hello all,
>I have some phone logs that I would like to import into table on a daily or
>periodic basis. I would like to be able to elliminate any duplicate records
>that it imports when it appends it to the table. Is there some T-SQL that I
>can run that would help with this situation? I DO have one particular
>field that has a unique ID for each record, so it *should* be pretty easy, if
>I only knew what I was doing. ;-)
Create a staging table that matches the input data layout. Import the
new data into the staging table. Insert the data from the staging
table into the production table with a WHERE NOT EXISTS test to skip
the duplicates. Truncate the staging table before loading and
processing the next set of data.
The only tricky part left is if there are duplicates in the staging
table itself, as the NOT EXISTS test only prevents inserting them when
they are already there. If the entire row is identical that can be
handled with a DISTINCT. If the row has differences in some column
other than the unique ID you need to provide more information on which
one to choose (as well as raising questions about the entire process.)
Roy Harvey
Beacon Falls, CT|||sketchy,
It would be better if you can avoid inserting the duplicated rows, as
TheSQLGuru stated. You need to work with the group of columns that make the
row unique, let us suppose that they are (c1, c2, c3), then:
delete dbo.t1
where exists (
select *
from dbo.t1 as a
where a.c1 = dbo.t1.c1
and a.c2 = dbo.t1.c2
and a.c3 = dbo.t1.c3
and a.[id] < dbo.t1.[id]
)
-- or
-- 2005
;with cte
as
(
select c1, ..., cn, row_number() over(partition by c1, c2, c3 order by [id])
as rn
from dbo.t1
)
delete cte
where rn > 1;
AMB
"sketchy" wrote:
> Hello all,
> I have some phone logs that I would like to import into table on a daily or
> periodic basis. I would like to be able to elliminate any duplicate records
> that it imports when it appends it to the table. Is there some T-SQL that I
> can run that would help with this situation? I DO have one particular
> field that has a unique ID for each record, so it *should* be pretty easy, if
> I only knew what I was doing. ;-)
> --
> Sketchy|||Wow guys. this is all great information. I never thought of having a
staging table. Let me soak this in a bit and if I have any more questions, I
know who to ask.
--
Sketchy
"Alejandro Mesa" wrote:
> sketchy,
> It would be better if you can avoid inserting the duplicated rows, as
> TheSQLGuru stated. You need to work with the group of columns that make the
> row unique, let us suppose that they are (c1, c2, c3), then:
> delete dbo.t1
> where exists (
> select *
> from dbo.t1 as a
> where a.c1 = dbo.t1.c1
> and a.c2 = dbo.t1.c2
> and a.c3 = dbo.t1.c3
> and a.[id] < dbo.t1.[id]
> )
> -- or
> -- 2005
> ;with cte
> as
> (
> select c1, ..., cn, row_number() over(partition by c1, c2, c3 order by [id])
> as rn
> from dbo.t1
> )
> delete cte
> where rn > 1;
>
> AMB
> "sketchy" wrote:
> > Hello all,
> >
> > I have some phone logs that I would like to import into table on a daily or
> > periodic basis. I would like to be able to elliminate any duplicate records
> > that it imports when it appends it to the table. Is there some T-SQL that I
> > can run that would help with this situation? I DO have one particular
> > field that has a unique ID for each record, so it *should* be pretty easy, if
> > I only knew what I was doing. ;-)
> > --
> > Sketchy|||Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
1. I have my original table, which will be used for my reporting needs,
called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
"Field3" etc. but the main field that has the unique info in it is called
"GlobalCallID"
2. I have created a new table, which will be used for my staging of data,
called 'phonelogstaging'. This database has the EXACT same field names and
types.
3. I've set up SSIS to import my log files into the staging table
("phonelogstaging"). It wipes out any previous data in this staging table,
so there is no possibility of duplicates in this table. Everything is
working good there.
Both tables have a field called GlobalCallID that has the unique number in
it that I should be able to check against.
So considering the above, how would my statement look?
--
Sketchy
"Roy Harvey" wrote:
> On Thu, 23 Aug 2007 07:22:30 -0700, sketchy
> <sketchy@.discussions.microsoft.com> wrote:
> >Hello all,
> >
> >I have some phone logs that I would like to import into table on a daily or
> >periodic basis. I would like to be able to elliminate any duplicate records
> >that it imports when it appends it to the table. Is there some T-SQL that I
> >can run that would help with this situation? I DO have one particular
> >field that has a unique ID for each record, so it *should* be pretty easy, if
> >I only knew what I was doing. ;-)
> Create a staging table that matches the input data layout. Import the
> new data into the staging table. Insert the data from the staging
> table into the production table with a WHERE NOT EXISTS test to skip
> the duplicates. Truncate the staging table before loading and
> processing the next set of data.
> The only tricky part left is if there are duplicates in the staging
> table itself, as the NOT EXISTS test only prevents inserting them when
> they are already there. If the entire row is identical that can be
> handled with a DISTINCT. If the row has differences in some column
> other than the unique ID you need to provide more information on which
> one to choose (as well as raising questions about the entire process.)
> Roy Harvey
> Beacon Falls, CT
>|||Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
1. I have my original table, which will be used for my reporting needs,
called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
"Field3" etc. but the main field that has the unique info in it is called
"GlobalCallID"
2. I have created a new table, which will be used for my staging of data,
called 'phonelogstaging'. This database has the EXACT same field names and
types.
3. I've set up SSIS to import my log files into the staging table
("phonelogstaging"). It wipes out any previous data in this staging table,
so there is no possibility of duplicates in this table. Everything is
working good there.
Both tables have a field called GlobalCallID that has the unique number in
it that I should be able to check against.
So considering the above, how would my statement look?
--
Sketchy
"TheSQLGuru" wrote:
> Since you have a unique key and existing data, set up the insert using a not
> exists clause
> insert realtable
> select ..
> from holdingtable ht
> where not exists (select * from realtable rt where rt.keyfield => ht.keyfield)
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "sketchy" <sketchy@.discussions.microsoft.com> wrote in message
> news:AD538EA5-A55B-45C1-9992-D0AAFD0AD0C6@.microsoft.com...
> > Hello all,
> >
> > I have some phone logs that I would like to import into table on a daily
> > or
> > periodic basis. I would like to be able to elliminate any duplicate
> > records
> > that it imports when it appends it to the table. Is there some T-SQL that
> > I
> > can run that would help with this situation? I DO have one particular
> > field that has a unique ID for each record, so it *should* be pretty easy,
> > if
> > I only knew what I was doing. ;-)
> > --
> > Sketchy
>
>|||Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
1. I have my original table, which will be used for my reporting needs,
called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
"Field3" etc. but the main field that has the unique info in it is called
"GlobalCallID"
2. I have created a new table, which will be used for my staging of data,
called 'phonelogstaging'. This database has the EXACT same field names and
types.
3. I've set up SSIS to import my log files into the staging table
("phonelogstaging"). It wipes out any previous data in this staging table,
so there is no possibility of duplicates in this table. Everything is
working good there.
Both tables have a field called GlobalCallID that has the unique number in
it that I should be able to check against.
So considering the above, how would my statement look?
--
Sketchy
"Alejandro Mesa" wrote:
> sketchy,
> It would be better if you can avoid inserting the duplicated rows, as
> TheSQLGuru stated. You need to work with the group of columns that make the
> row unique, let us suppose that they are (c1, c2, c3), then:
> delete dbo.t1
> where exists (
> select *
> from dbo.t1 as a
> where a.c1 = dbo.t1.c1
> and a.c2 = dbo.t1.c2
> and a.c3 = dbo.t1.c3
> and a.[id] < dbo.t1.[id]
> )
> -- or
> -- 2005
> ;with cte
> as
> (
> select c1, ..., cn, row_number() over(partition by c1, c2, c3 order by [id])
> as rn
> from dbo.t1
> )
> delete cte
> where rn > 1;
>
> AMB
> "sketchy" wrote:
> > Hello all,
> >
> > I have some phone logs that I would like to import into table on a daily or
> > periodic basis. I would like to be able to elliminate any duplicate records
> > that it imports when it appends it to the table. Is there some T-SQL that I
> > can run that would help with this situation? I DO have one particular
> > field that has a unique ID for each record, so it *should* be pretty easy, if
> > I only knew what I was doing. ;-)
> > --
> > Sketchy|||On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
<sketchy@.discussions.microsoft.com> wrote:
>Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
>1. I have my original table, which will be used for my reporting needs,
>called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
>"Field3" etc. but the main field that has the unique info in it is called
>"GlobalCallID"
>2. I have created a new table, which will be used for my staging of data,
>called 'phonelogstaging'. This database has the EXACT same field names and
>types.
>3. I've set up SSIS to import my log files into the staging table
>("phonelogstaging"). It wipes out any previous data in this staging table,
>so there is no possibility of duplicates in this table. Everything is
>working good there.
>Both tables have a field called GlobalCallID that has the unique number in
>it that I should be able to check against.
>So considering the above, how would my statement look?
Assuming that data already in the table does not need to be refreshed,
only new data added:
INSERT phones
SELECT <column list>
FROM phonelogstaging as A
WHERE NOT EXISTS
(SELECT * FROM phones as B
WHERE A.GlobalCallID = B.GlobalCallID)
If the incoming data itself has duplicates, add DISTINCT after the
word SELECT.
If you need to refresh the rest of the columns of matching rows from
the staging data, you would also run the following BEFORE the command
above.
UPDATE phones
SET col1 = A.col1,
col2 = A.col2
FROM phonelogstaging as A
WHERE phones.GlobalCallID = A.GlobalCallID
Roy Harvey
Beacon Falls, CT|||Hi Roy,
Thank you SO MUCH for your quick response.
1. Yes, only new data needs to be added. No data needs to be refreshed, so
that's good.
2. One little wrinkle in the plan is that much to my dismay, it does appear
that the "GlobalCallID" field isn't necessarily a unique number, but I do
have a field adjacent to it ("CallNumber") where no records would ever have
the same combination of the two. How would you ammend your last statement to
accomodate for this? (Ugh, I know... that probably doesn't make things
simpler)
--
Sketchy
"Roy Harvey" wrote:
> On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
> <sketchy@.discussions.microsoft.com> wrote:
> >Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
> >
> >1. I have my original table, which will be used for my reporting needs,
> >called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
> >"Field3" etc. but the main field that has the unique info in it is called
> >"GlobalCallID"
> >
> >2. I have created a new table, which will be used for my staging of data,
> >called 'phonelogstaging'. This database has the EXACT same field names and
> >types.
> >
> >3. I've set up SSIS to import my log files into the staging table
> >("phonelogstaging"). It wipes out any previous data in this staging table,
> >so there is no possibility of duplicates in this table. Everything is
> >working good there.
> >
> >Both tables have a field called GlobalCallID that has the unique number in
> >it that I should be able to check against.
> >
> >So considering the above, how would my statement look?
> Assuming that data already in the table does not need to be refreshed,
> only new data added:
> INSERT phones
> SELECT <column list>
> FROM phonelogstaging as A
> WHERE NOT EXISTS
> (SELECT * FROM phones as B
> WHERE A.GlobalCallID = B.GlobalCallID)
> If the incoming data itself has duplicates, add DISTINCT after the
> word SELECT.
> If you need to refresh the rest of the columns of matching rows from
> the staging data, you would also run the following BEFORE the command
> above.
> UPDATE phones
> SET col1 = A.col1,
> col2 = A.col2
> FROM phonelogstaging as A
> WHERE phones.GlobalCallID = A.GlobalCallID
> Roy Harvey
> Beacon Falls, CT
>|||Just add the new field that DOES make a row unique to the not exists clause:
> INSERT phones
> SELECT <column list>
> FROM phonelogstaging as A
> WHERE NOT EXISTS
> (SELECT * FROM phones as B
> WHERE A.GlobalCallID = B.GlobalCallID
and a.CallNumber = b.CallNumber)
You can use as many fields as you need to ensure uniqueness.
--
TheSQLGuru
President
Indicium Resources, Inc.
"sketchy" <sketchy@.discussions.microsoft.com> wrote in message
news:CCA5542F-5C15-413A-8B8F-D2200C1BEAD3@.microsoft.com...
> Hi Roy,
> Thank you SO MUCH for your quick response.
> 1. Yes, only new data needs to be added. No data needs to be refreshed,
> so
> that's good.
> 2. One little wrinkle in the plan is that much to my dismay, it does
> appear
> that the "GlobalCallID" field isn't necessarily a unique number, but I do
> have a field adjacent to it ("CallNumber") where no records would ever
> have
> the same combination of the two. How would you ammend your last statement
> to
> accomodate for this? (Ugh, I know... that probably doesn't make things
> simpler)
> --
> Sketchy
>
> "Roy Harvey" wrote:
>> On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
>> <sketchy@.discussions.microsoft.com> wrote:
>> >Okay, so here is what I have done. (this is a SQL 2005 DB by the
>> >way...)
>> >
>> >1. I have my original table, which will be used for my reporting needs,
>> >called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
>> >"Field3" etc. but the main field that has the unique info in it is
>> >called
>> >"GlobalCallID"
>> >
>> >2. I have created a new table, which will be used for my staging of
>> >data,
>> >called 'phonelogstaging'. This database has the EXACT same field names
>> >and
>> >types.
>> >
>> >3. I've set up SSIS to import my log files into the staging table
>> >("phonelogstaging"). It wipes out any previous data in this staging
>> >table,
>> >so there is no possibility of duplicates in this table. Everything is
>> >working good there.
>> >
>> >Both tables have a field called GlobalCallID that has the unique number
>> >in
>> >it that I should be able to check against.
>> >
>> >So considering the above, how would my statement look?
>> Assuming that data already in the table does not need to be refreshed,
>> only new data added:
>> INSERT phones
>> SELECT <column list>
>> FROM phonelogstaging as A
>> WHERE NOT EXISTS
>> (SELECT * FROM phones as B
>> WHERE A.GlobalCallID = B.GlobalCallID)
>> If the incoming data itself has duplicates, add DISTINCT after the
>> word SELECT.
>> If you need to refresh the rest of the columns of matching rows from
>> the staging data, you would also run the following BEFORE the command
>> above.
>> UPDATE phones
>> SET col1 = A.col1,
>> col2 = A.col2
>> FROM phonelogstaging as A
>> WHERE phones.GlobalCallID = A.GlobalCallID
>> Roy Harvey
>> Beacon Falls, CT|||It works!!!! ...Thanks guys for all of your help in this. I hope for some
good computer karma to come your way.
One interesting little tidbit is that I was unable to get it to work by
specifying all of the fields in the Select statement. No matter what I did,
it always came back with the error of:
Insert Error: Column name or number of supplied values does not match table
definition.
It was odd because that table was the exact same as the other one. So I
just changed it to "*" and all worked well.
--
Sketchy
"TheSQLGuru" wrote:
> Just add the new field that DOES make a row unique to the not exists clause:
>
> > INSERT phones
> > SELECT <column list>
> > FROM phonelogstaging as A
> > WHERE NOT EXISTS
> > (SELECT * FROM phones as B
> > WHERE A.GlobalCallID = B.GlobalCallID
> and a.CallNumber = b.CallNumber)
> You can use as many fields as you need to ensure uniqueness.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "sketchy" <sketchy@.discussions.microsoft.com> wrote in message
> news:CCA5542F-5C15-413A-8B8F-D2200C1BEAD3@.microsoft.com...
> > Hi Roy,
> >
> > Thank you SO MUCH for your quick response.
> >
> > 1. Yes, only new data needs to be added. No data needs to be refreshed,
> > so
> > that's good.
> >
> > 2. One little wrinkle in the plan is that much to my dismay, it does
> > appear
> > that the "GlobalCallID" field isn't necessarily a unique number, but I do
> > have a field adjacent to it ("CallNumber") where no records would ever
> > have
> > the same combination of the two. How would you ammend your last statement
> > to
> > accomodate for this? (Ugh, I know... that probably doesn't make things
> > simpler)
> > --
> > Sketchy
> >
> >
> > "Roy Harvey" wrote:
> >
> >> On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
> >> <sketchy@.discussions.microsoft.com> wrote:
> >>
> >> >Okay, so here is what I have done. (this is a SQL 2005 DB by the
> >> >way...)
> >> >
> >> >1. I have my original table, which will be used for my reporting needs,
> >> >called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
> >> >"Field3" etc. but the main field that has the unique info in it is
> >> >called
> >> >"GlobalCallID"
> >> >
> >> >2. I have created a new table, which will be used for my staging of
> >> >data,
> >> >called 'phonelogstaging'. This database has the EXACT same field names
> >> >and
> >> >types.
> >> >
> >> >3. I've set up SSIS to import my log files into the staging table
> >> >("phonelogstaging"). It wipes out any previous data in this staging
> >> >table,
> >> >so there is no possibility of duplicates in this table. Everything is
> >> >working good there.
> >> >
> >> >Both tables have a field called GlobalCallID that has the unique number
> >> >in
> >> >it that I should be able to check against.
> >> >
> >> >So considering the above, how would my statement look?
> >>
> >> Assuming that data already in the table does not need to be refreshed,
> >> only new data added:
> >>
> >> INSERT phones
> >> SELECT <column list>
> >> FROM phonelogstaging as A
> >> WHERE NOT EXISTS
> >> (SELECT * FROM phones as B
> >> WHERE A.GlobalCallID = B.GlobalCallID)
> >>
> >> If the incoming data itself has duplicates, add DISTINCT after the
> >> word SELECT.
> >>
> >> If you need to refresh the rest of the columns of matching rows from
> >> the staging data, you would also run the following BEFORE the command
> >> above.
> >>
> >> UPDATE phones
> >> SET col1 = A.col1,
> >> col2 = A.col2
> >> FROM phonelogstaging as A
> >> WHERE phones.GlobalCallID = A.GlobalCallID
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
>
>

Remoting timeout when calling SSIS package execute from a windows service

When running an integration services package from a windows service I get the "Object ... has been disconnected or does not exist at the server." exception after aproximately six minutes of execution.

This is *not* my windows service failing. I can loop indefinately while tracing to a log file within the service and it will run forever. While calling the mypackage.execute(...) method however, after six minutes (give or take) the exception is thrown...

my code looks something like this:
<code>
dim foo as Microsoft.SqlServer.Dts.Runtime.Application
mypackage = foo..LoadPackage(strimportPkgFilename, pkgevents)
results = myPackage.Execute(Nothing, Nothing, pkgevents, Nothing, Nothing)
</code>

<error>
A first chance exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll
Exception in: frmMyForm.DoImports
Message: Object '/b76f98a0_5bd9_49d8_a524_eeb49d55b303/bqbhkjnaofq_ifr_cwz+srid_1.rem' has been disconnected or does not exist at the server.
</error>

oddly, this same code works perfectly if I run it within a windows form application no matter how long it takes.

It also runs fine if the package can complete in under six minutes.

Any suggestions?

Mark

To give a little update, I'm still having this problem despite exhausting works around I've come up with.

* I've threaded the call to package.Execute, checking its status in a while loop every 60 seconds until it finishes. Still encounter the exception. This behaves as though it should work. The status and execution appear to work asynchronously.

* I've threaded the call to my service, calling my own status function which tests the status of shared MyPackage, every 60 seconds. Still encounter the remoting exception after ~6 minutes.

Every piece is busy making requests through the whole chain of accessible interactions frequently enough to maintain all known remoting timeout constraints and the problem persists.

It confuses me why the same code works in a windows form, but does not work in a windows service. The exception is defiantly being thrown from within package.Execute. I can loop indefinitely within the service without calling package Execute. I can also catch and disregard the exception and still provide status to the client within the service.

Looking for suggestions...

|||

SSIS does not use remoting. The exception is caused by remoting however, so you need to find out who introduced remoting? My ideas

1) you use remoting as link between client and server - then the exception is between the client and the service, which does not fit with the statement that it is thrown from within package.Execute and you can catch this exception (but did you try?)

2) you create multiple application domains in the service - if you do this, make sure you only use SSIS from default application domain. If you run the package from non-default application domain, some SSIS objects can be created in that app domain, but some in default app domain (a lot of SSIS is native code, and it is not aware of app-domains), which may cause failures like this one.

Remoting timeout when calling SSIS package execute from a windows service

When running an integration services package from a windows service I get the "Object ... has been disconnected or does not exist at the server." exception after aproximately six minutes of execution.

This is *not* my windows service failing. I can loop indefinately while tracing to a log file within the service and it will run forever. While calling the mypackage.execute(...) method however, after six minutes (give or take) the exception is thrown...

my code looks something like this:
<code>
dim foo as Microsoft.SqlServer.Dts.Runtime.Application
mypackage = foo..LoadPackage(strimportPkgFilename, pkgevents)
results = myPackage.Execute(Nothing, Nothing, pkgevents, Nothing, Nothing)
</code>

<error>
A first chance exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll
Exception in: frmMyForm.DoImports
Message: Object '/b76f98a0_5bd9_49d8_a524_eeb49d55b303/bqbhkjnaofq_ifr_cwz+srid_1.rem' has been disconnected or does not exist at the server.
</error>

oddly, this same code works perfectly if I run it within a windows form application no matter how long it takes.

It also runs fine if the package can complete in under six minutes.

Any suggestions?

Mark

To give a little update, I'm still having this problem despite exhausting works around I've come up with.

* I've threaded the call to package.Execute, checking its status in a while loop every 60 seconds until it finishes. Still encounter the exception. This behaves as though it should work. The status and execution appear to work asynchronously.

* I've threaded the call to my service, calling my own status function which tests the status of shared MyPackage, every 60 seconds. Still encounter the remoting exception after ~6 minutes.

Every piece is busy making requests through the whole chain of accessible interactions frequently enough to maintain all known remoting timeout constraints and the problem persists.

It confuses me why the same code works in a windows form, but does not work in a windows service. The exception is defiantly being thrown from within package.Execute. I can loop indefinitely within the service without calling package Execute. I can also catch and disregard the exception and still provide status to the client within the service.

Looking for suggestions...

|||

SSIS does not use remoting. The exception is caused by remoting however, so you need to find out who introduced remoting? My ideas

1) you use remoting as link between client and server - then the exception is between the client and the service, which does not fit with the statement that it is thrown from within package.Execute and you can catch this exception (but did you try?)

2) you create multiple application domains in the service - if you do this, make sure you only use SSIS from default application domain. If you run the package from non-default application domain, some SSIS objects can be created in that app domain, but some in default app domain (a lot of SSIS is native code, and it is not aware of app-domains), which may cause failures like this one.

Remote SSIS vs Domain\User: Access is Denied (0x80070005)

What OS permissions do I need to give a domain user to effectively connect to a remote instance of Integration Services?

I keep getting the following message:

Cannot connect to SQLDEV01
Failed to retreive data for this request.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (Microsoft.SqlServer.ManagedDTS)

I have already performed the Windows 2003 steps outlined in the "Eliminating the Access is Denied" error located at http://msdn2.microsoft.com/en-us/library/aa337083.aspx

I have no problem if the user is a local Administrator (go figure).

The MSDN page lacks another steps needed on W2K3 (not sure about XP) - add the account to Distributed COM Users group. (The page is being updated).|||

Yes, I have done this, and gave the Domain\User the DCOM permissions upon the MsDtsSvr object. The Distributed COM Users step is actually included on ths webpage.

Thanks for responding. Perhaps there is another step missing?

|||

JFoushee wrote:

The Distributed COM Users step is actually included on ths webpage.

Not really (if we get the same copy of http://msdn2.microsoft.com/en-us/library/aa337083.aspx).

The page talks about configuring security for MsDtsServer application, but on Windows 2003 Server and 64-bit XP machine there is another global per-machine setting: in DCOMCNFG right click My Computer, select Properties, find COM Security page and inspect both Edit Limits settings: they should allow the user to access the machine. The simplest way to do it is to add user to Distributed COM Users user group.

|||

I believe I got it to work...

One the webpage http://msdn2.microsoft.com/en-us/library/aa337083.aspx, under "To configure rights for remote users on Windows Server 2003"...

replace step 9 with "Click OK to close the dialog box."

Add a step 9.1 with the following text: "On the same Security tab, under Access Permissions, select Customize, then click Edit to open the Access Permission dialog box."

Add a step 9.2 with the following text: "In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Access, and Remote Access. The easiest is to add the local DCOM Distributed Users group. "

Add a step 9.3 with the following text: "Click OK to close the dialog box. Close the MMC snap-in."

Step 10 stays as-is: "Restart the Integration Services service."

|||

Thanks a lot.

Finally I can connect to SSIS.

Remote SSIS vs Domain\User: Access is Denied (0x80070005)

What OS permissions do I need to give a domain user to effectively connect to a remote instance of Integration Services?

I keep getting the following message:

Cannot connect to SQLDEV01
Failed to retreive data for this request.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (Microsoft.SqlServer.ManagedDTS)

I have already performed the Windows 2003 steps outlined in the "Eliminating the Access is Denied" error located at http://msdn2.microsoft.com/en-us/library/aa337083.aspx

I have no problem if the user is a local Administrator (go figure).

The MSDN page lacks another steps needed on W2K3 (not sure about XP) - add the account to Distributed COM Users group. (The page is being updated).|||

Yes, I have done this, and gave the Domain\User the DCOM permissions upon the MsDtsSvr object. The Distributed COM Users step is actually included on ths webpage.

Thanks for responding. Perhaps there is another step missing?

|||

JFoushee wrote:

The Distributed COM Users step is actually included on ths webpage.

Not really (if we get the same copy of http://msdn2.microsoft.com/en-us/library/aa337083.aspx).

The page talks about configuring security for MsDtsServer application, but on Windows 2003 Server and 64-bit XP machine there is another global per-machine setting: in DCOMCNFG right click My Computer, select Properties, find COM Security page and inspect both Edit Limits settings: they should allow the user to access the machine. The simplest way to do it is to add user to Distributed COM Users user group.

|||

I believe I got it to work...

One the webpage http://msdn2.microsoft.com/en-us/library/aa337083.aspx, under "To configure rights for remote users on Windows Server 2003"...

replace step 9 with "Click OK to close the dialog box."

Add a step 9.1 with the following text: "On the same Security tab, under Access Permissions, select Customize, then click Edit to open the Access Permission dialog box."

Add a step 9.2 with the following text: "In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Access, and Remote Access. The easiest is to add the local DCOM Distributed Users group. "

Add a step 9.3 with the following text: "Click OK to close the dialog box. Close the MMC snap-in."

Step 10 stays as-is: "Restart the Integration Services service."

|||

Thanks alot.

Finally I can connect to SSIS.

Remote SSIS vs Domain\User: Access is Denied (0x80070005)

What OS permissions do I need to give a domain user to effectively connect to a remote instance of Integration Services?

I keep getting the following message:

Cannot connect to SQLDEV01
Failed to retreive data for this request.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (Microsoft.SqlServer.ManagedDTS)

I have already performed the Windows 2003 steps outlined in the "Eliminating the Access is Denied" error located at http://msdn2.microsoft.com/en-us/library/aa337083.aspx

I have no problem if the user is a local Administrator (go figure).

The MSDN page lacks another steps needed on W2K3 (not sure about XP) - add the account to Distributed COM Users group. (The page is being updated).|||

Yes, I have done this, and gave the Domain\User the DCOM permissions upon the MsDtsSvr object. The Distributed COM Users step is actually included on ths webpage.

Thanks for responding. Perhaps there is another step missing?

|||

JFoushee wrote:

The Distributed COM Users step is actually included on ths webpage.

Not really (if we get the same copy of http://msdn2.microsoft.com/en-us/library/aa337083.aspx).

The page talks about configuring security for MsDtsServer application, but on Windows 2003 Server and 64-bit XP machine there is another global per-machine setting: in DCOMCNFG right click My Computer, select Properties, find COM Security page and inspect both Edit Limits settings: they should allow the user to access the machine. The simplest way to do it is to add user to Distributed COM Users user group.

|||

I believe I got it to work...

One the webpage http://msdn2.microsoft.com/en-us/library/aa337083.aspx, under "To configure rights for remote users on Windows Server 2003"...

replace step 9 with "Click OK to close the dialog box."

Add a step 9.1 with the following text: "On the same Security tab, under Access Permissions, select Customize, then click Edit to open the Access Permission dialog box."

Add a step 9.2 with the following text: "In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Access, and Remote Access. The easiest is to add the local DCOM Distributed Users group. "

Add a step 9.3 with the following text: "Click OK to close the dialog box. Close the MMC snap-in."

Step 10 stays as-is: "Restart the Integration Services service."

|||

Thanks a lot.

Finally I can connect to SSIS.

sql

Remote SSIS connection problem

we are trying to remotely adminiter SQL 2005 installed on Server B (actual server names are diferent) from SQL Server Manamagement Studio installed on Server A.

We can connect to everything else except SSIS. When we try to connect to SSIS, it says:

Cannot Connecto to B
Additional Information:
Failed to retrieve data fro this request. (Microsoft.SqlServer.SmoEnum)
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable
.
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable

from connection dialog on A, we have tried putting FDQN of B.
we are using windows authentication with domain account.

per one of the suggestions, we modified MsDtsSrvr.ini.xml to change the ServerName for MSDB to be one fo the following:
B, .\MSSQLSERVER, B\MSSQLSERVER, .

we also went to DCOM settings of MsDtsServer, under security, Customize for selected for all three Permissions, and under customize settings, B\Administrators was given full access for everything.

also the domain user was added to both machines in the administrators group.

i'll appreciate any ideas.Might there be a firewall enabled on machine B?|||Both are windows 2003 boxes with no firewalls. there is a firewall in between box b and a, and we requested hosting services to open ports 135, and 3882 which they did. we have emailed them back today to make sure that the ports were indeed opened.

btw, i am with mcs. i posted it here since i wasnt sure who to direct this question to, since i did not have any internal BI contacts. i am working on this issue with a client.

Thanks
Ali|||

We are using DCOM, not RPC and port 135 may not be enough.

This port serves as RPC endpoint for initial service discovery, but then the client should be able to communicate via (randomly assigned) port used by MsDtsSrvr.exe

|||

If it always picks a random port, do we have to resort to what is specified in this article (static end points for DCOM ) http://weblogs.asp.net/rhurlbut/archive/2004/03/07/85542.aspx so we can open only 2 ports (or a range of ports in the firewall) ?

|||Or, can we configure a static endpoint for the MsDtsServer.exe from the application properties under component services? is that same as setting the registry to configure port settings?

Thanks
Ali|||

Yes, it creates the same registry key.

|||I have the same problem. Did you find a resolution ? I can connect from my desktop to Analysis Services and Database Engine, just not to SSIS. I haven't seen a useful answer to this anywhere !!!|||

Hi,

I dont know exactly why this error comes but i solved it by removing the server name from the named pipes.

\\.\pipe\sql\query and entered this value in the named pipes field.

and i started working.

from

sufian

Remote SSIS connection problem

we are trying to remotely adminiter SQL 2005 installed on Server B (actual server names are diferent) from SQL Server Manamagement Studio installed on Server A.

We can connect to everything else except SSIS. When we try to connect to SSIS, it says:

Cannot Connecto to B
Additional Information:
Failed to retrieve data fro this request. (Microsoft.SqlServer.SmoEnum)
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable
.
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable

from connection dialog on A, we have tried putting FDQN of B.
we are using windows authentication with domain account.

per one of the suggestions, we modified MsDtsSrvr.ini.xml to change the ServerName for MSDB to be one fo the following:
B, .\MSSQLSERVER, B\MSSQLSERVER, .

we also went to DCOM settings of MsDtsServer, under security, Customize for selected for all three Permissions, and under customize settings, B\Administrators was given full access for everything.

also the domain user was added to both machines in the administrators group.

i'll appreciate any ideas.Might there be a firewall enabled on machine B?|||Both are windows 2003 boxes with no firewalls. there is a firewall in between box b and a, and we requested hosting services to open ports 135, and 3882 which they did. we have emailed them back today to make sure that the ports were indeed opened.

btw, i am with mcs. i posted it here since i wasnt sure who to direct this question to, since i did not have any internal BI contacts. i am working on this issue with a client.

Thanks
Ali|||

We are using DCOM, not RPC and port 135 may not be enough.

This port serves as RPC endpoint for initial service discovery, but then the client should be able to communicate via (randomly assigned) port used by MsDtsSrvr.exe

|||

If it always picks a random port, do we have to resort to what is specified in this article (static end points for DCOM ) http://weblogs.asp.net/rhurlbut/archive/2004/03/07/85542.aspx so we can open only 2 ports (or a range of ports in the firewall) ?

|||Or, can we configure a static endpoint for the MsDtsServer.exe from the application properties under component services? is that same as setting the registry to configure port settings?

Thanks
Ali|||

Yes, it creates the same registry key.

|||I have the same problem. Did you find a resolution ? I can connect from my desktop to Analysis Services and Database Engine, just not to SSIS. I haven't seen a useful answer to this anywhere !!!|||

Hi,

I dont know exactly why this error comes but i solved it by removing the server name from the named pipes.

\\.\pipe\sql\query and entered this value in the named pipes field.

and i started working.

from

sufian

Remote SSIS Access

Hello,
We have discovered that unless a user is an administrator on the MS SQL 2005
server, they cannot connect to SSIS server locally or remotely.
The SQL Junkies site has the solution. They recommend that you first add the
user to the Distributed COM Users group. Then you should run
%windir%\system32\Com\comexp.msc to launch Component Services to launch
component server. On the properties of MsDtsServer you can choose security
and from there you can set the Remote Activation permissions to allow the
user to connect the SSIS server remotely. The SSIS service should then be
restarted.
I tried it and it works. However, what are the security implications with
this solution?The implications are basically just what you set - you allow
that user to connect remotely to the process for
MsDtsServer. Not much outside of that really - you're only
changing this for SSIS and that particular user.
-Sue
On Thu, 8 Jun 2006 14:04:04 -0600, "Loren Zubis"
<Loren.Zubis@.gov.ab.ca> wrote:

>Hello,
>We have discovered that unless a user is an administrator on the MS SQL 200
5
>server, they cannot connect to SSIS server locally or remotely.
>The SQL Junkies site has the solution. They recommend that you first add th
e
>user to the Distributed COM Users group. Then you should run
>%windir%\system32\Com\comexp.msc to launch Component Services to launch
>component server. On the properties of MsDtsServer you can choose security
>and from there you can set the Remote Activation permissions to allow the
>user to connect the SSIS server remotely. The SSIS service should then be
>restarted.
>I tried it and it works. However, what are the security implications with
>this solution?
>|||The implications are basically just what you set - you allow
that user to connect remotely to the process for
MsDtsServer. Not much outside of that really - you're only
changing this for SSIS and that particular user.
-Sue
On Thu, 8 Jun 2006 14:04:04 -0600, "Loren Zubis"
<Loren.Zubis@.gov.ab.ca> wrote:

>Hello,
>We have discovered that unless a user is an administrator on the MS SQL 200
5
>server, they cannot connect to SSIS server locally or remotely.
>The SQL Junkies site has the solution. They recommend that you first add th
e
>user to the Distributed COM Users group. Then you should run
>%windir%\system32\Com\comexp.msc to launch Component Services to launch
>component server. On the properties of MsDtsServer you can choose security
>and from there you can set the Remote Activation permissions to allow the
>user to connect the SSIS server remotely. The SSIS service should then be
>restarted.
>I tried it and it works. However, what are the security implications with
>this solution?
>

Remote Executiong - SSIS

All,

Is it possible to run the ssis package from a remote box?

The SSIS package will be stored in a sql server (open for suggestions on this) . We have a seperate box which has a 3rd party scheduler application. All our current scheduled jobs are ran from this box. we want the ssis packages also to be called from this box, as it will be easier to maintain and keep track of the jobs running.

So, we want the SSIS package to be called from this scheduler box. Any ideas?

Thanks

You can create a job without schedule on SQL box, then invoke this job at the time defined by your own scheduler (e.g. start osql.exe to run sp_start_job).

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

|||

If I install the client tools for Sql Server 2005 in the scheduler box, will i be able to run the package from that box itself using DTEXEC?

The problem I see with sp_start_job is that, it looks it will just start the job and report a success as long as the job gets started. If the job fails during execution we will still not know anything about it and I cannot have a dependency of jobs based on that.

|||

Karunakaran wrote:

If I install the client tools for Sql Server 2005 in the scheduler box, will i be able to run the package from that box itself using DTEXEC?

Yes, but that box will require a SQL Server license.|||

Another question on the same lines, let me know if I need to post this in a seperate thread.

I write a console application referencing dts runtime classes, and I invoke the package from the console app. Now If I deploy this console application in a box where ssis is not installed, but .NET framework is installed will this work? or is it against the licensing terms?

Thanks
Karunakaran

|||

Karunakaran wrote:

Another question on the same lines, let me know if I need to post this in a seperate thread.

I write a console application referencing dts runtime classes, and I invoke the package from the console app. Now If I deploy this console application in a box where ssis is not installed, but .NET framework is installed will this work? or is it against the licensing terms?

Thanks
Karunakaran

No. SSIS runtime components are not redistributable. You'll need to install SSIS on that box, and that will require a license.|||Thanks for the clarifications, Phil.

Remote Debugging in SSIS ?

Hello

I've just heard that Remote Debugging should be possible in SSIS, but how ?

Some of the projects we run require a lot of memory and it's sometimes slow to debug on the local machine ?

Yes i know i can reduce the input rows, but in some cases i need all the data for testing.

Does anyone know how to remote debug ?

Do you have Visual Studio installed?

-Satya SKJ

SQL Server MVP

|||Yes|||And then ?|||

cgpl,

Just to clarify, by remote debugging I assume you mean that you want to make a package execute on a remote machine whilst seeing things turn green/yellow/red on your local machine. Is that correct?

As far as I know, that isn't possible. But don't take my word for it. Where did you hear that it WAS possible?

-Jamie

|||

green/yellow/red yes

We had a meeting with Kevin Cox from Microsoft who told that remote debugging was possible (the way i understood it). Don't know if you know him, but he told that he would look in to it.

|||

No, I haven't heard of Kevin. if you hear anything back I'd be keen to hear it as well.

TIA

-Jamie

|||SSIS does not implement remote debugging. It was considered, but it was too costly to do. Maybe in the next version, if we'll have time.|||If possible, core be able to debug a script component task too...|||

We would appreciate if this could be considered as 'must have' functionallity. As we experience great disadvantages from not being able to remotely debug our ssis packages. Now we have to invest in more memory or servers to be able to provide our ssis development team with good performance. For each remote desktop session we will need to provide 1 gb of memory (visual studio, ssms, desktop etc) + memory for sql server 2005. When working with 8 ssis developers we would need 16gb + memory on our development server. Informatica/Business Obects/Oracle Warehouse builder all provide client/server development scenario's. Hopefully Microsoft will consider this functionallity as important for a new release of ssis.

Marc

|||

MarcvdW wrote:

We would appreciate if this could be considered as 'must have' functionallity. As we experience great disadvantages from not being able to remotely debug our ssis packages. Now we have to invest in more memory or servers to be able to provide our ssis development team with good performance. For each remote desktop session we will need to provide 1 gb of memory (visual studio, ssms, desktop etc) + memory for sql server 2005. When working with 8 ssis developers we would need 16gb + memory on our development server. Informatica/Business Obects/Oracle Warehouse builder all provide client/server development scenario's. Hopefully Microsoft will consider this functionallity as important for a new release of ssis.

Marc

Marc,

If you want this then the place to ask for it is http://connect.microsoft.com/sqlserver/feedback

-Jamie

|||Thanks Jamie, I wasn't aware of this option. I posted my feedback.|||

MarcvdW wrote:

Thanks Jamie, I wasn't aware of this option. I posted my feedback.

cool. Could you put the link up here? The more people that add their weight to it the more likely it is to happen. I will certainly add some comments.

|||

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

Here it is.

Marc

Remote Debugging in SSIS ?

Hello

I've just heard that Remote Debugging should be possible in SSIS, but how ?

Some of the projects we run require a lot of memory and it's sometimes slow to debug on the local machine ?

Yes i know i can reduce the input rows, but in some cases i need all the data for testing.

Does anyone know how to remote debug ?

Do you have Visual Studio installed?

-Satya SKJ

SQL Server MVP

|||Yes|||And then ?|||

cgpl,

Just to clarify, by remote debugging I assume you mean that you want to make a package execute on a remote machine whilst seeing things turn green/yellow/red on your local machine. Is that correct?

As far as I know, that isn't possible. But don't take my word for it. Where did you hear that it WAS possible?

-Jamie

|||

green/yellow/red yes

We had a meeting with Kevin Cox from Microsoft who told that remote debugging was possible (the way i understood it). Don't know if you know him, but he told that he would look in to it.

|||

No, I haven't heard of Kevin. if you hear anything back I'd be keen to hear it as well.

TIA

-Jamie

|||SSIS does not implement remote debugging. It was considered, but it was too costly to do. Maybe in the next version, if we'll have time.|||If possible, core be able to debug a script component task too...|||

We would appreciate if this could be considered as 'must have' functionallity. As we experience great disadvantages from not being able to remotely debug our ssis packages. Now we have to invest in more memory or servers to be able to provide our ssis development team with good performance. For each remote desktop session we will need to provide 1 gb of memory (visual studio, ssms, desktop etc) + memory for sql server 2005. When working with 8 ssis developers we would need 16gb + memory on our development server. Informatica/Business Obects/Oracle Warehouse builder all provide client/server development scenario's. Hopefully Microsoft will consider this functionallity as important for a new release of ssis.

Marc

|||

MarcvdW wrote:

We would appreciate if this could be considered as 'must have' functionallity. As we experience great disadvantages from not being able to remotely debug our ssis packages. Now we have to invest in more memory or servers to be able to provide our ssis development team with good performance. For each remote desktop session we will need to provide 1 gb of memory (visual studio, ssms, desktop etc) + memory for sql server 2005. When working with 8 ssis developers we would need 16gb + memory on our development server. Informatica/Business Obects/Oracle Warehouse builder all provide client/server development scenario's. Hopefully Microsoft will consider this functionallity as important for a new release of ssis.

Marc

Marc,

If you want this then the place to ask for it is http://connect.microsoft.com/sqlserver/feedback

-Jamie

|||Thanks Jamie, I wasn't aware of this option. I posted my feedback.|||

MarcvdW wrote:

Thanks Jamie, I wasn't aware of this option. I posted my feedback.

cool. Could you put the link up here? The more people that add their weight to it the more likely it is to happen. I will certainly add some comments.

|||

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

Here it is.

Marc

Remote Debugging in SSIS ?

Hello

I've just heard that Remote Debugging should be possible in SSIS, but how ?

Some of the projects we run require a lot of memory and it's sometimes slow to debug on the local machine ?

Yes i know i can reduce the input rows, but in some cases i need all the data for testing.

Does anyone know how to remote debug ?

Do you have Visual Studio installed?

-Satya SKJ

SQL Server MVP

|||Yes|||And then ?|||

cgpl,

Just to clarify, by remote debugging I assume you mean that you want to make a package execute on a remote machine whilst seeing things turn green/yellow/red on your local machine. Is that correct?

As far as I know, that isn't possible. But don't take my word for it. Where did you hear that it WAS possible?

-Jamie

|||

green/yellow/red yes

We had a meeting with Kevin Cox from Microsoft who told that remote debugging was possible (the way i understood it). Don't know if you know him, but he told that he would look in to it.

|||

No, I haven't heard of Kevin. if you hear anything back I'd be keen to hear it as well.

TIA

-Jamie

|||SSIS does not implement remote debugging. It was considered, but it was too costly to do. Maybe in the next version, if we'll have time.|||If possible, core be able to debug a script component task too...|||

We would appreciate if this could be considered as 'must have' functionallity. As we experience great disadvantages from not being able to remotely debug our ssis packages. Now we have to invest in more memory or servers to be able to provide our ssis development team with good performance. For each remote desktop session we will need to provide 1 gb of memory (visual studio, ssms, desktop etc) + memory for sql server 2005. When working with 8 ssis developers we would need 16gb + memory on our development server. Informatica/Business Obects/Oracle Warehouse builder all provide client/server development scenario's. Hopefully Microsoft will consider this functionallity as important for a new release of ssis.

Marc

|||

MarcvdW wrote:

We would appreciate if this could be considered as 'must have' functionallity. As we experience great disadvantages from not being able to remotely debug our ssis packages. Now we have to invest in more memory or servers to be able to provide our ssis development team with good performance. For each remote desktop session we will need to provide 1 gb of memory (visual studio, ssms, desktop etc) + memory for sql server 2005. When working with 8 ssis developers we would need 16gb + memory on our development server. Informatica/Business Obects/Oracle Warehouse builder all provide client/server development scenario's. Hopefully Microsoft will consider this functionallity as important for a new release of ssis.

Marc

Marc,

If you want this then the place to ask for it is http://connect.microsoft.com/sqlserver/feedback

-Jamie

|||Thanks Jamie, I wasn't aware of this option. I posted my feedback.|||

MarcvdW wrote:

Thanks Jamie, I wasn't aware of this option. I posted my feedback.

cool. Could you put the link up here? The more people that add their weight to it the more likely it is to happen. I will certainly add some comments.

|||

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

Here it is.

Marc

Remote connection to SSIS fails with Access is Denied

I'm new to SSIS and I'm having problems getting a remote connection to the SSIS service using Management Studio on my workstation. If I terminal Service onto the Server I have no problems connecting to SSIS, but if I try to connect remotely I get the "Access is denied" error message. I have completed the following steps:

To configure rights for remote users on Windows Server 2003 or Windows XP

1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.

4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

5. Right-click on MsDtsServer and select Properties.

6. In the MsDtsServer Properties dialog box, select the Security tab.

7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

9. Click OK to close the dialog box. Close the MMC snap-in.

10. Restart the Integration Services service.

But I still get the Access is denied error from my workstation?

I have Power User rights on the server and I'm a sysadmin in the database instance. The SSIS packages I am trying to access are stored in the database. If I add myself to the local administrators group on the server I CAN get remote access, but this is not an acceptable solution in our production environment.

Thanks for any help

Please search the forums... You'd find your answer there, however:

http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/|||Thank you

Remote Connection to SSIS

Hello.

I am trying to remotely connect to SSIS from my PC using windows authentication in SQL mgmt studio and I keep getting the following error message:

Cannot connect to <server>

Additional Information:

Failed to retrieve date for this request. (Microsoft.SqlServer.SmoEnum)

Connect to SSIS service on machine "<server>" failed: The RPC server is unavailable.

I do not get the same error when I connect to the database engine, just SSIS. I don't have a firewall inbetween the machines either so it can't be that.

Has anyone else had a similar problem? if so, I would be grateful if you can you tell me how you got around it

Thanks

See this thread where I answered a similar question regarding not being able to connect to SSIS from Management Studio, I'm assuming this may be your problem:

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

HTH

|||

I get the same RPC error, but one of my co-workers doesn't get the error, and he can connect to SSIS from his desktop, but I cannot. I assume I don't need to change the .i' GAD 0l3/2e3/2 006o Rn the server since he has no problem connecting.

Any other ideas ?

Remote Connection to SSIS

Hello.

I am trying to remotely connect to SSIS from my PC using windows authentication in SQL mgmt studio and I keep getting the following error message:

Cannot connect to <server>

Additional Information:

Failed to retrieve date for this request. (Microsoft.SqlServer.SmoEnum)

Connect to SSIS service on machine "<server>" failed: The RPC server is unavailable.

I do not get the same error when I connect to the database engine, just SSIS. I don't have a firewall inbetween the machines either so it can't be that.

Has anyone else had a similar problem? if so, I would be grateful if you can you tell me how you got around it

Thanks

See this thread where I answered a similar question regarding not being able to connect to SSIS from Management Studio, I'm assuming this may be your problem:

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

HTH

|||

I get the same RPC error, but one of my co-workers doesn't get the error, and he can connect to SSIS from his desktop, but I cannot. I assume I don't need to change the .i' GAD 0l3/2e3/2 006o Rn the server since he has no problem connecting.

Any other ideas ?