noob to SSIS package configure and deploy

Hi there

I am fairly new to SSIS, I got my package running fine in my development environment with the connection managers (connecting to 3 databases on the same server, with username and password, not Integrated security) and all.

The problem comes in when i want to deploy my package or execute it outside of the development environment.

When I execute the .dtsx file in my project it brings up the Execute Package Utility and I just click Execute, thinking it should work, but not, any ideas?

So I read some of the forums and got looking into the configuration file, suppose that it does not carry the connection manager settings thru, but dam that still doesn't work for me?

The whole idea is that I will be executing the package from my C# code, so does my package have to be just on the server in a folder or does it have to be installed in the db? which is best?

Hope anyone can help me!!

Thanks
Jamie

If the error you are getting is a failure to connect to the database, the problem is in the password not being stored in the package. If you search the forum for connection strings and configurations, you'll see a number of different solutions to this.

If that's not the problem, please include the error message that you are receiving.

non-admin user and SSIS (integration services)

I wish to grant access to SSIS (e.g., register server in mssms) for a
user _without_ making that user an administrator of the host.
How can this be done--if at all?
horseradish,
A login that can use SSIS needs no special server rights. In terms of
server roles, my developers are in 'public'. There are decisions that you
need to make, of course, and these are outlined here:
http://technet.microsoft.com/en-us/library/ms137833.aspx
FWIW, I (despite the pitch for how good it is to store packages in msdb)
keep my stored on the file system. For one thing, on the file system more
than one team member can edit the package.
RLF
"horseradish" <dontcallme@.illcallu.net> wrote in message
news:OgC5bzRMIHA.5240@.TK2MSFTNGP04.phx.gbl...
>I wish to grant access to SSIS (e.g., register server in mssms) for a user
>_without_ making that user an administrator of the host.
> How can this be done--if at all?
|||You can follow this procedure to give access to SSIS to users who are not
local Administrators (look for the 'Access is Denied' section').
http://technet.microsoft.com/en-us/library/aa337083.aspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"horseradish" wrote:

> I wish to grant access to SSIS (e.g., register server in mssms) for a
> user _without_ making that user an administrator of the host.
> How can this be done--if at all?
>
|||Ben,

> You can follow this procedure to give access to SSIS to users who are not
> local Administrators (look for the 'Access is Denied' section').
> http://technet.microsoft.com/en-us/library/aa337083.aspx
BINGO. This solved the issue. Thanks for the assist!

non-admin user and SSIS (integration services)

I wish to grant access to SSIS (e.g., register server in mssms) for a
user _without_ making that user an administrator of the host.
How can this be done--if at all?horseradish,
A login that can use SSIS needs no special server rights. In terms of
server roles, my developers are in 'public'. There are decisions that you
need to make, of course, and these are outlined here:
http://technet.microsoft.com/en-us/library/ms137833.aspx
FWIW, I (despite the pitch for how good it is to store packages in msdb)
keep my stored on the file system. For one thing, on the file system more
than one team member can edit the package.
RLF
"horseradish" <dontcallme@.illcallu.net> wrote in message
news:OgC5bzRMIHA.5240@.TK2MSFTNGP04.phx.gbl...
>I wish to grant access to SSIS (e.g., register server in mssms) for a user
>_without_ making that user an administrator of the host.
> How can this be done--if at all?|||You can follow this procedure to give access to SSIS to users who are not
local Administrators (look for the 'Access is Denied' section').
http://technet.microsoft.com/en-us/library/aa337083.aspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"horseradish" wrote:
> I wish to grant access to SSIS (e.g., register server in mssms) for a
> user _without_ making that user an administrator of the host.
> How can this be done--if at all?
>|||Ben,
> You can follow this procedure to give access to SSIS to users who are not
> local Administrators (look for the 'Access is Denied' section').
> http://technet.microsoft.com/en-us/library/aa337083.aspx
BINGO. This solved the issue. Thanks for the assist!

non-admin user and SSIS (integration services)

I wish to grant access to SSIS (e.g., register server in mssms) for a
user _without_ making that user an administrator of the host.
How can this be done--if at all?horseradish,
A login that can use SSIS needs no special server rights. In terms of
server roles, my developers are in 'public'. There are decisions that you
need to make, of course, and these are outlined here:
http://technet.microsoft.com/en-us/...y/ms137833.aspx
FWIW, I (despite the pitch for how good it is to store packages in msdb)
keep my stored on the file system. For one thing, on the file system more
than one team member can edit the package.
RLF
"horseradish" <dontcallme@.illcallu.net> wrote in message
news:OgC5bzRMIHA.5240@.TK2MSFTNGP04.phx.gbl...
>I wish to grant access to SSIS (e.g., register server in mssms) for a user
>_without_ making that user an administrator of the host.
> How can this be done--if at all?|||You can follow this procedure to give access to SSIS to users who are not
local Administrators (look for the 'Access is Denied' section').
http://technet.microsoft.com/en-us/...y/aa337083.aspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"horseradish" wrote:

> I wish to grant access to SSIS (e.g., register server in mssms) for a
> user _without_ making that user an administrator of the host.
> How can this be done--if at all?
>|||Ben,

> You can follow this procedure to give access to SSIS to users who are not
> local Administrators (look for the 'Access is Denied' section').
> http://technet.microsoft.com/en-us/...y/aa337083.aspx
BINGO. This solved the issue. Thanks for the assist!

non unicode

Hi,
Is DT_STR Unicode? From SSIS I am using a data conversion component
and defining the data type as DT_STR and saving the output in excel
file. I am getting an error that 'can not convert from Unicode to non-
unicode'. I thought I have already taken care of that in data
conversion.
Could you explain exactly which data conversion you prepared? The DT_STR was
specified for the input field or for the output? In this last case, which is
the input field data type?
Gilberto Zampatti
"SB" wrote:

> Hi,
> Is DT_STR Unicode? From SSIS I am using a data conversion component
> and defining the data type as DT_STR and saving the output in excel
> file. I am getting an error that 'can not convert from Unicode to non-
> unicode'. I thought I have already taken care of that in data
> conversion.
>
|||On Apr 20, 2:10 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> Could you explain exactly which data conversion you prepared? The DT_STR was
> specified for the input field or for the output? In this last case, which is
> the input field data type?
> Gilberto Zampatti
>
> "SB" wrote:
> - Show quoted text -
Hi,
DT_STR should be an input field because I am getting data from a table
using ole db source.
|||OK; supposing that input field is actually non-unicode (DT_STR could be the
right data type), verify the transformation task to be sure about the output
data type. SQL 2005 uses UNICODE by default, and you should explicitly
declare non unicode data types.
If you correct the input field definition specifying Unicode (DT_WSTR), you
should avoid such error message.
The fact that you are accessing input table via OLE DB does'nt change the
fact that QSL 2005 uses Unicode by default.
Gilberto Zampatti
"SB" wrote:

> On Apr 20, 2:10 pm, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> Hi,
> DT_STR should be an input field because I am getting data from a table
> using ole db source.
>
|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode (DT_STR could be the
> right data type), verify the transformation task to be sure about the output
> data type. SQL 2005 uses UNICODE by default, and you should explicitly
> declare non unicode data types.
> If you correct the input field definition specifying Unicode (DT_WSTR), you
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 uses Unicode by default.
> Gilberto Zampatti
>
> "SB" wrote:
>
>
> - Show quoted text -
Thanks.
For output I have created a excel table using the statement:
CREATE TABLE `Excel Destination` (
`NAME` VARCHAR(),
)
I have tried string, text etc but no success.
Excel Destination editor:
Data Access Mode:
Table or view
Name of the excel sheet:
Excel Destination

non unicode

Hi,
Is DT_STR Unicode? From SSIS I am using a data conversion component
and defining the data type as DT_STR and saving the output in excel
file. I am getting an error that 'can not convert from Unicode to non-
unicode'. I thought I have already taken care of that in data
conversion.Could you explain exactly which data conversion you prepared? The DT_STR was
specified for the input field or for the output? In this last case, which is
the input field data type?
Gilberto Zampatti
"SB" wrote:

> Hi,
> Is DT_STR Unicode? From SSIS I am using a data conversion component
> and defining the data type as DT_STR and saving the output in excel
> file. I am getting an error that 'can not convert from Unicode to non-
> unicode'. I thought I have already taken care of that in data
> conversion.
>|||On Apr 20, 2:10 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> Could you explain exactly which data conversion you prepared? The DT_STR w
as
> specified for the input field or for the output? In this last case, which
is
> the input field data type?
> Gilberto Zampatti
>
> "SB" wrote:
> - Show quoted text -
Hi,
DT_STR should be an input field because I am getting data from a table
using ole db source.|||OK; supposing that input field is actually non-unicode (DT_STR could be the
right data type), verify the transformation task to be sure about the output
data type. SQL 2005 uses UNICODE by default, and you should explicitly
declare non unicode data types.
If you correct the input field definition specifying Unicode (DT_WSTR), you
should avoid such error message.
The fact that you are accessing input table via OLE DB does'nt change the
fact that QSL 2005 uses Unicode by default.
Gilberto Zampatti
"SB" wrote:

> On Apr 20, 2:10 pm, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> Hi,
> DT_STR should be an input field because I am getting data from a table
> using ole db source.
>|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode (DT_STR could be th
e
> right data type), verify the transformation task to be sure about the outp
ut
> data type. SQL 2005 uses UNICODE by default, and you should explicitly
> declare non unicode data types.
> If you correct the input field definition specifying Unicode (DT_WSTR), yo
u
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 uses Unicode by default.
> Gilberto Zampatti
>
> "SB" wrote:
>
>
>
> - Show quoted text -
Thanks.
For output I have created a excel table using the statement:
CREATE TABLE `Excel Destination` (
`NAME` VARCHAR(),
)
I have tried string, text etc but no success.
Excel Destination editor:
Data Access Mode:
Table or view
Name of the excel sheet:
Excel Destination|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode(DT_STR could be the
> right data type), verify the transformation task to be sure about the outp
ut
> data type. SQL 2005 usesUNICODEby default, and you should explicitly
> declare nonunicodedata types.
> If you correct the input field definition specifyingUnicode(DT_WSTR), you
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 usesUnicodeby default.
> Gilberto Zampatti
>
> "SB" wrote:
>
>
>
> - Show quoted text -
Hi,
Is it alright if I declare a datatype as nvarchar 'create table (name
nvarchar(20))' and then treat it as if varchar for example functions,
inserts and join with other varchar columns etc? Except giving a
larger string size I don't have much use for unicode. They might as
well keep this type internal so that we didn't have to worry about it.|||On Apr 23, 10:41 am, SB <othell...@.yahoo.com> wrote:
> On Apr 20, 3:28 pm, Gilberto Zampatti
>
>
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
>
>
>
>
>
> Hi,
> Is it alright if I declare a datatype as nvarchar 'create table (name
> nvarchar(20))' and then treat it as if varchar for example functions,
> inserts and join with other varchar columns etc? Except giving a
> larger string size I don't have much use for unicode. They might as
> well keep this type internal so that we didn't have to worry about it.- Hi
de quoted text -
> - Show quoted text -
That question was not related to SSIS package (as you have told ssis
is unicode). FWIW I was able to get the data (ssis) imported when I
used unicode for input and output data using a data conversion in
between from varchar to unicode string.

non unicode

Hi,
Is DT_STR Unicode? From SSIS I am using a data conversion component
and defining the data type as DT_STR and saving the output in excel
file. I am getting an error that 'can not convert from Unicode to non-
unicode'. I thought I have already taken care of that in data
conversion.Could you explain exactly which data conversion you prepared? The DT_STR was
specified for the input field or for the output? In this last case, which is
the input field data type?
Gilberto Zampatti
"SB" wrote:
> Hi,
> Is DT_STR Unicode? From SSIS I am using a data conversion component
> and defining the data type as DT_STR and saving the output in excel
> file. I am getting an error that 'can not convert from Unicode to non-
> unicode'. I thought I have already taken care of that in data
> conversion.
>|||On Apr 20, 2:10 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> Could you explain exactly which data conversion you prepared? The DT_STR was
> specified for the input field or for the output? In this last case, which is
> the input field data type?
> Gilberto Zampatti
>
> "SB" wrote:
> > Hi,
> > Is DT_STR Unicode? From SSIS I am using a data conversion component
> > and defining the data type as DT_STR and saving the output in excel
> > file. I am getting an error that 'can not convert from Unicode to non-
> > unicode'. I thought I have already taken care of that in data
> > conversion.- Hide quoted text -
> - Show quoted text -
Hi,
DT_STR should be an input field because I am getting data from a table
using ole db source.|||OK; supposing that input field is actually non-unicode (DT_STR could be the
right data type), verify the transformation task to be sure about the output
data type. SQL 2005 uses UNICODE by default, and you should explicitly
declare non unicode data types.
If you correct the input field definition specifying Unicode (DT_WSTR), you
should avoid such error message.
The fact that you are accessing input table via OLE DB does'nt change the
fact that QSL 2005 uses Unicode by default.
Gilberto Zampatti
"SB" wrote:
> On Apr 20, 2:10 pm, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > Could you explain exactly which data conversion you prepared? The DT_STR was
> > specified for the input field or for the output? In this last case, which is
> > the input field data type?
> > Gilberto Zampatti
> >
> >
> >
> > "SB" wrote:
> > > Hi,
> > > Is DT_STR Unicode? From SSIS I am using a data conversion component
> > > and defining the data type as DT_STR and saving the output in excel
> > > file. I am getting an error that 'can not convert from Unicode to non-
> > > unicode'. I thought I have already taken care of that in data
> > > conversion.- Hide quoted text -
> >
> > - Show quoted text -
> Hi,
> DT_STR should be an input field because I am getting data from a table
> using ole db source.
>|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode (DT_STR could be the
> right data type), verify the transformation task to be sure about the output
> data type. SQL 2005 uses UNICODE by default, and you should explicitly
> declare non unicode data types.
> If you correct the input field definition specifying Unicode (DT_WSTR), you
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 uses Unicode by default.
> Gilberto Zampatti
>
> "SB" wrote:
> > On Apr 20, 2:10 pm, Gilberto Zampatti
> > <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > > Could you explain exactly which data conversion you prepared? The DT_STR was
> > > specified for the input field or for the output? In this last case, which is
> > > the input field data type?
> > > Gilberto Zampatti
> > > "SB" wrote:
> > > > Hi,
> > > > Is DT_STR Unicode? From SSIS I am using a data conversion component
> > > > and defining the data type as DT_STR and saving the output in excel
> > > > file. I am getting an error that 'can not convert from Unicode to non-
> > > > unicode'. I thought I have already taken care of that in data
> > > > conversion.- Hide quoted text -
> > > - Show quoted text -
> > Hi,
> > DT_STR should be an input field because I am getting data from a table
> > using ole db source.- Hide quoted text -
> - Show quoted text -
Thanks.
For output I have created a excel table using the statement:
CREATE TABLE `Excel Destination` (
`NAME` VARCHAR(),
)
I have tried string, text etc but no success.
Excel Destination editor:
Data Access Mode:
Table or view
Name of the excel sheet:
Excel Destination|||On Apr 20, 3:28 pm, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> OK; supposing that input field is actually non-unicode(DT_STR could be the
> right data type), verify the transformation task to be sure about the output
> data type. SQL 2005 usesUNICODEby default, and you should explicitly
> declare nonunicodedata types.
> If you correct the input field definition specifyingUnicode(DT_WSTR), you
> should avoid such error message.
> The fact that you are accessing input table via OLE DB does'nt change the
> fact that QSL 2005 usesUnicodeby default.
> Gilberto Zampatti
>
> "SB" wrote:
> > On Apr 20, 2:10 pm, Gilberto Zampatti
> > <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > > Could you explain exactly which data conversion you prepared? The DT_STR was
> > > specified for the input field or for the output? In this last case, which is
> > > the input field data type?
> > > Gilberto Zampatti
> > > "SB" wrote:
> > > > Hi,
> > > > Is DT_STRUnicode? From SSIS I am using a data conversion component
> > > > and defining the data type as DT_STR and saving the output in excel
> > > > file. I am getting an error that 'can not convert fromUnicodeto non-
> > > >unicode'. I thought I have already taken care of that in data
> > > > conversion.- Hide quoted text -
> > > - Show quoted text -
> > Hi,
> > DT_STR should be an input field because I am getting data from a table
> > using ole db source.- Hide quoted text -
> - Show quoted text -
Hi,
Is it alright if I declare a datatype as nvarchar 'create table (name
nvarchar(20))' and then treat it as if varchar for example functions,
inserts and join with other varchar columns etc? Except giving a
larger string size I don't have much use for unicode. They might as
well keep this type internal so that we didn't have to worry about it.|||On Apr 23, 10:41 am, SB <othell...@.yahoo.com> wrote:
> On Apr 20, 3:28 pm, Gilberto Zampatti
>
>
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > OK; supposing that input field is actually non-unicode(DT_STR could be the
> > right data type), verify the transformation task to be sure about the output
> > data type. SQL 2005 usesUNICODEby default, and you should explicitly
> > declare nonunicodedata types.
> > If you correct the input field definition specifyingUnicode(DT_WSTR), you
> > should avoid such error message.
> > The fact that you are accessing input table via OLE DB does'nt change the
> > fact that QSL 2005 usesUnicodeby default.
> > Gilberto Zampatti
> > "SB" wrote:
> > > On Apr 20, 2:10 pm, Gilberto Zampatti
> > > <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > > > Could you explain exactly which data conversion you prepared? The DT_STR was
> > > > specified for the input field or for the output? In this last case, which is
> > > > the input field data type?
> > > > Gilberto Zampatti
> > > > "SB" wrote:
> > > > > Hi,
> > > > > Is DT_STRUnicode? From SSIS I am using a data conversion component
> > > > > and defining the data type as DT_STR and saving the output in excel
> > > > > file. I am getting an error that 'can not convert fromUnicodeto non-
> > > > >unicode'. I thought I have already taken care of that in data
> > > > > conversion.- Hide quoted text -
> > > > - Show quoted text -
> > > Hi,
> > > DT_STR should be an input field because I am getting data from a table
> > > using ole db source.- Hide quoted text -
> > - Show quoted text -
> Hi,
> Is it alright if I declare a datatype as nvarchar 'create table (name
> nvarchar(20))' and then treat it as if varchar for example functions,
> inserts and join with other varchar columns etc? Except giving a
> larger string size I don't have much use for unicode. They might as
> well keep this type internal so that we didn't have to worry about it.- Hide quoted text -
> - Show quoted text -
That question was not related to SSIS package (as you have told ssis
is unicode). FWIW I was able to get the data (ssis) imported when I
used unicode for input and output data using a data conversion in
between from varchar to unicode string.

Non significant '0' removed from alphanumeric code

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

Double check all of your metadata links and double check on the advanced editor screens that you don't inadvertently have that column cast as a numeric data type.|||Already done twice! ;-)|||

Sbastien Nunes wrote:

Already done twice! ;-)

Then I'm missing something. A DT_WSTR will not drop leading zeros.|||Are you using SQL in your lookup transformation?|||Yes I am.|||

Sebastion,

Did you get this resolved?

Did you try using data viewers across the Data flow to see at what point the leading zero gets removed...what type of destination are you using...may the probelm be there?

|||

Hi,

No it's not resolved. Yes I tried using Data Viewers, the column where the 0 is removed is an output column from a Lookup Transformation. The Lookup is based on a SQL query.

I have checked the MetaData and everything seems allright.

Thanks for your help.

|||

That is weird...

What type of OLE DB source you are using in the lookup transform?

Did you get same results when running the same query in a native query tool (e.g. SSMS if SQL Server)?

What happens if you try to explicitly cast that column to string type in the lookup query?

|||

Thanks for your answer Rafael, I didn't had the time to test that today, I'll try on monday and I'll let you know what happens.

Non significant '0' removed from alphanumeric code

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

Double check all of your metadata links and double check on the advanced editor screens that you don't inadvertently have that column cast as a numeric data type.|||Already done twice! ;-)|||

Sbastien Nunes wrote:

Already done twice! ;-)

