Dumb, but necessary, question on SSIS feature completeness

We have never built a data warehouse. We are not even sure what the required features are to do this. Nontheless, our developers are exploring two ETL tools: SSIS and Sunopsis.

They tend to like Sunopsis because it has been around a few years, has the equivalent of source-code modules (libraries) in the tool, has a highly granular security system, has 500 companies using it. and therefore must have survived several ETL projects. (We don't know if, or how many, real-world projects SSIS has been used for.). Sunopsis is Java based and uses its own authentication scheme (no support for Windows Active Directory yet. Bummer)

To the best of your knowledge is SSIS, out of the box, missing any of the standard functionalites needed to populate the datastore that will be used as the basis of our warehoue? (I don't even know precisely what all these functionalites are but my boss is worried we will find out somewhere down the road. While he recognizes that we can write our own code in .NET he prefers things "out of the box". They may not be fast but they tend to work right.)

TIA,

Barkingdog

I am biased, I like SSIS (most of the time) and have never used Sunopsis. Will it do what you want, I don't know, but since you admit you don't know what you want, why does that make Sunopsis a better choice?

I cannot compare the two for you, but I think SSIS allows you to do what you should need for any DW scenario, and yes custom code may play a part of that. It doesn't need to normally, but it often does. If you want a more feature rich system, less code, more mouse clicks, then perhaps Sunopsis is it, but I bet it comes at the price of flexibility and/or speed. Building tight or specific features that do not require or offer customisation, such as some code, naturally means you are tied into a specific pattern which may not meet your requirements.

|||I haven't used Sunopsis, so I also am commenting out of ignorance, but I can tell you one real problem with SSIS if you scale up past toy usage -- it doesn't really support reuse of components or logic(*). For example, if you want to use the same lookup followed by derived column transformation in 10 different places, you have to make 10 different copies, and make any changes to all 10 of them -- classic copy&paste "code reuse", which is about the worst kind of code reuse I think.

(*) You can reuse some logic by moving it into custom scripts, or into custom programmed components, but now you've certainly left the world of easy & graphical use, and gone back to the -- familiar :) -- world of real programming, and difficult debugging|||

>>> it doesn't really support reuse of components or logic(*).

Yes, that too was my concern! I didn't see any sense of code libraries or reusability (short of custom componenets) but I am too much an amateur in this area to know if it was simply my ignorance or SSIS reality.

Three tail wags and a hardy bark to you "tryanothername"!

|||

Also, I have included a link to Sunopsis only to show its extensive customer base. While the young SSIS can not beign to match this base numerically, does anyone know of a web "page" listing companies that have used SSIS for large projects?

http://sunopsis.com/corporate/us/company/references/

TIA,

Barkingdog

|||

I saw a demo of Sunopsis recently. The question isn't really one of features, its on of approach.

Sunopsis preach the gospel of ELT which means load the data into a staging table by hook or by crook (and that is what their libraries do) and then use the power of the database engine to do all your transformation.

SSIS certainly does not preclude the use of that approach but is more usually categorised as an ETL tool which means you can do the transformation prior to loading it into a database.

If you want a tool that can transform the data prior to insertion then you have no option - go with SSIS. If you are happy with the staging approach then you have a choice - SSIS or Sunopsis. I won't talk about which choice you should make here because I am obviously biased!

-Jamie

|||

barkingdog wrote:

>>> it doesn't really support reuse of components or logic(*).

Yes, that too was my concern! I didn't see any sense of code libraries or reusability (short of custom componenets) but I am too much an amateur in this area to know if it was simply my ignorance or SSIS reality.

Three tail wags and a hardy bark to you "tryanothername"!

Quite true. But remember that all Sunopsis' libraries do as far as I'm aware is get the data into a databse and as I'm sure you know SSIS has ample stuff available to do that. I would guess that SSIS is far far more feature rich than Sunopsis because Sunopsis basically relies on code to do the work.

With Sunopsis, once the data is in the database you have to write code (i.e. T-SQL) to do anything you want to do although I believe it does code generation for you. Just don't be under the impression that using Sunopsis means you'll have to write less code because if I understand it correctly the opposite is true.

-Jamie

|||

I use SSIS to do ELT because ETL is a real dog in terms of performance. I just finished re-engineering a Data import package, took out most of the scripting, moved some of the functionality to c# for better programming control and brought it down to 2.5 minutes from 35 minutes. Whatever tool you use, if the job is time critical, then you have to write some code and use ELT. I use SSIS to load the data into temp tables and use the powerful SQL engine to process the data. Lookups, deletes / updates / conversions are a breeze in SQL. Of course, you have to deal with some temp tables, but you can place them in a scratch database not to pollute your main database.

my 2 cents

-chiraj

|||

I found a list of some SSIS case studies that are interesting reading.

http://www.microsoft.com/sql/evaluation/casestudies/integration.aspx?ddiDirectoryID=389

Barkingdog

|||

chiraj wrote:

> I use SSIS to do ELT because ETL is a real dog in terms of performance.

My experience is certainly different from this, generalized into:

The SSIS data flow is generally faster or much faster than the database engine for:

Record based operations: manipulating a single record at a time such as string manipulation, Derived column component, Script component calling simple VBA.Net functions etc.

Dumb trigger question

Table with last UpdatedBy Column and LastUpdatedOn Column.
Want to create Insert and Update Triggers that would automatically write
Logged in User Name and System Date Time to each field.
I Think User is SUSER_SNAME function and System date Is GETDATE() but can't
figure out how to write these two to row from within a trigger in table.
Any help greatly appreciated.
BobYou can use the inserted psuedo-table to identify the updated rows. One
method:
CREATE TRIGGER TR__Update_MyTable
ON MyTable FOR UPDATE
AS
UPDATE MyTable
SET UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
WHERE EXISTS
(
SELECT *
FROM inserted
WHERE inserted.PK = MyTable.PK
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>|||Use an INSTEAD OF trigger. The deleted pseudotable is a copy of any
existing rows that are about to be updated, the inserted pseudotable
contains the new values. The deleted pseudotable will be empty if the
operation was INSERT. The inserted pseudotable will be empty if the
operation was DELETE. Both pseudotables will have the same number of rows
if the operation was UPDATE. The reason you should use an INSTEAD OF
trigger is that it allows you to change values before it hits the database.
The only problem with them is that the cascade kludge won't coexist, which
in my opinion is a good thing.
In an INSTEAD OF UPDATE trigger, you simply issue an update statement:
UPDATE tableName
SET column1 = inserted.column1,
column2 = inserted.column2,
..,
UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
FROM inserted
WHERE primaryKeyColumn = inserted.primaryKeyColumn
The only time this is a problem is if you're using natural keys. If that's
the case, then you need to test for multiple rows
put code similar to this at the top of the trigger
--Don't put anything before this!!!
DECLARE @._ROWCOUNT INT SET @._ROWCOUNT = @.@.ROWCOUNT
IF @._ROWCOUNT = 0 RETURN
IF @._ROWCOUNT > 1 AND (UPDATE(primaryKeyColumn1) OR
UPDATE(primaryKeyColumn2))
BEGIN
ROLLBACK TRANSACTION
RAISERROR('ATTEMPT TO UPDATE Primary Key using set-based operation', 16, 0)
RETURN
END
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>|||Thank you both very much
Bob
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>

Dumb Transaction log question.

I know I've been asking a lot of dumb questions here lately but - One must
learn somewhere.
In my test lab cluster that I'm building, W2k3 Cluster admin put each of my
physical disks in its own group. Now I'm keep one for the cluster group, and
one for the MSDTC, which leaves me four. I'm going to use 1 disk for data
and 1 disk for tranaction logs for each instance of sql. Are there any
advantages in putting the disks in the same group. (that is putting 1 disk
for the transaction logs and 1 disk for the data in the same group). I don't
see any advantages, but I'll ask anyway.
Also on transaction logs, where do I go to move the default location of
transaction logs in each instance of SQL.
The physical disk resources need to be in the same group as the SQL server resource that will use them, and the SQL server resource needs to have a dependancy on both physical disk resources.
As to the default locations, they are specified on the "Database Settings" tab of the Server Properties dialog in EM. (Not the "Edit registration properties" near the top of the right-click menu, but the "properties" near the bottom)
If you want to move database files or transaction logs, you can detach the database, move the files, and reattach the database files by specifying the new paths in the attach dialog box. Right-click on the database and select "all tasks >> detach" to detach the database. To attach the database, right click on the "databases" container and select "all tasks >> attach database".
You can also use T-SQL to call sp_detach and sp_attach, but the GUI works fine.
Just make sure that the SQL server resource has the physical disk resource(s) as a dependancy(ies) or you will not be able to attach the files.
Play around until you are comfortable with this stuff before you go live. It's quite geeky fun!
Good Luck
jg

Quote:

Originally posted by Wayne
I know I've been asking a lot of dumb questions here lately but - One must
learn somewhere.
In my test lab cluster that I'm building, W2k3 Cluster admin put each of my
physical disks in its own group. Now I'm keep one for the cluster group, and
one for the MSDTC, which leaves me four. I'm going to use 1 disk for data
and 1 disk for tranaction logs for each instance of sql. Are there any
advantages in putting the disks in the same group. (that is putting 1 disk
for the transaction logs and 1 disk for the data in the same group). I don't
see any advantages, but I'll ask anyway.
Also on transaction logs, where do I go to move the default location of
transaction logs in each instance of SQL.

|||There is no dumb question.
Its a good question. Since you are using one disk for data and another disk for log, both the disk resources will need to be in SAME SQL Server group (along with other SQL Server resources). Infact, if they are on
seperate group SQL Server cannot use it. During installation, we can just specify one disk. After installation, one has to move the second disk to the SQL group, take SQL Server resource offline, add the second disk
as a dependency for SQL Server resource and then take the SQL Server resource online. After that you can use your normal steps/techniques to move the logs on the second disk.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Dumb Stored Procedure Question

I've designed a very basic SQL Server Stored Procedure that I'm using
via a Visual Basic 6.0 front-end file in retrieving records into a
data entry form.

I can't for the life of me get the records retrieved via the Stored
Procedure to be edited.

I can't even run the stored procedure in MSDE's T-SQL utility and then
edit any of the records shown to me.

Any ideas?

Thanks!

Brad McCollum
bmccoll1@.midsouth.rr.comBrad H McCollum (bmccoll1@.midsouth.rr.com) writes:
> I've designed a very basic SQL Server Stored Procedure that I'm using
> via a Visual Basic 6.0 front-end file in retrieving records into a
> data entry form.
> I can't for the life of me get the records retrieved via the Stored
> Procedure to be edited.
> I can't even run the stored procedure in MSDE's T-SQL utility and then
> edit any of the records shown to me.

Since I don't know how your procedure looks like, I have to guess.

The problem is likelyl to be that ADO is to smart for its own good.
It thinks that each column must be associated with a table, so if you
say:

SELECT a, b + 'j' FROM tbl

The first column will be editable, the second not.

If you get the result set into a temp table and return from that, you
are OK. Nevermind that the temp table dies with the procedure. ADO is
easy to fool.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

dumb sql mistake or SQL Server error?

If I run the following I was hoping for the same result from both final
queries. The code below isn't what I am actually doing, what I'm actually
doing is putting values into a 'min' column of a data analysis table, and I
want to be able to put more than just numbers (for example, minimum dates) s
o
it should be easiest if that column is a varchar(100). But the error I'm
dealing with shows up with the following minimal code:
drop table #Values
go
create table #Values (value money)
go
insert into #Values Values (1)
insert into #Values Values (-21705503.4616)
go
select min(value) from #Values
select CAST(min(value) as varchar(100)) from #Values
The last two decimal places drop on the cast... That later ruins some stuff
I'm doing, I need all the decimal places to be there.
Any ideas? Am I missing something obvious or is this a SQL Server error?
It's such a simple thing really...
Thanks!First question: why are you converting from money to a varchar?
Second question: do you really need to use the money data type?
The money datattype is useful if you are storing currency delimiters
with your numeric data; if not, you can use numeric or decimal instead.
Stu|||WHOOPS.
Forgot to mention that if you must convert money to a character
datatype, use the CONVERT statement (not CAST) because it allows you to
specify a style.

>From the Books Online:
Value Output
0 (default) No commas every three digits to the left of the decimal
point, and two digits to the right of the decimal point; for example,
4235.98.
1 Commas every three digits to the left of the decimal point, and two
digits to the right of the decimal point; for example, 3,510.92.
2 No commas every three digits to the left of the decimal point, and
four digits to the right of the decimal point; for example, 4235.9819.
In your case,
SELECT CONVERT(varchar(100), MIN(value), 3) FROM #Values
HTH,
Stu|||Like I said above :) I have to use varchar because I have a column that is
going to have minimums of lots of different datatypes including datetime.
However, later I get values from this table and convert them back to their
real datatypes. It works in all cases except this.
This is an ETL process, the source data column is money, I don't have the
opportunity to change it. I am similarly not excited about money, I usually
strictly use numeric(9,2), numeric(9,4), numeric(19,2) numeric(19,4) and
with 6, etc. instead. I work for Company B who is doing the project for
Company A, so I definitely can't change the column type!!
So you get the same result running the code? If everyone gets the same
result seems like a bug to me.
"Stu" wrote:

> First question: why are you converting from money to a varchar?
> Second question: do you really need to use the money data type?
> The money datattype is useful if you are storing currency delimiters
> with your numeric data; if not, you can use numeric or decimal instead.
>
> Stu
>|||IMHO a more appropriate alternative would be to use the sql_variant datatype
,
if you really must store the values in a single column. It also 'remembers'
the original datatype.
That is, if you can live with the whole 8kB worth of sql-variant length.
ML
http://milambda.blogspot.com/|||Cool. So you are suggesting this line:
select convert(varchar(100), min(value), 2) from #Values
appended to the end of the sql I sent. That seems to work. This is
incredibly annoying because I now have to do this conditionally on the sourc
e
column data type. My actual code that does the min is inside some dynamic
sql and fires tens of times, not just once... Ughh... I can do it, though.
Microsoft: Update Cast on money->varchar so it will catch all decimal places
for money, make everyone's life easier :). Make default behavior no commas
and all significant figures beyond the decimal.
Thanks Stu!
"Stu" wrote:

> WHOOPS.
> Forgot to mention that if you must convert money to a character
> datatype, use the CONVERT statement (not CAST) because it allows you to
> specify a style.
>
> Value Output
> 0 (default) No commas every three digits to the left of the decimal
> point, and two digits to the right of the decimal point; for example,
> 4235.98.
> 1 Commas every three digits to the left of the decimal point, and two
> digits to the right of the decimal point; for example, 3,510.92.
> 2 No commas every three digits to the left of the decimal point, and
> four digits to the right of the decimal point; for example, 4235.9819.
>
> In your case,
> SELECT CONVERT(varchar(100), MIN(value), 3) FROM #Values
>
> HTH,
> Stu
>|||That sounds - so then the conversion is implicit and I would just updat
e
the column with no cast or convert? I'll play with that. That would be
great.
I am putting in the avg, min, max, stdev and other statistical information
about columns in this table, so it is not large although my statistical
analysis sp runs over tables that are quite large. The result table is no
bigger than 50X100, so 8kB is nothing.
Thanks!
"ML" wrote:

> IMHO a more appropriate alternative would be to use the sql_variant dataty
pe,
> if you really must store the values in a single column. It also 'remembers
'
> the original datatype.
> That is, if you can live with the whole 8kB worth of sql-variant length.
> ML
> --
> http://milambda.blogspot.com/|||It worked - I just changed the column data type in my 'summary table' and
touched no other code. Which is good as this is a 1000+ line stored
procedure and is really annoying to update...
"ML" wrote:

> IMHO a more appropriate alternative would be to use the sql_variant dataty
pe,
> if you really must store the values in a single column. It also 'remembers
'
> the original datatype.
> That is, if you can live with the whole 8kB worth of sql-variant length.
> ML
> --
> http://milambda.blogspot.com/|||Test well before use. :)
Also look up the SQL_VARIANT_PROPERTY system function in Books Online.
ML
http://milambda.blogspot.com/|||Hi
drop table #Values
go
create table #Values (value decimal(18,4))
go
insert into #Values Values (1)
insert into #Values Values (-21705503.4616)
go
select min(value) from #Values
select CAST(min(value) as varchar(100)) from #Values
"LightMiner" <LightMiner@.discussions.microsoft.com> wrote in message
news:61A8ED8F-08D2-456B-A540-B1EE1248902E@.microsoft.com...
> It worked - I just changed the column data type in my 'summary table' and
> touched no other code. Which is good as this is a 1000+ line stored
> procedure and is really annoying to update...
> "ML" wrote:
>

Dumb SQL 6.5 Question....

I have a mixed environment of mostly SQL 2000 server with a few (3?) SQL 6.5 servers (the ? is there because every now and again, I find a new, undocumented server hiding behind a firewall that some developer just happens to be having an issue with).

Can I install SQL 6.5 client tools alongside SQL 2000 client tools on the same server?

Regards,

hmscottClient tools for 6.5 will live happily with 2K, except in my situation I always had 6.5 already installed, and just added 2K. I am not sure if adding 2K to 6.5 tools would yield the same success.

Dumb questions on attribute relationships and multiple hierachy relationships

Hi,

This is a couple of general questions about how to create robust attribute hierarchies. I'm sure the answera are somewhere on the MS web sites, but I can't find it.

1) How precisely does the inclusion of attribute relationships affect the design of aggregations and the resulting speed?

I'm led to believe that defining the attribute relationships will effectively force AS to use these hierarchies when creating aggregations, and so putting them in improves the speed. This seems to be the case - any known best practices?

2) What do all the properties such as "Fully Optimised" for attributes mean?

