DTS Export Wizard

Is there a way for me to globally select "Delete rows in destination table"
when performing an export via the Export Wizard?
It is not obviously visible to me, and I have a great deal of tables. I've
been clicking "Edit" for each one, and this isnt practical for obvious
reasons.
Can anyone point me in the right direction? Thanks.there is an option under 'select object...' dialog.
http://msdn.microsoft.com/library/e...elpwiz_0nci.asp
or you can create a dml task and delete the data before insert new ones.
-oj
"Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
news:eAbKC$1TGHA.4608@.tk2msftngp13.phx.gbl...
> Is there a way for me to globally select "Delete rows in destination
> table" when performing an export via the Export Wizard?
> It is not obviously visible to me, and I have a great deal of tables. I've
> been clicking "Edit" for each one, and this isnt practical for obvious
> reasons.
> Can anyone point me in the right direction? Thanks.
>

DTS export problem

Hello

i can 't use DTS export via entreprise manager betwen 2 identic server (same os ,same
sqlserver 7 ,same pack et...)

I have this message

The licensing mode don t let to use DTS export ...

Where i find a parameter ?
Where i find a tool to change the licensing mode ?

RegardsControl Panel/Licensingsql

DTS export for MSDE

I need to create a DTS Export job that can be imported to
MSDE via script. But I need to be able to alter the
destination server in the script.
When I use DTS wizard to create the job and create a script
from it. I do not see how I can change the destination
server within the script, because it looks to be encoded.
Any Ideas?
You will need to use the DTS editor in Enterprise Manager to create a global
variable for your server name and make that a parameter to dtsrun. Use the
dtsrunui utility to create the command line with the global variable
parameter. Then add a Dynamic Properties task to your package that sets the
server name from the global variable and make this task the first thing that
runs in your package. Using this method you can set the names of servers,
databases and other values in the package dynamically.
Jim
"Jahnathan Cain" <cainj@.ritchie.disa.mil> wrote in message
news:2a23901c465ea$02b2ee50$a401280a@.phx.gbl...
> I need to create a DTS Export job that can be imported to
> MSDE via script. But I need to be able to alter the
> destination server in the script.
> When I use DTS wizard to create the job and create a script
> from it. I do not see how I can change the destination
> server within the script, because it looks to be encoded.
> Any Ideas?

DTS Delimited Record Question

Hello-
I am using SQL Server 2000 and am processing records via DTS. The
records are delimited and I have no trouble breaking them using the
file object but every record ends with a tilde (~) that I would like to
strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
so that the tilde would get chopped off each record before being passed
to my AxtiveX script? The records do not have the same number of
elements, so I can not simply chmop the tilde off the Nth field.
If the above will not work, how can I determine the final field in each
record and remove the offending tilde?
Thanks!
--greg
Hi
If you do not expect a tilda in any field you can just replace any occurence
in every field. If you activeX scripts assumes that there are at most n
fields you can check backwards to the last non-blank field and remove the
tilda.
John
"tubaranger@.gmail.com" wrote:

> Hello-
> I am using SQL Server 2000 and am processing records via DTS. The
> records are delimited and I have no trouble breaking them using the
> file object but every record ends with a tilde (~) that I would like to
> strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
> so that the tilde would get chopped off each record before being passed
> to my AxtiveX script? The records do not have the same number of
> elements, so I can not simply chmop the tilde off the Nth field.
> If the above will not work, how can I determine the final field in each
> record and remove the offending tilde?
> Thanks!
> --greg
>
sql

DTS Delimited Record Question

Hello-
I am using SQL Server 2000 and am processing records via DTS. The
records are delimited and I have no trouble breaking them using the
file object but every record ends with a tilde (~) that I would like to
strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
so that the tilde would get chopped off each record before being passed
to my AxtiveX script? The records do not have the same number of
elements, so I can not simply chmop the tilde off the Nth field.
If the above will not work, how can I determine the final field in each
record and remove the offending tilde?
Thanks!
--gregHi
If you do not expect a tilda in any field you can just replace any occurence
in every field. If you activeX scripts assumes that there are at most n
fields you can check backwards to the last non-blank field and remove the
tilda.
John
"tubaranger@.gmail.com" wrote:

> Hello-
> I am using SQL Server 2000 and am processing records via DTS. The
> records are delimited and I have no trouble breaking them using the
> file object but every record ends with a tilde (~) that I would like to
> strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
> so that the tilde would get chopped off each record before being passed
> to my AxtiveX script? The records do not have the same number of
> elements, so I can not simply chmop the tilde off the Nth field.
> If the above will not work, how can I determine the final field in each
> record and remove the offending tilde?
> Thanks!
> --greg
>

DTS Delimited Record Question

Hello-
I am using SQL Server 2000 and am processing records via DTS. The
records are delimited and I have no trouble breaking them using the
file object but every record ends with a tilde (~) that I would like to
strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
so that the tilde would get chopped off each record before being passed
to my AxtiveX script? The records do not have the same number of
elements, so I can not simply chmop the tilde off the Nth field.
If the above will not work, how can I determine the final field in each
record and remove the offending tilde?
Thanks!
--gregHi
If you do not expect a tilda in any field you can just replace any occurence
in every field. If you activeX scripts assumes that there are at most n
fields you can check backwards to the last non-blank field and remove the
tilda.
John
"tubaranger@.gmail.com" wrote:
> Hello-
> I am using SQL Server 2000 and am processing records via DTS. The
> records are delimited and I have no trouble breaking them using the
> file object but every record ends with a tilde (~) that I would like to
> strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
> so that the tilde would get chopped off each record before being passed
> to my AxtiveX script? The records do not have the same number of
> elements, so I can not simply chmop the tilde off the Nth field.
> If the above will not work, how can I determine the final field in each
> record and remove the offending tilde?
> Thanks!
> --greg
>

DTS and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
Thanks
Hi
There are a couple of mentions for your error message
http://search.microsoft.com/search/r...=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the end
of the cells you have previously cleared.
John
"Stephen" wrote:

> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>

DTS and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
ThanksHi
There are a couple of mentions for your error message
http://search.microsoft.com/search/...a=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the en
d
of the cells you have previously cleared.
John
"Stephen" wrote:

> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>

DTS and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
ThanksHi
There are a couple of mentions for your error messag
http://search.microsoft.com/search/results.aspx?view=msdn&st=a&na=81&qu=&qp=Deleting+data+in+a+linked+table+is+not+supported+by+this+ISAM&qa=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the end
of the cells you have previously cleared.
John
"Stephen" wrote:
> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>

DTS 2005 and quoted table names

We're trying to use DTS from SQL Server 2005 beta 2 to query data
from an Informix IDS server via OLEDB.

Unfortunately DTS is building queries of the form:

select * from "database":"owner"."tabname"

and the quoted table name is being rejected by the Informix server as a
syntax error.

Is there a way to keep DTS from quoting the table name in queries? Or is
this happening within the OLEDB provider?

Thanks.

--
John Hardin
Development and Technology group (Seattle)
CRS Retail Systems, Inc.John Hardin (jhardin@.crsretail.com) writes:
> We're trying to use DTS from SQL Server 2005 beta 2 to query data
> from an Informix IDS server via OLEDB.
> Unfortunately DTS is building queries of the form:
> select * from "database":"owner"."tabname"
> and the quoted table name is being rejected by the Informix server as a
> syntax error.
> Is there a way to keep DTS from quoting the table name in queries? Or is
> this happening within the OLEDB provider?

That question is best asked in microsoft.private.sqlserver2005.dts.

See http://go.microsoft.com/fwlink/?linkid=31765 for access information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
The table that you are loading into - is that an empty table?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegr oups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegr oups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.
|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTExec via xp_cmdshell

In my previous post here: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 Michael Entin provides a number of responses to my questions regarding programatic execution of remote SSIS packages.

Having experienced some significant reliability problems with the Microsoft.SqlServer.Dts.Runtime components from an ASP.NET process (the page either times out, or inevitably just stops responding), I have been prototyping the DTExec command option which Michael suggests as being a better approach to remote programability.