Then I'm missing something. A DT_WSTR will not drop leading zeros.|||Are you using SQL in your lookup transformation?|||Yes I am.|||

Sebastion,

Did you get this resolved?

Did you try using data viewers across the Data flow to see at what point the leading zero gets removed...what type of destination are you using...may the probelm be there?

|||

Hi,

No it's not resolved. Yes I tried using Data Viewers, the column where the 0 is removed is an output column from a Lookup Transformation. The Lookup is based on a SQL query.

I have checked the MetaData and everything seems allright.

Thanks for your help.

|||

That is weird...

What type of OLE DB source you are using in the lookup transform?

Did you get same results when running the same query in a native query tool (e.g. SSMS if SQL Server)?

What happens if you try to explicitly cast that column to string type in the lookup query?

|||

Thanks for your answer Rafael, I didn't had the time to test that today, I'll try on monday and I'll let you know what happens.

No support for SQL Server Authentication and SSIS !!!!

Hi

I want to manage my servers from a central location and have the ability to manage my packges too. Unfortunatley my servers are across domains which means I have to use SQL Server Login, which isn't a bad thing. However for some bizzare reason I can't connect to Integration Services using this method, only windows authentication which is a complete pain in the butt from a management stand point as I have to copy packages to various servers rather than add them from a central point.