3) Imagine have, for example, a time dimension table which has Year, Month, Week, Date columns on it, where weeks do not roll into months, but weeks and months roll into years. I build two user defined hierarchies Year-Month-Date and Year-Week-Date, then when I try to put attribute relationships on both these tables, I can get odd results, for example all the weeks appearing in the first year only.

This may be finger trouble, or complete stupidity, but has anyone else seen this? The actual case was not time as above, but was a similar structure.

As ever, thanks for all the input.

Richard

1) If you defined relationship between say your State and your City. And if you are asking for the State data, you can get answer from aggregation that contains City, or from cache created as result of City request. Now if you've created relationships for all the attributes in your dimensions, you need few aggregations to cover big space of potential requests. And therefore you can achieve performance. It is almost imperative to create attribute relationships.

2) Fully Optimized tells the server whenever to build indexes for this attribute or not. Sometimes you might want your attribute to exist but to be hidden. In this case makes sense to tell the server not to create indexes for it.

3) Not sure what you mean here. If you have correct relationships in the relational database, you should get right results when you browse you cube. Please note, when you define your relationships Analysis Server will try and find a parent for every individual week member. For instance if you have "Week 1 Dec 2001" it will have Dec 2001 parent in Week-Month hierarchy , or 2001 in Week-Year hierarchy.

Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||To add a comment on 3: This normally indicates a problem with a key. AS 2005 is very sensitive with keys. "1" may be a bad key for a week... "200601" is a good key... (the same applies to months)...|||