So, off I've been prototyping this all day today...

I have a stored procedure that wraps a call to xp_cmdshell which takes the DTS (DTEXEC) params as a big long argument. This scenario would hopefully allow me to call the sproc from an ASP.NET application.

The proc is deployed to a SQL 2005 machine running SSIS (which I now understand is a REQUIREMENT for targetting SSIS "remotely"). The package targets a seperate SQL 2000 machine and includes two connection managers which are set to use SSPI. I use configuration option files to allow for configurable connection manager target/sources.

In this scenario, it does not seem that the DTEXEC command runs in the same context as the caller. and as a result, a peculiar account called MACHINENAME$ is used (where MachineName is literally the name of the SQL 2005 machine). The account authentication fails (obviously) when the package tries to establish a connection to any of the connection managers because MACHINENAME$ does not exist on the connection manager servers.

Based on the following excerpt from the MSDN doc on xp_cmdshell, it would seem that MACHINENAME$ is probably the LOCAL SYSTEM, which is the process tha the SQL Server Service is running under:

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

Obviously, settting the ENTIRE SQL Server service to run as a fixed account or even a domain account is probably not appropriate for client sites. Any opinion to the contrary is welcome.

In reading Kirk Haselden's walkthrough for setting up a SQL Agent Proxy, this seems incredibly involved. Before I go through this exercise, can anyone validate that this is the way to go for doing SSPI?

A work-around is to use SQL Server Auth for the connection managers and use configuration files to try to obfuscate these details, but my preference would be SSPI/Windows Integrated.

Thanks.

SSIS was designed to interact with SQL Server Agent because it permits packages to be executed within a very secure environment. There are a variety stored procedures specific to SQL Server Agent.

I strongly recommend using SQL Server Agent stored procedures to execute SSIS packages programatically.

|||

Thanks.

Is sp_start_job the way to go then? Will this work for packages stored on the file system or package store, or only MSDB?

Also, do SQL Server Agent proxies have to be setup?

Any additional direction appreciated.

Thanks,

Rick

|||

Follow up- I got the agent proxy and credentials configured on my local dev machine and can call the job using sp_start_job, but it appears that the proc works asynchronously.

Is there anyway to either block or determine the progress or outcome of the job?

Thanks,

Rick

|||

Assert.True wrote:

Follow up- I got the agent proxy and credentials configured on my local dev machine and can call the job using sp_start_job, but it appears that the proc works asynchronously.

Is there anyway to either block or determine the progress or outcome of the job?

Thanks,

Rick

Alerts can be defined to fire when a job starts, is in progress, ends, etc. Then, the sp_add_notification stored procedure can be used to configure the notification type of the alert.

DTExec Can this thing run in the background?

I have a DTS Package that I am running from a command line via .bat file. Does anyone know if there is a command to have the command window minimized or running in the background? I used the /Rep N command but that still leaves the window open until the package has executed.

Thanks!

Try the start command it may have some options that are useful for you.

If using a bath file, then you cannot get away from having a command console session open, that is the nature of what you are doing, unlress you use a separate user session, such as you would get with a service.

|||

Thank you! The Start helped at least minimize the window! Thanks again!

DTExec and Pasword Prompting

A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).

We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.

Thanks.

SSIS Amateur wrote:

A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).

We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.

Thanks.

I recommend setting ProtectionLevel=DontSaveSensitive and then using configurations to store your conenction strings. This is the "least hassle" choice in my experience.

-Jamie

|||

OK, we'll try that.

Couple more things, in a whole series of packages, it only prompts for a password on one or another, and it's not always the same packages, even though they always run in the same order, but they all hit AS400 data. Just wondering if you had any input on that? They are all set up the same security wise.

Also, we just came across the connection manager property "ProtectionLevel". I didn't research it yet, but if we changed the setting from the default "0", could this have an impact?

|||

SSIS Amateur wrote:

OK, we'll try that.

Couple more things, in a whole series of packages, it only prompts for a password on one or another, and it's not always the same packages, even though they always run in the same order, but they all hit AS400 data. Just wondering if you had any input on that? They are all set up the same security wise.