Does anyone know if this bug/feature or what ever Microsoft is calling it will be fixed or know of any workarounds.

Thanks

I have no problem with SSIS and SQL-Server-Authentification.|||

I do not even get the option to change the authentication method to SQL Server and the box is greyed out so I can't change the Windows authentication user either.

I do not have any problems inside SSIS, only when I try to connect to the SSIS server from within SQL Server Management Studio in a different domain.

|||I am assuming you are trying to connect to Integration Services from SQL Server Management Studio. You are right, you cannot change the authentication method to SQL Server authentication in this case. This is because it is a separate service, and sql server authentication does not work in this case.|||

This is indeed problematic... if developers for SSIS or even SSAS for that matter have workstations in a domain different from the domain that hosts the Server you cannot deploy packages to the server, you can't create/access SSAS Dbs either.

This means then that any developer must work in the same domain as the server they are trying to deploy to or access (SQL Server mgmt. studio to monitor SSIS packages or to even open a SSAS DB in Visual Studio)

Anyone find a way around this?

Joe.

|||

I just found this the hard way. I work for client in a different domain to my workstation and I cannot deploy the app. Like Joe, if someone has already researched this, plxz let us know.

I also have problems with restricted VPN access with locked down ports at the client.

Thanks

|||

I have exactly the same problem and also found out the hard way. In fact my problem is a little worse - our client doesn't even HAVE a domain (yes they are a little backward)!

I managed to get the package installed on the client's server after much trial and error (by copying the VS solution to the server, editing my connection managers and debugging then deploying from there. Thankfully they had installed VS on the server).

The bigger problem is now that the clients can never run the SSIS package remotely. What is worse is that SSAS has the same problem - my clients can't browse their cube from Excel or SSMS! The only way I can think of around this is to remote desktop into the server, which is obviously bad.

Thanks in advance for any help / suggestions / comments.

|||

If you want to execute a SSIS package remotely, set up a SQL Server Agent job to execute the package. That job can be executed from wherever you like by anyone with SSMS.

-Jamie

|||

Aranda wrote:

The bigger problem is now that the clients can never run the SSIS package remotely.

SSIS Service does not provide remote execution, as Jamie suggested - use Agent Jobs instead.

The usual way to get package installed on a machine you can access remotely is to build deployment utility (option in VS SSIS project), copy the folder with deployment utility and run it on the target machine.

What you'll be missing due to lack of domain is
1) ability to remotely store packages, and
2) ability to remotely monitor packages as they are being run, and to terminate these packages.

|||

I just came across this.

I to am frustrated that you can only connect to the SSIS service via windows authentication. I am attempting to connect from my computer(a client running Management Studio) not directly from the machine 'hosting' the SQL Server and SSIS services. When I do this, I obviously have to set myself up with an operating system(Win 2k3 server )account on the server machine but I am only able to connect to SSIS if I am a member of the administrators group. Is this true? Can I connect as someone with less rights/privledges?

No support for SQL Server Authentication and SSIS !!!!

Hi

I want to manage my servers from a central location and have the ability to manage my packges too. Unfortunatley my servers are across domains which means I have to use SQL Server Login, which isn't a bad thing. However for some bizzare reason I can't connect to Integration Services using this method, only windows authentication which is a complete pain in the butt from a management stand point as I have to copy packages to various servers rather than add them from a central point.

Does anyone know if this bug/feature or what ever Microsoft is calling it will be fixed or know of any workarounds.

Thanks

I have no problem with SSIS and SQL-Server-Authentification.|||

I do not even get the option to change the authentication method to SQL Server and the box is greyed out so I can't change the Windows authentication user either.

I do not have any problems inside SSIS, only when I try to connect to the SSIS server from within SQL Server Management Studio in a different domain.

|||I am assuming you are trying to connect to Integration Services from SQL Server Management Studio. You are right, you cannot change the authentication method to SQL Server authentication in this case. This is because it is a separate service, and sql server authentication does not work in this case.|||

This is indeed problematic... if developers for SSIS or even SSAS for that matter have workstations in a domain different from the domain that hosts the Server you cannot deploy packages to the server, you can't create/access SSAS Dbs either.

This means then that any developer must work in the same domain as the server they are trying to deploy to or access (SQL Server mgmt. studio to monitor SSIS packages or to even open a SSAS DB in Visual Studio)

Anyone find a way around this?

Joe.

|||

I just found this the hard way. I work for client in a different domain to my workstation and I cannot deploy the app. Like Joe, if someone has already researched this, plxz let us know.

I also have problems with restricted VPN access with locked down ports at the client.

Thanks

|||

I have exactly the same problem and also found out the hard way. In fact my problem is a little worse - our client doesn't even HAVE a domain (yes they are a little backward)!

I managed to get the package installed on the client's server after much trial and error (by copying the VS solution to the server, editing my connection managers and debugging then deploying from there. Thankfully they had installed VS on the server).

The bigger problem is now that the clients can never run the SSIS package remotely. What is worse is that SSAS has the same problem - my clients can't browse their cube from Excel or SSMS! The only way I can think of around this is to remote desktop into the server, which is obviously bad.

Thanks in advance for any help / suggestions / comments.

|||

If you want to execute a SSIS package remotely, set up a SQL Server Agent job to execute the package. That job can be executed from wherever you like by anyone with SSMS.

-Jamie

|||

Aranda wrote:

The bigger problem is now that the clients can never run the SSIS package remotely.

SSIS Service does not provide remote execution, as Jamie suggested - use Agent Jobs instead.

The usual way to get package installed on a machine you can access remotely is to build deployment utility (option in VS SSIS project), copy the folder with deployment utility and run it on the target machine.

What you'll be missing due to lack of domain is
1) ability to remotely store packages, and
2) ability to remotely monitor packages as they are being run, and to terminate these packages.

|||

I just came across this.

I to am frustrated that you can only connect to the SSIS service via windows authentication. I am attempting to connect from my computer(a client running Management Studio) not directly from the machine 'hosting' the SQL Server and SSIS services. When I do this, I obviously have to set myself up with an operating system(Win 2k3 server )account on the server machine but I am only able to connect to SSIS if I am a member of the administrators group. Is this true? Can I connect as someone with less rights/privledges?

No support for SQL Server Authentication and SSIS !!!!

Hi

I want to manage my servers from a central location and have the ability to manage my packges too. Unfortunatley my servers are across domains which means I have to use SQL Server Login, which isn't a bad thing. However for some bizzare reason I can't connect to Integration Services using this method, only windows authentication which is a complete pain in the butt from a management stand point as I have to copy packages to various servers rather than add them from a central point.

Does anyone know if this bug/feature or what ever Microsoft is calling it will be fixed or know of any workarounds.

Thanks

I have no problem with SSIS and SQL-Server-Authentification.|||

I do not even get the option to change the authentication method to SQL Server and the box is greyed out so I can't change the Windows authentication user either.

I do not have any problems inside SSIS, only when I try to connect to the SSIS server from within SQL Server Management Studio in a different domain.

|||I am assuming you are trying to connect to Integration Services from SQL Server Management Studio. You are right, you cannot change the authentication method to SQL Server authentication in this case. This is because it is a separate service, and sql server authentication does not work in this case.|||

This is indeed problematic... if developers for SSIS or even SSAS for that matter have workstations in a domain different from the domain that hosts the Server you cannot deploy packages to the server, you can't create/access SSAS Dbs either.

This means then that any developer must work in the same domain as the server they are trying to deploy to or access (SQL Server mgmt. studio to monitor SSIS packages or to even open a SSAS DB in Visual Studio)

Anyone find a way around this?

Joe.

|||

I just found this the hard way. I work for client in a different domain to my workstation and I cannot deploy the app. Like Joe, if someone has already researched this, plxz let us know.

I also have problems with restricted VPN access with locked down ports at the client.

Thanks

|||

I have exactly the same problem and also found out the hard way. In fact my problem is a little worse - our client doesn't even HAVE a domain (yes they are a little backward)!

I managed to get the package installed on the client's server after much trial and error (by copying the VS solution to the server, editing my connection managers and debugging then deploying from there. Thankfully they had installed VS on the server).

The bigger problem is now that the clients can never run the SSIS package remotely. What is worse is that SSAS has the same problem - my clients can't browse their cube from Excel or SSMS! The only way I can think of around this is to remote desktop into the server, which is obviously bad.

Thanks in advance for any help / suggestions / comments.

|||

If you want to execute a SSIS package remotely, set up a SQL Server Agent job to execute the package. That job can be executed from wherever you like by anyone with SSMS.

-Jamie

|||

Aranda wrote:

The bigger problem is now that the clients can never run the SSIS package remotely.

SSIS Service does not provide remote execution, as Jamie suggested - use Agent Jobs instead.

The usual way to get package installed on a machine you can access remotely is to build deployment utility (option in VS SSIS project), copy the folder with deployment utility and run it on the target machine.

What you'll be missing due to lack of domain is
1) ability to remotely store packages, and
2) ability to remotely monitor packages as they are being run, and to terminate these packages.

|||

I just came across this.

I to am frustrated that you can only connect to the SSIS service via windows authentication. I am attempting to connect from my computer(a client running Management Studio) not directly from the machine 'hosting' the SQL Server and SSIS services. When I do this, I obviously have to set myself up with an operating system(Win 2k3 server )account on the server machine but I am only able to connect to SSIS if I am a member of the administrators group. Is this true? Can I connect as someone with less rights/privledges?