refering to question #3

It sounds like you have not defined the key correctly. It needs to be unique.

so in the case of your hierarchy of year>month>week>date you need to define the key

for month and week to also include the year. In AS2005 you cannot rely on the hierarchy

to define a unique attribute. If you do a search on time dimension you should get much

more details on how to correctly define it.

Dumb questions on attribute relationships and multiple hierachy relationships

Hi,

This is a couple of general questions about how to create robust attribute hierarchies. I'm sure the answera are somewhere on the MS web sites, but I can't find it.

1) How precisely does the inclusion of attribute relationships affect the design of aggregations and the resulting speed?

I'm led to believe that defining the attribute relationships will effectively force AS to use these hierarchies when creating aggregations, and so putting them in improves the speed. This seems to be the case - any known best practices?

2) What do all the properties such as "Fully Optimised" for attributes mean?

3) Imagine have, for example, a time dimension table which has Year, Month, Week, Date columns on it, where weeks do not roll into months, but weeks and months roll into years. I build two user defined hierarchies Year-Month-Date and Year-Week-Date, then when I try to put attribute relationships on both these tables, I can get odd results, for example all the weeks appearing in the first year only.

This may be finger trouble, or complete stupidity, but has anyone else seen this? The actual case was not time as above, but was a similar structure.

As ever, thanks for all the input.

Richard

1) If you defined relationship between say your State and your City. And if you are asking for the State data, you can get answer from aggregation that contains City, or from cache created as result of City request. Now if you've created relationships for all the attributes in your dimensions, you need few aggregations to cover big space of potential requests. And therefore you can achieve performance. It is almost imperative to create attribute relationships.

2) Fully Optimized tells the server whenever to build indexes for this attribute or not. Sometimes you might want your attribute to exist but to be hidden. In this case makes sense to tell the server not to create indexes for it.

3) Not sure what you mean here. If you have correct relationships in the relational database, you should get right results when you browse you cube. Please note, when you define your relationships Analysis Server will try and find a parent for every individual week member. For instance if you have "Week 1 Dec 2001" it will have Dec 2001 parent in Week-Month hierarchy , or 2001 in Week-Year hierarchy.

Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||To add a comment on 3: This normally indicates a problem with a key. AS 2005 is very sensitive with keys. "1" may be a bad key for a week... "200601" is a good key... (the same applies to months)...|||

refering to question #3

It sounds like you have not defined the key correctly. It needs to be unique.

so in the case of your hierarchy of year>month>week>date you need to define the key

for month and week to also include the year. In AS2005 you cannot rely on the hierarchy

to define a unique attribute. If you do a search on time dimension you should get much

more details on how to correctly define it.

Dumb Question?

Does anyone know of amy SQL Code that will drop all the records in all the
tables at once and leave the db structure in place?
sp_msforeachtable 'TRUNCATE TABLE ?'
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?
|||truncate table may not work if the table is referenced by foreign keys. You
may try scripting out the schemas and run it in a separate database to
create all table structures.
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?

Dumb Question?

Does anyone know of amy SQL Code that will drop all the records in all the
tables at once and leave the db structure in place?sp_msforeachtable 'TRUNCATE TABLE ?'
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?|||truncate table may not work if the table is referenced by foreign keys. You
may try scripting out the schemas and run it in a separate database to
create all table structures.
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?

Dumb question: Sanpshot reports

Will a report based on a scheduled snapshot send itself out (either to an email or data space) if the data in the new snap shot is the same as the previous?

If so is there any method to only send out a report if the data has changed?

And if not what do most people use snap shot based subscriptions for?

Thanks.

1.) Yes.

2.) You can try to use Data Driven Subscriptions. What you will have to do is have the query which generates the list of recipients detect if the data has changed, and return an empty set if this is the case. SSRS will not really help you do this, so you will need to cook up your own way of determining if the data has changed since the last report went out.

Dumb question: How do I view a report?

I have SSRS installed, but when I try to navigate to it via IE, I'm prompted
for my username and password even though I'm using Windows integrated
security for SQL Server. What do I need to do to create/access reports?Never mind. I'd typed in http://localhost/ReportServices$SQLSERVER2005 when
I was prompted for a username and password. I should've typed in
http://localhost/Reports$SQLSERVER2005/Pages/ReportViewer.aspx ...
As I said, it's a dumb question ... hahaha.
"AlBruAn" wrote:
> I have SSRS installed, but when I try to navigate to it via IE, I'm prompted
> for my username and password even though I'm using Windows integrated
> security for SQL Server. What do I need to do to create/access reports?|||On Apr 9, 1:22 pm, AlBruAn <albr...@.hotmail.com.(donotspam)> wrote:
> I have SSRS installed, but when I try to navigate to it via IE, I'm prompted
> for my username and password even though I'm using Windows integrated
> security for SQL Server. What do I need to do to create/access reports?
If you are using a browser other than IE, this may occur. If not, then
you will want to check the Directory Security (for the Reports and
ReportServer virtual directories). You do this via: Right-click My
Computer -> Select Manage -> Navigate to Services and Applications ->
Internet Information Services -> Web Sites -> Default Web Site. Then,
right-click Reports (or ReportServer) and select Properties -> select
the Directory Security tab -> select the Edit button just below
'Anonymous access and authentication control' and check to see if you
have either 'Anonymous access' selected or 'Integrated Windows
Authentication.' If anonymous is selected, most likely SSRS will not
allow you to avoid providing authentication (if it allows navigation
to the URL at all). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

dumb question...

After configuring exactly as specified in reporting services custom security, when I try to hit report manager, I keep on getting the error:

Invalid URI: The format of the URI could not be determined.

It does not redirct me to UILogin.aspx page but forwards me to Pages/Folder.aspx and gives this error.

Please if possible let me know ASAP.

I have the following in "rsreportserver.config" file

<UrlRoot>http://<computername>/reportserver</UrlRoot>

I have tried using "localhost" instead of computer name in the above line. Secondly, when I hit report manager, I thought it should redirect me to UILogin.aspx. It does not. And even if it did, I would like to know how it would do that because in report server, you specify the url in the <forms> tag. here there is no such tag that would make it redirect to UILogon.aspx.

I have the following in Web.config file of Report Manager

<authentication mode="Windows" />
<identity impersonate="false" />

I have the following in RsWebApplication.config file

<UI>
<ReportServerUrl></ReportServerUrl>
<ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
<CustomAuthenticationUI>
<loginUrl>UILogin.aspx</loginUrl>
<UseSSL>False</UseSSL>
</CustomAuthenticationUI>
</UI>

Thanks.

Thanks.

Solved it

There were 2 issues

the UILogon.aspx file was not under pages(wonder what that would do?)

Secondly, changed the ordering in RSWebApplication.config file to

<UI>
<CustomAuthenticationUI>
<loginUrl>/Pages/UILogon.aspx</loginUrl>
<UseSSL>False</UseSSL>
</CustomAuthenticationUI>
<ReportServerUrl>http://<computername>/ReportServer</ReportServerUrl>
</UI>

Finally, in the url requested

http://<computername>/Reports

|||

I been trying to get my own sql reporting services going... but i have so many issues..

the dumb question: does SQL 2005 Reporting Services require IIS 6.0 ? Windows 2003?

thanks

|||

Should work on xp pro . no need for windows 2003. For production deployment however, consider deploying enterprise version on windows server 2003.

Also always use iis6.0

|||

so, iis5 does not work?

dumb question, but I need help

Help people,

I am trying to figure out the syntax to declare a @.table varible with a primary key clusterd

Hence,

declare @.RANK_FIGROSS_TEST table
{
RANK int NOT NULL IDENTITY (1, 1)|PRIMARY KEY CLUSTERED,
FINAME nvarchar(255) NULL,
FIGROSS decimal (10,2)
}

But I am not having any luck, anyone know what I am missing ??should be ( and not { and no | for the primary key declaration

here is the code modified:

declare @.RANK_FIGROSS_TEST table
(
RANK int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
FINAME nvarchar(255) NULL,
FIGROSS decimal (10,2))|||that worked, told you it was a stupid question, just couldn't see it.

Thanks|||no such thing as a stupid question :-) we all have to learn somehow|||hmm.... I did even more stupid "ER" things than u ever imagined!!!
:)

Dumb question about SSIS

I just imported data from one database to another, I specified to save the
SSIS package on the database server (not on the file system).
Now I cannot find the package anywhere. There isn't a "Local Package" tree
like in 2000 Enterprise Manager. I can't even find a shortcut for starting
SSIS in my Start menu, and the SSIS Tutorial only shows how to create a new
project in VS, not how to open an existing package.
Hello,
You could click "Connect" button in Server management studio and click to
select "Integration services", and then type the instance information you
want.
Hope this is helpful.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==
>X-WBNR-Posting-Host: 84.56.32.28
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>Subject: Dumb question about SSIS
>Date: Fri, 25 Nov 2005 10:56:02 -0800
>Lines: 7
>Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I just imported data from one database to another, I specified to save the
>SSIS package on the database server (not on the file system).
>Now I cannot find the package anywhere. There isn't a "Local Package" tree
>like in 2000 Enterprise Manager. I can't even find a shortcut for starting
>SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
new
>project in VS, not how to open an existing package.
>
|||Thanks Peter, exactly what I was looking for.
But now I still cannot see my package, as I'm getting following error
message when clicking on MSDB under "Stored Packages" in the Object Explorer
tree. Any idea why?
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.Applicatio nClass.GetDtsServerPackageInfos(String bstrPackageFolder, String bstrServerName)
at
Microsoft.SqlServer.Dts.Runtime.Application.GetDts ServerPackageInfos(String
sPackageFolder, String sServerName)
"Peter Yang [MSFT]" wrote:

> Hello,
> You could click "Connect" button in Server management studio and click to
> select "Integration services", and then type the instance information you
> want.
> Hope this is helpful.
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> new
>
|||Hello,
To understand the issue better, will you confirm the default instance is
SQL server 2005 instance? SSIS does not support named instance anyway.
Also, from the error message, it seems that client force encryption but
server does not have certificate installed. Please check the following
settings:
Run SQL Server 2005 Network Configuration, right click SQL native client
network conigurations->Properties, and configure "force protocol
encryption" to NO.
Right click the protocol under server network configuration and also
configure "force protocol encryption" to NO.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==
>X-WBNR-Posting-Host: 84.147.181.254
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Mon, 28 Nov 2005 04:31:02 -0800
>Lines: 76
>Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks Peter, exactly what I was looking for.
>But now I still cannot see my package, as I'm getting following error
>message when clicking on MSDB under "Stored Packages" in the Object
Explorer
>tree. Any idea why?
>Client unable to establish connection
>Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> at
>Microsoft.SqlServer.Dts.Runtime.Wrapper.Applicati onClass.GetDtsServerPackag
eInfos(String bstrPackageFolder, String bstrServerName)
> at
>Microsoft.SqlServer.Dts.Runtime.Application.GetDt sServerPackageInfos(String
[vbcol=seagreen]
>sPackageFolder, String sServerName)
>"Peter Yang [MSFT]" wrote:
to[vbcol=seagreen]
you[vbcol=seagreen]
rights.[vbcol=seagreen]
the[vbcol=seagreen]
tree[vbcol=seagreen]
starting
>
|||Both encryption settings were set to No.
Also, SQL Server 2005 is a named instance. The default instance is SQL
Server 2000.
In the "Connect to Server" dialog of management studio, I connect to
Integration Service, and as server name, the name of the machine. Does that
mean that I'm attempting to connect to the default instance? That would be
SQL 2000.
Does that mean that you cannot use SSIS on a machine where there is default
instance of SQL 2000?
"Peter Yang [MSFT]" wrote:

> Hello,
> To understand the issue better, will you confirm the default instance is
> SQL server 2005 instance? SSIS does not support named instance anyway.
> Also, from the error message, it seems that client force encryption but
> server does not have certificate installed. Please check the following
> settings:
> Run SQL Server 2005 Network Configuration, right click SQL native client
> network conigurations->Properties, and configure "force protocol
> encryption" to NO.
> Right click the protocol under server network configuration and also
> configure "force protocol encryption" to NO.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> Explorer
> eInfos(String bstrPackageFolder, String bstrServerName)
> to
> you
> rights.
> the
> tree
> starting
>
|||Hello,
You could still store SSIS package in filesystem other than msdb under this
situation. Since SSIS will try to access msde of default instance, you
could not store the package in msdb because it is a 2000 database.
Also, you may try the following method to change the instance SSIS
connects. However, it is not officially documented and may have issues when
running package from there
1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn
2. Change the string <ServerName>.</ServerName>?as following:
<ServerName>MachineName\InstanceName</ServerName>
3. Save the file and Re-Start SSIS Service
Please let me know if you have further concerns or questions on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX02Vvn5mIYncaZQVq2jfNzlN2UsA==
>X-WBNR-Posting-Host: 84.147.129.198
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
<36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
<yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Tue, 29 Nov 2005 03:38:03 -0800
>Lines: 159
>Message-ID: <43861D30-F658-4906-B197-CE2D9DE8D97C@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412564
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Both encryption settings were set to No.
>Also, SQL Server 2005 is a named instance. The default instance is SQL
>Server 2000.
>In the "Connect to Server" dialog of management studio, I connect to
>Integration Service, and as server name, the name of the machine. Does
that
>mean that I'm attempting to connect to the default instance? That would be
>SQL 2000.
>Does that mean that you cannot use SSIS on a machine where there is
default[vbcol=seagreen]
>instance of SQL 2000?
>
>"Peter Yang [MSFT]" wrote:
rights.[vbcol=seagreen]
>Microsoft.SqlServer.Dts.Runtime.Wrapper.Applicati onClass.GetDtsServerPackag
>Microsoft.SqlServer.Dts.Runtime.Application.GetDt sServerPackageInfos(String
click[vbcol=seagreen]
so[vbcol=seagreen]
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl[vbcol=seagreen]
save[vbcol=seagreen]
Package"[vbcol=seagreen]
create a
>
|||This sounds like a solution. Many thanks.
"Peter Yang [MSFT]" wrote:

> Hello,
> You could still store SSIS package in filesystem other than msdb under this
> situation. Since SSIS will try to access msde of default instance, you
> could not store the package in msdb because it is a 2000 database.
> Also, you may try the following method to change the instance SSIS
> connects. However, it is not officially documented and may have issues when
> running package from there
> 1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
> C:\Program Files\Microsoft SQL Server\90\DTS\Binn
> 2. Change the string <ServerName>.</ServerName>?as following:
> <ServerName>MachineName\InstanceName</ServerName>
> 3. Save the file and Re-Start SSIS Service
> Please let me know if you have further concerns or questions on this.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> <yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> that
> default
> rights.
> click
> so
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
> save
> Package"
> create a
>

Dumb question about SSIS

I just imported data from one database to another, I specified to save the
SSIS package on the database server (not on the file system).
Now I cannot find the package anywhere. There isn't a "Local Package" tree
like in 2000 Enterprise Manager. I can't even find a shortcut for starting
SSIS in my Start menu, and the SSIS Tutorial only shows how to create a new
project in VS, not how to open an existing package.Hello,
You could click "Connect" button in Server management studio and click to
select "Integration services", and then type the instance information you
want.
Hope this is helpful.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Dumb question about SSIS
>thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==>X-WBNR-Posting-Host: 84.56.32.28
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>Subject: Dumb question about SSIS
>Date: Fri, 25 Nov 2005 10:56:02 -0800
>Lines: 7
>Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I just imported data from one database to another, I specified to save the
>SSIS package on the database server (not on the file system).
>Now I cannot find the package anywhere. There isn't a "Local Package" tree
>like in 2000 Enterprise Manager. I can't even find a shortcut for starting
>SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
new
>project in VS, not how to open an existing package.
>|||Thanks Peter, exactly what I was looking for.
But now I still cannot see my package, as I'm getting following error
message when clicking on MSDB under "Stored Packages" in the Object Explorer
tree. Any idea why?
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackageInfos(String bstrPackageFolder, String bstrServerName)
at
Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
sPackageFolder, String sServerName)
"Peter Yang [MSFT]" wrote:
> Hello,
> You could click "Connect" button in Server management studio and click to
> select "Integration services", and then type the instance information you
> want.
> Hope this is helpful.
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> >Thread-Topic: Dumb question about SSIS
> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==> >X-WBNR-Posting-Host: 84.56.32.28
> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >Subject: Dumb question about SSIS
> >Date: Fri, 25 Nov 2005 10:56:02 -0800
> >Lines: 7
> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >I just imported data from one database to another, I specified to save the
> >SSIS package on the database server (not on the file system).
> >Now I cannot find the package anywhere. There isn't a "Local Package" tree
> >like in 2000 Enterprise Manager. I can't even find a shortcut for starting
> >SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
> new
> >project in VS, not how to open an existing package.
> >
> >
>|||Hello,
To understand the issue better, will you confirm the default instance is
SQL server 2005 instance? SSIS does not support named instance anyway.
Also, from the error message, it seems that client force encryption but
server does not have certificate installed. Please check the following
settings:
Run SQL Server 2005 Network Configuration, right click SQL native client
network conigurations->Properties, and configure "force protocol
encryption" to NO.
Right click the protocol under server network configuration and also
configure "force protocol encryption" to NO.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==>X-WBNR-Posting-Host: 84.147.181.254
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Mon, 28 Nov 2005 04:31:02 -0800
>Lines: 76
>Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks Peter, exactly what I was looking for.
>But now I still cannot see my package, as I'm getting following error
>message when clicking on MSDB under "Stored Packages" in the Object
Explorer
>tree. Any idea why?
>Client unable to establish connection
>Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> at
>Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
eInfos(String bstrPackageFolder, String bstrServerName)
> at
>Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
>sPackageFolder, String sServerName)
>"Peter Yang [MSFT]" wrote:
>> Hello,
>> You could click "Connect" button in Server management studio and click
to
>> select "Integration services", and then type the instance information
you
>> want.
>> Hope this is helpful.
>>
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>>
>> --
>> >Thread-Topic: Dumb question about SSIS
>> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==>> >X-WBNR-Posting-Host: 84.56.32.28
>> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>> >Subject: Dumb question about SSIS
>> >Date: Fri, 25 Nov 2005 10:56:02 -0800
>> >Lines: 7
>> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.server
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >I just imported data from one database to another, I specified to save
the
>> >SSIS package on the database server (not on the file system).
>> >Now I cannot find the package anywhere. There isn't a "Local Package"
tree
>> >like in 2000 Enterprise Manager. I can't even find a shortcut for
starting
>> >SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
>> new
>> >project in VS, not how to open an existing package.
>> >
>> >
>>
>|||Both encryption settings were set to No.
Also, SQL Server 2005 is a named instance. The default instance is SQL
Server 2000.
In the "Connect to Server" dialog of management studio, I connect to
Integration Service, and as server name, the name of the machine. Does that
mean that I'm attempting to connect to the default instance? That would be
SQL 2000.
Does that mean that you cannot use SSIS on a machine where there is default
instance of SQL 2000'
"Peter Yang [MSFT]" wrote:
> Hello,
> To understand the issue better, will you confirm the default instance is
> SQL server 2005 instance? SSIS does not support named instance anyway.
> Also, from the error message, it seems that client force encryption but
> server does not have certificate installed. Please check the following
> settings:
> Run SQL Server 2005 Network Configuration, right click SQL native client
> network conigurations->Properties, and configure "force protocol
> encryption" to NO.
> Right click the protocol under server network configuration and also
> configure "force protocol encryption" to NO.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> >Thread-Topic: Dumb question about SSIS
> >thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==> >X-WBNR-Posting-Host: 84.147.181.254
> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> >Subject: RE: Dumb question about SSIS
> >Date: Mon, 28 Nov 2005 04:31:02 -0800
> >Lines: 76
> >Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Thanks Peter, exactly what I was looking for.
> >But now I still cannot see my package, as I'm getting following error
> >message when clicking on MSDB under "Stored Packages" in the Object
> Explorer
> >tree. Any idea why?
> >
> >Client unable to establish connection
> >Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> >
> > at
> >Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
> eInfos(String bstrPackageFolder, String bstrServerName)
> > at
> >Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
> >sPackageFolder, String sServerName)
> >
> >"Peter Yang [MSFT]" wrote:
> >
> >> Hello,
> >>
> >> You could click "Connect" button in Server management studio and click
> to
> >> select "Integration services", and then type the instance information
> you
> >> want.
> >>
> >> Hope this is helpful.
> >>
> >>
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> =====================================================> >>
> >>
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >>
> >>
> >> --
> >> >Thread-Topic: Dumb question about SSIS
> >> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==> >> >X-WBNR-Posting-Host: 84.56.32.28
> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >> >Subject: Dumb question about SSIS
> >> >Date: Fri, 25 Nov 2005 10:56:02 -0800
> >> >Lines: 7
> >> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> >> >MIME-Version: 1.0
> >> >Content-Type: text/plain;
> >> > charset="Utf-8"
> >> >Content-Transfer-Encoding: 7bit
> >> >X-Newsreader: Microsoft CDO for Windows 2000
> >> >Content-Class: urn:content-classes:message
> >> >Importance: normal
> >> >Priority: normal
> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >> >Newsgroups: microsoft.public.sqlserver.server
> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >> >
> >> >I just imported data from one database to another, I specified to save
> the
> >> >SSIS package on the database server (not on the file system).
> >> >Now I cannot find the package anywhere. There isn't a "Local Package"
> tree
> >> >like in 2000 Enterprise Manager. I can't even find a shortcut for
> starting
> >> >SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
> >> new
> >> >project in VS, not how to open an existing package.
> >> >
> >> >
> >>
> >>
> >
>|||Hello,
You could still store SSIS package in filesystem other than msdb under this
situation. Since SSIS will try to access msde of default instance, you
could not store the package in msdb because it is a 2000 database.
Also, you may try the following method to change the instance SSIS
connects. However, it is not officially documented and may have issues when
running package from there
1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn
2. Change the string <ServerName>.</ServerName>?as following:
<ServerName>MachineName\InstanceName</ServerName>
3. Save the file and Re-Start SSIS Service
Please let me know if you have further concerns or questions on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX02Vvn5mIYncaZQVq2jfNzlN2UsA==>X-WBNR-Posting-Host: 84.147.129.198
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
<36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
<yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Tue, 29 Nov 2005 03:38:03 -0800
>Lines: 159
>Message-ID: <43861D30-F658-4906-B197-CE2D9DE8D97C@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412564
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Both encryption settings were set to No.
>Also, SQL Server 2005 is a named instance. The default instance is SQL
>Server 2000.
>In the "Connect to Server" dialog of management studio, I connect to
>Integration Service, and as server name, the name of the machine. Does
that
>mean that I'm attempting to connect to the default instance? That would be
>SQL 2000.
>Does that mean that you cannot use SSIS on a machine where there is
default
>instance of SQL 2000'
>
>"Peter Yang [MSFT]" wrote:
>> Hello,
>> To understand the issue better, will you confirm the default instance is
>> SQL server 2005 instance? SSIS does not support named instance anyway.
>> Also, from the error message, it seems that client force encryption but
>> server does not have certificate installed. Please check the following
>> settings:
>> Run SQL Server 2005 Network Configuration, right click SQL native client
>> network conigurations->Properties, and configure "force protocol
>> encryption" to NO.
>> Right click the protocol under server network configuration and also
>> configure "force protocol encryption" to NO.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>>
>> --
>> >Thread-Topic: Dumb question about SSIS
>> >thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==>> >X-WBNR-Posting-Host: 84.147.181.254
>> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>> >Subject: RE: Dumb question about SSIS
>> >Date: Mon, 28 Nov 2005 04:31:02 -0800
>> >Lines: 76
>> >Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.server
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >Thanks Peter, exactly what I was looking for.
>> >But now I still cannot see my package, as I'm getting following error
>> >message when clicking on MSDB under "Stored Packages" in the Object
>> Explorer
>> >tree. Any idea why?
>> >
>> >Client unable to establish connection
>> >Encryption not supported on SQL Server. (Microsoft SQL Native Client)
>> >
>> > at
>Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
>> eInfos(String bstrPackageFolder, String bstrServerName)
>> > at
>Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
>> >sPackageFolder, String sServerName)
>> >
>> >"Peter Yang [MSFT]" wrote:
>> >
>> >> Hello,
>> >>
>> >> You could click "Connect" button in Server management studio and
click
>> to
>> >> select "Integration services", and then type the instance information
>> you
>> >> want.
>> >>
>> >> Hope this is helpful.
>> >>
>> >>
>> >> Best Regards,
>> >>
>> >> Peter Yang
>> >> MCSE2000/2003, MCSA, MCDBA
>> >> Microsoft Online Partner Support
>> >>
>> >> When responding to posts, please "Reply to Group" via your newsreader
so
>> >> that others may learn and benefit from your issue.
>> >>
>> >> =====================================================>> >>
>> >>
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >>
>> >>
>> >> --
>> >> >Thread-Topic: Dumb question about SSIS
>> >> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==>> >> >X-WBNR-Posting-Host: 84.56.32.28
>> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>> >> >Subject: Dumb question about SSIS
>> >> >Date: Fri, 25 Nov 2005 10:56:02 -0800
>> >> >Lines: 7
>> >> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>> >> >MIME-Version: 1.0
>> >> >Content-Type: text/plain;
>> >> > charset="Utf-8"
>> >> >Content-Transfer-Encoding: 7bit
>> >> >X-Newsreader: Microsoft CDO for Windows 2000
>> >> >Content-Class: urn:content-classes:message
>> >> >Importance: normal
>> >> >Priority: normal
>> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >> >Newsgroups: microsoft.public.sqlserver.server
>> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >> >Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >> >
>> >> >I just imported data from one database to another, I specified to
save
>> the
>> >> >SSIS package on the database server (not on the file system).
>> >> >Now I cannot find the package anywhere. There isn't a "Local
Package"
>> tree
>> >> >like in 2000 Enterprise Manager. I can't even find a shortcut for
>> starting
>> >> >SSIS in my Start menu, and the SSIS Tutorial only shows how to
create a
>> >> new
>> >> >project in VS, not how to open an existing package.
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>|||This sounds like a solution. Many thanks.
"Peter Yang [MSFT]" wrote:
> Hello,
> You could still store SSIS package in filesystem other than msdb under this
> situation. Since SSIS will try to access msde of default instance, you
> could not store the package in msdb because it is a 2000 database.
> Also, you may try the following method to change the instance SSIS
> connects. However, it is not officially documented and may have issues when
> running package from there
> 1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
> C:\Program Files\Microsoft SQL Server\90\DTS\Binn
> 2. Change the string <ServerName>.</ServerName>?as following:
> <ServerName>MachineName\InstanceName</ServerName>
> 3. Save the file and Re-Start SSIS Service
> Please let me know if you have further concerns or questions on this.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> >Thread-Topic: Dumb question about SSIS
> >thread-index: AcX02Vvn5mIYncaZQVq2jfNzlN2UsA==> >X-WBNR-Posting-Host: 84.147.129.198
> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> <yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> >Subject: RE: Dumb question about SSIS
> >Date: Tue, 29 Nov 2005 03:38:03 -0800
> >Lines: 159
> >Message-ID: <43861D30-F658-4906-B197-CE2D9DE8D97C@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412564
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Both encryption settings were set to No.
> >Also, SQL Server 2005 is a named instance. The default instance is SQL
> >Server 2000.
> >In the "Connect to Server" dialog of management studio, I connect to
> >Integration Service, and as server name, the name of the machine. Does
> that
> >mean that I'm attempting to connect to the default instance? That would be
> >SQL 2000.
> >Does that mean that you cannot use SSIS on a machine where there is
> default
> >instance of SQL 2000'
> >
> >
> >"Peter Yang [MSFT]" wrote:
> >
> >> Hello,
> >>
> >> To understand the issue better, will you confirm the default instance is
> >> SQL server 2005 instance? SSIS does not support named instance anyway.
> >>
> >> Also, from the error message, it seems that client force encryption but
> >> server does not have certificate installed. Please check the following
> >> settings:
> >>
> >> Run SQL Server 2005 Network Configuration, right click SQL native client
> >> network conigurations->Properties, and configure "force protocol
> >> encryption" to NO.
> >>
> >> Right click the protocol under server network configuration and also
> >> configure "force protocol encryption" to NO.
> >>
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> =====================================================> >>
> >>
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >>
> >>
> >> --
> >> >Thread-Topic: Dumb question about SSIS
> >> >thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==> >> >X-WBNR-Posting-Host: 84.147.181.254
> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> >> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> >> >Subject: RE: Dumb question about SSIS
> >> >Date: Mon, 28 Nov 2005 04:31:02 -0800
> >> >Lines: 76
> >> >Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> >> >MIME-Version: 1.0
> >> >Content-Type: text/plain;
> >> > charset="Utf-8"
> >> >Content-Transfer-Encoding: 7bit
> >> >X-Newsreader: Microsoft CDO for Windows 2000
> >> >Content-Class: urn:content-classes:message
> >> >Importance: normal
> >> >Priority: normal
> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >> >Newsgroups: microsoft.public.sqlserver.server
> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >> >
> >> >Thanks Peter, exactly what I was looking for.
> >> >But now I still cannot see my package, as I'm getting following error
> >> >message when clicking on MSDB under "Stored Packages" in the Object
> >> Explorer
> >> >tree. Any idea why?
> >> >
> >> >Client unable to establish connection
> >> >Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> >> >
> >> > at
> >>
> >Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
> >> eInfos(String bstrPackageFolder, String bstrServerName)
> >> > at
> >>
> >Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
> >>
> >> >sPackageFolder, String sServerName)
> >> >
> >> >"Peter Yang [MSFT]" wrote:
> >> >
> >> >> Hello,
> >> >>
> >> >> You could click "Connect" button in Server management studio and
> click
> >> to
> >> >> select "Integration services", and then type the instance information
> >> you
> >> >> want.
> >> >>
> >> >> Hope this is helpful.
> >> >>
> >> >>
> >> >> Best Regards,
> >> >>
> >> >> Peter Yang
> >> >> MCSE2000/2003, MCSA, MCDBA
> >> >> Microsoft Online Partner Support
> >> >>
> >> >> When responding to posts, please "Reply to Group" via your newsreader
> so
> >> >> that others may learn and benefit from your issue.
> >> >>
> >> >> =====================================================> >> >>
> >> >>
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> >>
> >> >>
> >> >> --
> >> >> >Thread-Topic: Dumb question about SSIS
> >> >> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==> >> >> >X-WBNR-Posting-Host: 84.56.32.28
> >> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >> >> >Subject: Dumb question about SSIS
> >> >> >Date: Fri, 25 Nov 2005 10:56:02 -0800
> >> >> >Lines: 7
> >> >> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> >> >> >MIME-Version: 1.0
> >> >> >Content-Type: text/plain;
> >> >> > charset="Utf-8"
> >> >> >Content-Transfer-Encoding: 7bit
> >> >> >X-Newsreader: Microsoft CDO for Windows 2000
> >> >> >Content-Class: urn:content-classes:message
> >> >> >Importance: normal
> >> >> >Priority: normal
> >> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >> >> >Newsgroups: microsoft.public.sqlserver.server
> >> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> >> >Path:
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
> >> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >> >> >
> >> >> >I just imported data from one database to another, I specified to
> save
> >> the
> >> >> >SSIS package on the database server (not on the file system).
> >> >> >Now I cannot find the package anywhere. There isn't a "Local
> Package"
> >> tree
> >> >> >like in 2000 Enterprise Manager. I can't even find a shortcut for
> >> starting
> >> >> >SSIS in my Start menu, and the SSIS Tutorial only shows how to
> create a
> >> >> new
> >> >> >project in VS, not how to open an existing package.
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >>
> >
>

Dumb question about SSIS

I just imported data from one database to another, I specified to save the
SSIS package on the database server (not on the file system).
Now I cannot find the package anywhere. There isn't a "Local Package" tree
like in 2000 Enterprise Manager. I can't even find a shortcut for starting
SSIS in my Start menu, and the SSIS Tutorial only shows how to create a new
project in VS, not how to open an existing package.Hello,
You could click "Connect" button in Server management studio and click to
select "Integration services", and then type the instance information you
want.
Hope this is helpful.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==
>X-WBNR-Posting-Host: 84.56.32.28
>From: "examnotes" <aa1948@.nospam.nospam>
>Subject: Dumb question about SSIS
>Date: Fri, 25 Nov 2005 10:56:02 -0800
>Lines: 7
>Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I just imported data from one database to another, I specified to save the
>SSIS package on the database server (not on the file system).
>Now I cannot find the package anywhere. There isn't a "Local Package" tree
>like in 2000 Enterprise Manager. I can't even find a shortcut for starting
>SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
new
>project in VS, not how to open an existing package.
>|||Thanks Peter, exactly what I was looking for.
But now I still cannot see my package, as I'm getting following error
message when clicking on MSDB under "Stored Packages" in the Object Explorer
tree. Any idea why?
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackage
Infos(String bstrPackageFolder, String bstrServerName)
at
Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
sPackageFolder, String sServerName)
"Peter Yang [MSFT]" wrote:

> Hello,
> You could click "Connect" button in Server management studio and click to
> select "Integration services", and then type the instance information you
> want.
> Hope this is helpful.
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> new
>|||Hello,
To understand the issue better, will you confirm the default instance is
SQL server 2005 instance? SSIS does not support named instance anyway.
Also, from the error message, it seems that client force encryption but
server does not have certificate installed. Please check the following
settings:
Run SQL Server 2005 Network Configuration, right click SQL native client
network conigurations->Properties, and configure "force protocol
encryption" to NO.
Right click the protocol under server network configuration and also
configure "force protocol encryption" to NO.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==
>X-WBNR-Posting-Host: 84.147.181.254
>From: "examnotes" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Mon, 28 Nov 2005 04:31:02 -0800
>Lines: 76
>Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks Peter, exactly what I was looking for.
>But now I still cannot see my package, as I'm getting following error
>message when clicking on MSDB under "Stored Packages" in the Object
Explorer
>tree. Any idea why?
>Client unable to establish connection
>Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> at
>Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
eInfos(String bstrPackageFolder, String bstrServerName)
> at
>Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String

>sPackageFolder, String sServerName)
>"Peter Yang [MSFT]" wrote:
>
to[vbcol=seagreen]
you[vbcol=seagreen]
rights.[vbcol=seagreen]
the[vbcol=seagreen]
tree[vbcol=seagreen]
starting[vbcol=seagreen]
>|||Both encryption settings were set to No.
Also, SQL Server 2005 is a named instance. The default instance is SQL
Server 2000.
In the "Connect to Server" dialog of management studio, I connect to
Integration Service, and as server name, the name of the machine. Does that
mean that I'm attempting to connect to the default instance? That would be
SQL 2000.
Does that mean that you cannot use SSIS on a machine where there is default
instance of SQL 2000'
"Peter Yang [MSFT]" wrote:

> Hello,
> To understand the issue better, will you confirm the default instance is
> SQL server 2005 instance? SSIS does not support named instance anyway.
> Also, from the error message, it seems that client force encryption but
> server does not have certificate installed. Please check the following
> settings:
> Run SQL Server 2005 Network Configuration, right click SQL native client
> network conigurations->Properties, and configure "force protocol
> encryption" to NO.
> Right click the protocol under server network configuration and also
> configure "force protocol encryption" to NO.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> Explorer
> eInfos(String bstrPackageFolder, String bstrServerName)
>
> to
> you
> rights.
> the
> tree
> starting
>|||Hello,
You could still store SSIS package in filesystem other than msdb under this
situation. Since SSIS will try to access msde of default instance, you
could not store the package in msdb because it is a 2000 database.
Also, you may try the following method to change the instance SSIS
connects. However, it is not officially documented and may have issues when
running package from there
1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn
2. Change the string <ServerName>.</ServerName>?as following:
<ServerName>MachineName\InstanceName</ServerName>
3. Save the file and Re-Start SSIS Service
Please let me know if you have further concerns or questions on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX02Vvn5mIYncaZQVq2jfNzlN2UsA==
>X-WBNR-Posting-Host: 84.147.129.198
>From: "examnotes" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
<36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
<yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Tue, 29 Nov 2005 03:38:03 -0800
>Lines: 159
>Message-ID: <43861D30-F658-4906-B197-CE2D9DE8D97C@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412564
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Both encryption settings were set to No.
>Also, SQL Server 2005 is a named instance. The default instance is SQL
>Server 2000.
>In the "Connect to Server" dialog of management studio, I connect to
>Integration Service, and as server name, the name of the machine. Does
that
>mean that I'm attempting to connect to the default instance? That would be
>SQL 2000.
>Does that mean that you cannot use SSIS on a machine where there is
default
>instance of SQL 2000'
>
>"Peter Yang [MSFT]" wrote:
>
rights.[vbcol=seagreen]
>Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
>Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
click[vbcol=seagreen]
so[vbcol=seagreen]
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl[vbcol=seagreen]
save[vbcol=seagreen]
Package"[vbcol=seagreen]
create a[vbcol=seagreen]
>|||This sounds like a solution. Many thanks.
"Peter Yang [MSFT]" wrote:

> Hello,
> You could still store SSIS package in filesystem other than msdb under thi
s
> situation. Since SSIS will try to access msde of default instance, you
> could not store the package in msdb because it is a 2000 database.
> Also, you may try the following method to change the instance SSIS
> connects. However, it is not officially documented and may have issues whe
n
> running package from there
> 1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
> C:\Program Files\Microsoft SQL Server\90\DTS\Binn
> 2. Change the string <ServerName>.</ServerName>?as following:
> <ServerName>MachineName\InstanceName</ServerName>
> 3. Save the file and Re-Start SSIS Service
> Please let me know if you have further concerns or questions on this.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> <yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> that
> default
> rights.
> click
> so
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> save
> Package"
> create a
>