Seems strange but I can't explain it. Sorry.

SSIS Amateur wrote:

Also, we just came across the connection manager property "ProtectionLevel". I didn't research it yet, but if we changed the setting from the default "0", could this have an impact?

Yes, it could. You should read the BOL topic on it.

-Jamie

|||

Jamie,

We're currently trying to implement your suggestion to set the Package Level Security "ProtectionLevel" to "DontSaveSensitive" and create a configuration to hold the connection strings. I seem to not be able to even get it to run in debug mode (let alone deploying and testing), because a login is failing. I noticed that the connection string does not store the password, and it's not possible to put it in there (just an observation - maybe not relevant).

It runs fine when I changed the ProtectionLevel back to the default setting. I'm know I'm missing something really stupid here. Your help is much appreciated, you know your stuff.

|||

SSIS Amateur wrote:

Jamie,

We're currently trying to implement your suggestion to set the Package Level Security "ProtectionLevel" to "DontSaveSensitive" and create a configuration to hold the connection strings. I seem to not be able to even get it to run in debug mode (let alone deploying and testing), because a login is failing. I noticed that the connection string does not store the password, and it's not possible to put it in there (just an observation - maybe not relevant).

It runs fine when I changed the ProtectionLevel back to the default setting. I'm know I'm missing something really stupid here. Your help is much appreciated, you know your stuff.

Good observation about the password. You have to manually edit the connection string yourself and put the password in there.

As you may know there was a big push in Microsoft not long ago around tightening security holes in its products and this is one of things that came out of that - it is considered a security risk for SSIS to store a password for you. Microsoft want you to responsible for creating security risks, not them.

-Jamie

|||Jamie, when you say "manually edit the connection string", where are you talking about? Actually opening the .dtsConfig file and editing? Because I can't seem to be able to successfully store the password in the Package editor.|||

OK, I have seemed to have figured my problem out. For those of you who may be a little new to SSIS or new to configuration files, here's a run down on my problem and how I fixed it.

My company scheduling software was getting prompted for a password when a .bat file containing a DTExec command was initiated, this was due to connecting to AS400 servers while transferring data. So we had to look into a way to prevent the scheduled .bat file executions from prompting for a password. Here is the solution, in detail for those of you who are lacking in SSIS experience like myself, and have a similar situation. Some of it is very obvious to most, but was not to me, so I included everything I could think of. Thanks once again to Jamie for all the help.

1. In the package, change protection level to "DontSaveSensitive" (click anywhere in the control flow, off of a task and look at properties).
2. Create a package configuration to store connection string (there are many examples on the web, here is one).
http://msdn2.microsoft.com/en-us/library/ms140213.aspx

3. You will have to manually edit the resulting .dtsConfig to include passwords. Open in notepad and add passwords to each connection. It will be stored in the project folder.
4. Build the package and deploy it, save the configuration file where you feel best. I saved on folder a sql server itself (this is an option when deploying).
5. Change (or create) your .bat file to include the /CONF option, this will be the fully qualified path each of your .dtsConfig files.
6. Rerun you .bat to make sure it works properly.

|||

SSIS Amateur wrote:

Jamie, when you say "manually edit the connection string", where are you talking about? Actually opening the .dtsConfig file and editing?

Yep. That's exactly what I mean.

DSN-less connection

I am looking to try and connect to SQL Server via a DSN-less
connection in a VB application.
Setting up ODBC data sources is a headache for each user workstation.
There has to be an easier way.
Is there a straight forward way to do that or do you know a good
reference source that would have information on this topic?

Thank you.RLN (rlntemp-newsgroup@.yahoo.com) writes:

Quote:

Originally Posted by

I am looking to try and connect to SQL Server via a DSN-less
connection in a VB application.
Setting up ODBC data sources is a headache for each user workstation.
There has to be an easier way.
Is there a straight forward way to do that or do you know a good
reference source that would have information on this topic?

What is the problem?

What means of authentication do you use? SQL Server authentication or
Windows authentication? Is the server always the one and the same? What is
the database?

Our application addresses all these problems by prompting the user. Isn't
that feasible for you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx