Best Practices Question - how do you execute multiple packages?

I have 200+ plus packages that need to be flexible in how they are run. For example, an end user may choose to run packages 1,2,3 and the next end user may choose to run packages 2,3,7, etc. Prior ro running a package, I set an "instance id" inside the group of packages so I can tie them all together in the logfile - I know that packages 1,2,3 were all run as group and that's distinct from packages 2,3,7 that were run in a differnt group.

Initially I embarked on a scenario where I had a queue table that loaded up the packages to be run and then had a little c# app that read the queue, generated the "instance id" and ran all the packages (either thru dtexec.exe or the Microsoft.SqlServer.DTS.Runtime). But now I wonder if using a master package that uses the Execute Package Task is the way to go. My 200+ packages are all independent and run based on a single config file and it seems as though going the parent package route will destroy some of that independence because I'll now be relying on parent package variables.

Any comments or suggestions?

Sounds like you have a great solution that works for you. If you decided to use a parent package to execute the child packages, it would be easy enough to drive with a ForEach loop and a simple file input or even a script task. A lot about whether this is the right choice for you depends on some things you haven't told us. For example, what is the long term plan for your current solution, do you plan on enhancing the current solution etc. Also, if you use a parent package, you're not forced to use parent package configurations. You can still use the same configuration scheme you're using currently.

From the information you've given, I'd say that it sounds like a good solution.

|||

Thanks for the response, Kirk. My 200+ packages use the config file in an indirect manner. When I design my packages, I don't step thru the Configuration Wizard and create a direct configuration, I just make sure to always name my objects the same and then I just apply my global config file with the /CONFIGFILE "c:\wherever\conf.dtsConfig". However, the Execute Package Task doesn't have any properties for specifying configurations. Ideally, I'd like a master package that read a queue table and that table would have the path to a package and a path to a config file and feed that to the Execute Package Task. Also, it would be nice if the Execute Package Task had a property like /SET from dtexec.exe so you wouldn't have to have child packages "pulling" variables/data from a master package because you have to design child packages with an awareness that they are executing in a larger context. Being able to apply a change from a master package to a child package would be preferrabe.

Best practice question

I have a aspx page that needs data from sqlexpress. When I installed sqlexpress, I installed it with "mixed-mode" security. When a user visit's my page, by default the username is IUSR_MyMachine. So, I added this user to the SQLServer2005MSSSQLUSers$MyMachine$SQLEXPRESS group. I have a connection string that looks like:

"Data Source=MyServer\sqlexpress;Initial Catalog=MyDB;Integrated Security=True;Pooling=True"

At this point I can go on my merry way and log in and do my thing. My question is this, is this the right way to do this? Should I be adding IUSR to the sql group? Should I set up a sql user instead? What's the best practice for connecting to a db from an aspx page?

Any insight would be great.
Thanks ... Ed

This is a repost. I never got a response the first time.

I'm pretty sure that you made a major error just then. By adding the IUSR account to that group, you gave anonymous visitors full database admin rights. I *think* you want to set up a limited account, and grant it specific rights on specific databases or tables w/in databases. I'm poking around myself to figure out how to do it right (which brought me here), but I think your solution is like making everybody domain admin. Sure, everything works that way :), but some things you don't want to "work" for some users.|||In general the most secure way to configure this is to create a new local user called SQLReader. Then configure this local user inside SQL Server to only allow login to your specfic database, granting the user read-only access. This keeps the access very restricted. Then map the aspx page to the SQLReader account.

Of course I am leaving out all the gory details of mapping a specific aspx page to a specific user, this can be bloody difficult. One way to do it is to create a .NET component hosted in COM+ and set the COM+ package to run as a specific user (having the aspx page make calls to the component). You can also twiggle around with the web.config file in aspx to do this mapping.

However, since you already have all your desired users mapped to IUSR_MyMachine by default, then you can just skip the SQLReader part, just add IUSR_MyMachine as login to only the MyDB database and only grant read access and you are set. This is assuming you are ok for any user that hits your web site to read from the SQL database.|||Thanks for the response. You've given me a lot to think about. I appreciate the thoughtful comments.
Later ... Ed

Best Practice for MSDE User permissions

Hi All
I am new to MSDE/SQL Server and need some guidance on best practices for
user permissions.
I have a VB6 program running in a bakery factory
The computer network is a peer to peer 3 computer network running WIndowsXP
MSDE runs on computer A and the data entry person runs my program on this
machine to enter daily orders for their customers
A manager needs to access the MSDE data from another computer for reporting
tasks and is not allowed to enter or modify data
I am sure I can't use Windows authentication as it is only a peer network.
Is this correct?
Should I create a new Login and set individual permissions on each table or
is it OK to use the sa account etc?
Any ideas appreciated
Regards
Steve> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
Windows authentication is problematic when you have multiple computers
without a domain. It is possible by mapping a drive on the client to the
SQL Server using a local server account but this is a kluge.

> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
I suggest you use SQL authentication and assign permissions to roles. You
can prompt for the user's SQL login and password during application at
startup. Never use the 'sa' login for routine application access.
USE MyDatabase
--setup role-based security
EXEC sp_addrole 'Manager'
EXEC sp_addrole 'Clerk'
GRANT SELECT ON MyTable TO Manager
GRANT SELECT, INSERT, UPDATE, DELETE ON MyOtherTable TO Manager
GRANT SELECT ON MyOtherTable TO Clerk
--create login for managers
EXEC sp_addlogin 'SomeManager', 'SomeManagerPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeManager'
EXEC sp_addrolemember 'Manager', 'SomeManager'
--create login for clerks
EXEC sp_addlogin 'SomeClerk', 'SomeClerkPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeClerk'
EXEC sp_addrolemember 'Clerk', 'SomeClerk'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6DE28FBB-20B4-4637-BC82-7BDDD9FDA62B@.microsoft.com...
> Hi All
> I am new to MSDE/SQL Server and need some guidance on best practices for
> user permissions.
> I have a VB6 program running in a bakery factory
> The computer network is a peer to peer 3 computer network running
> WIndowsXP
> MSDE runs on computer A and the data entry person runs my program on this
> machine to enter daily orders for their customers
> A manager needs to access the MSDE data from another computer for
> reporting
> tasks and is not allowed to enter or modify data
> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
> Any ideas appreciated
> --
> Regards
> Steve

best practice for lookup

say i have a customer.aspx that allows a user to enter in customer data.

on customer.aspx, i have dropdownSalesRep which allows the user to associate a sales rep with the customer

but some customers come to directly, and not thru a sales rep, so I want the user to be able to specify "none"

Is it best to have a dummy record in my SalesReps table called "none" with an ID of say "999", or is there some other better way to deal with this?

I think this is more of a business decision. When you have to do reporting later on to track the sales, would you want to see "none" under sales rep?

|||

well, probably not, so assuming you don't - then is there even another way dealing with it assuming that in the underlying db, a customer must have an associated sales rep? in that reporting scenario, you'd have to write sql to filter out the "dummies" i guess.

|||

NuJoizey:

...assuming that in the underlying db, a customer must have an associated sales rep?

If you have to code according to that assumption, then you really have no choice than to use some sort of default value in your application with an Id of "999" or whichever you are comfortable with. Jjust make sure it doesnt get repeated. You can also use a negative value like -1 so there is no conflict with any number generated by SQL Server if your table grows.

|||

Another way to handle this is to store NULL in the sales rep ID. This is, in a sense, the more elegant solution since NULL means not known and you can do this and still maintain the foreign key relationship with your sales rep master table

However, many people don't like using NULL values because of their unintuitive behavior in select statements (eg, "select * from customer where SalesRepId <> 3" will NOT return rows where SalesRepId is NULL because NULL= unknown and if something is unknown it justmight be 3).

My advice is to follow ndinakar's suggestion and use a special value like -1

|||

great - thank you for the discussions

Best practice for dbo

When setting up databases for end users, what's the best practice regarding who's the dbo for each individual database - the user itself or a sysadmin?
Does it really have any importance at all who the owner (as defined by 'dbo') is ?I'd strongly recommend leaving sa as dbo, and if need be then making the user a member of the db_owner role if you need that.

-PatP|||Thanks.

The issue was raised when I noticed that for older user databases, someone had assigned a system admin as the dbo by his own, personal user name. When than person then left, and his user was removed, those user databases became orphans.|||You can assign db_ddladmin.

db_ddladmin act same as dbo but it has limited rights comparing db_owner.|||I suspect that Coolberg's problem wasn't one of permission level (they want the user to be equivalent to dbo), but one of ownership (they don't want the login to "own" the database).

There are two issues here that are tightly intertwined, and often confused.

A login is what gives a person access to SQL Server. Logins exist at the server level, and can be either SQL Authenticated or Windows Authenticated. Logins are what "own" a database.

A User is what gives a person permissions inside a SQL Server database. Users exist only inside a database, and are logically tied to exactly one login on the server.

I think that Coolberg wants to keep the ownership of the database limited to an administrative login. I strongly recommend using sa (because you just about can't delete that login), but I agree with the general idea regardless of what login you use.

By using this strategy, you can keep the database ownership limited to an administrative login, but still make any database users memebers of the db_owner role (giving them exactly the same permissions as dbo).

-PatP|||Thanks.
Yes, I'll go for the sa user.
My main goal is to avoid getting orphanized databases when users are leaving in the future.

Best Practice

Can someone tell me what the best practice for managing a sql environment
is? Is it best to have a second user account besides your everyday user
account that has elevated permissions required to manage sql?
Hi
[url]http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1 [/url]
--administaiting best practices
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
"Bad Beagle" <maxwelli@.nospam.postalias> wrote in message
news:OnorAbVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Can someone tell me what the best practice for managing a sql environment
> is? Is it best to have a second user account besides your everyday user
> account that has elevated permissions required to manage sql?
>

Best Practice

Can someone tell me what the best practice for managing a sql environment
is? Is it best to have a second user account besides your everyday user
account that has elevated permissions required to manage sql?Hi
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting best practices
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
"Bad Beagle" <maxwelli@.nospam.postalias> wrote in message
news:OnorAbVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Can someone tell me what the best practice for managing a sql environment
> is? Is it best to have a second user account besides your everyday user
> account that has elevated permissions required to manage sql?
>

Best Practice

Can someone tell me what the best practice for managing a sql environment
is? Is it best to have a second user account besides your everyday user
account that has elevated permissions required to manage sql?Hi
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
"Bad Beagle" <maxwelli@.nospam.postalias> wrote in message
news:OnorAbVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Can someone tell me what the best practice for managing a sql environment
> is? Is it best to have a second user account besides your everyday user
> account that has elevated permissions required to manage sql?
>

Best method of Restoring a SQL 2005 Desktop Edition

For a SQL 2005 Desktop edition that has a user database and some user
accounts and linked servers. What is the best method of backing this
up and restoring to a different SQL Server?Robin9876 wrote:
> For a SQL 2005 Desktop edition that has a user database and some user
> accounts and linked servers. What is the best method of backing this
> up and restoring to a different SQL Server?
See: http://vyaskn.tripod.com/moving_sql_server.htm
--
Razvan Socol
SQL Server MVP

Best method of checking for duplicate entries in SQL Server

Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional.

In my Favorites table, the 3 fields are....

favoriteId (set as primary key)
userId
carId

I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives.

1) Check proactively by doing a....
SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)
If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table.

2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique.

Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice?

TIA for your help.

Your best bet IMO would be a derivative of option #1.

If NOT Exists(select favoriteID from favorites where userid = @.x and carid = @.y)
BEGIN
--insert record here
return 0
END
ELSE
BEGIN
return 1
END

where a return of 1 means the record exists

|||

Thanks for the reply, Diamsorn. Is that something called a stored procedure? I'm not terribly familiar with them.... but if that's the only way to go, I'll try to research them. Is there a way to translate that into an inline sql query in my VB code? I've been using the SqlCommand object with an SqlDataReader to get at my queries. Is your method noticeably more efficient that mine? Here's my snippet of code where I proactively check for an existing entry, then I do one of two actions depending on whether or not the record exists. Thanks for helping out an ignorant.

Dim favoriteIdAsInteger
Dim favoriteLookupCmdAsNew SqlCommand _
("SELECT favoriteId FROM Favorites WHERE userId = " & userId &" AND carId = " & carId, dbConnection)
thisReader = favoriteLookupCmd.ExecuteReader()
While (thisReader.Read())
favoriteId = thisReader.GetValue(0).ToString
EndWhile
thisReader.Close()

If (String.IsNullOrEmpty(favoriteId) =False)Then
addFavStatusLabel.Text ="This car already exists in your favorites list."
Else
Dim rowsAffectedAsInteger
Dim insertFavoriteCmdAs SqlCommand =New SqlCommand("INSERT INTO Favorites (userId, carId) VALUES (" & userId &", " & carId &")", dbConnection)
rowsAffected = insertFavoriteCmd.ExecuteNonQuery()
If rowsAffected <> 1Then
addFavStatusLabel.Text ="Error in adding car."
Else
addFavStatusLabel.Text ="Car added to favorites successfully."
EndIf
EndIf

Thanks.

|||

Its not a stored procedure, but rather the T-SQL that will do the same as what you are doing above inside of a stored procedure.

Either method is fine, doing it in a single stored procedure reduces the number of trips back to the server your app has to make,

Also you reduce the chance for SQL injection attacks.
If your going to go with the method you have above, then your going to want to use parameters to also reduce the chance for SQL injection attacks.

 
Dim favoriteLookupCmdAs New SqlCommand _ ("SELECT favoriteId FROM Favorites WHERE userId = @.useriD AND carId = @.carID", dbConnection)favoriteLookupCmd.Parameters.AddWithValue("@.useriD", userID)favoriteLookupCmd.Parameters.AddWithValue("@.carID", carID)
 

and do the same for your 2nd sql statement as well.|||

INSERT INTO Favorates(UserID,CarID) SELECT @.UserID,@.CarID WHERE NOT EXISTS(SELECT * FROM Favorates WHEREuserid=@.UserID ANDcarId=@.CarID)

because the check and insert are wrapped into a single SQL Statement, locks are automatically placed on any records it matches in the WHERE clause until the INSERT has completed. This guarantees that you will never insert records into the favorates table that already has the record. You could also do this:

IF NOT EXISTS(...) INSERT ...

but the locks (read/shared) on the records in the exists clause are released prior to the insert, which leaves an opportunity for a record to be inserted between them.

Dim rowsAffectedAsInteger
Dim insertFavoriteCmdAs SqlCommand =New SqlCommand("INSERT INTO Favorites (userId, carId) SELECT @.userid,@.caridWHERE NOT EXISTS(SELECT * FROM Favorates WHEREuserid=@.UserID ANDcarId=@.CarID)", dbConnection)

with insertFavoriteCmd

.Parameters.Add("@.userid",sqldbtype.nvarchar).value=userid

.Parameters.Add("@.carid",sqldbtype.integer).Value=carid

end with
rowsAffected = insertFavoriteCmd.ExecuteNonQuery()
If rowsAffected <> 1Then
addFavStatusLabel.Text ="This car already exists in your favorites list."
Else
addFavStatusLabel.Text ="Car added to favorites successfully."
EndIf

|||

How should i write update query for the same ?

plz help me out of this guys....

Best Enterprise Datawarehouse

We currently run a MASSIVE enterprise data warehouse on SQL Server 2000 and it is struggling a bit with both user queries and daily loads.
Need opinions on
1. Is SQL Server the best product for the job
2. What are the alternatives
CheersWhat is massive ? Also, what is your hardware specs ?|||And what are you doing with it? Are you creating cubes off of it?|||Have about 7 + Terabytes of data - growing at 100 gb a month

Team of dedicated App Dev staff designing and developing OLAP cubes and publishing on the intranet using third party software|||What type of cubes ? What version of sql are you using ?|||What window do you have available to perform the daily loads? Approx how long does each load take?|||At that size I am sure MS would love to help you (and use you in their marketing :) )|||You can easily deploy SQL Analaysis Services in this case, check ver 2000 for more enhancements and information on MS SQL homepage.

HTH|||No one seems to have opinions on any other products other than SQL Server? I guess it is a SQL Server forum but like I have already said - this is our current platform and is Struggling !!!!|||Originally posted by aldo_2003
Have about 7 + Terabytes of data - growing at 100 gb a month

Team of dedicated App Dev staff designing and developing OLAP cubes and publishing on the intranet using third party software

Gotta ask...what's the subject of the data?

3rd party software?

I don't think (damn it, again) that ANY 3rd party software plans for anything that massive...

And EVERYONE is writting adhoc queries...right?

"This sounds like a job for "Super-Silver bullet""

And yes, MS would love to talk to you....|||Originally posted by aldo_2003
No one seems to have opinions on any other products other than SQL Server? I guess it is a SQL Server forum but like I have already said - this is our current platform and is Struggling !!!!

Be happy using Sybase IQ (http://www.sybase.com/products/bi/sybaseiq) !
=> T-SQL compliance
=> DSS database
=> performance++|||You have given very few information regarding your environment and the reasons you are struggling, other blaming SQL. We would be glad to help you if you could tell us more about your data warehouse. So we can see where your bottleneck is. But if you are looking for the answer of 'Yes, you need to switch to O.... and S.. platform', please go to O.... forum. Thanks.

best datatype to save password

Hi,

what is the best datatype to save user's passwordin an encrypted format? Is there any ready datatype for that or i have to send the password enrypted to the database?

Thanks..

If you send it in clear text to the server and then checks for it anyone that has access to the connection between your application and server would be able to have a look at the password. The best practive would be to use a recognized one way hashing algorithm and send only that over the connection.

This way it will be up to your application to hash and match password and snooping on the line will be less interesting for hackers.

|||

dose this mean SQL Server dosen't have a ready encrypted datatype?

if yes, what would be the best way to encrypt if i am using C#?

thanks.

|||

SHA256 would probably be stronger than most expect.
http://msdn2.microsoft.com/en-us/library/system.security.cryptography.sha256.aspx

Store it as varbinary(32)

|||

An SHA256 hash (or any hash, for that matter) of the password alone is completely insecure unless strong passwords or pass phrases are used. Although SHA256 is technically a "one-way" transformation, in reality, it is easy to decode if the plain text is just a word. All that's necessary is to search for the hashed password in a dictionary of the SHA256 hashes of the million most common words. SHA256 is a reasonable choice as a message digest to signal unauthorized changes to the message, but it is not intended or useful for encoding single words. Steve Kass Drew University Andreas Johansson@.discussions.microsoft.com wrote:
> SHA256 would probably be stronger than most expect.
> http://msdn2.microsoft.com/en-us/library/system.security.cryptography.sh
> a256.aspx
>
> Store it as varbinary(32)
>
>

|||

Can nothing but agree, it is important to use strong passwords.

http://en.wikipedia.org/wiki/Password_strength

Beginning SSIS - Sharepoint and Excel

Hi all, I have never used SSIS before, but am looking to use it in this aspect:

1) A user uploads an Excel file into Sharepoint. This will be through a Document Library in a Sharepoint page.

2) When this file is uploaded, I'd like SSIS to notice there is a new file and process it - it will pull information from the Excel file, put it into a database, and - if this is possible - delete the new file.

3) This is iffy. Can SSIS then generate an InfoPath document from the information stored in the database? If not, I can just have a InfoPath query form.

I'd just like to know if this is possible. If you have any helpful links, please let me know - I would greatly appreciate it!

Thanks,

James

James,

2) SSIS can do all of this. You can use the FileWatcher task as provided on SQLIS.com. Deleting the file is easy.

3) No idea. If there is an API for InfoPath then it should be possible.

-Jamie

|||Thanks for the reply, Jamie. I'll take a look at that.|||

Alright, well I've taken a look and I'm having some issues -

In Control Flow, I have the file watcher watching a directory for any new Excel files and the output of the filewatcher is User::NewExcelFile, which I understand will be a string with the information of the type "file.xls".

I also have an Excel connection manager which points to the format of the Excel file that will be used. Then I have a SQL server destination that is mapped to the database the information will be put to. How does the File Watcher control flow task pass the information of the filename to the Excel/SQL data flow tasks? They don't allow variable inputs.

Thanks,

James

|||

The Excel source component uses an Excel Connection Manager which has a property called ExcelFilePath that contains the name and location of the file.

You can set this property using a property expression. (Select the connection manager, press F4, expand 'Expressions' in the properties pane). Set it to the value returned by the File Watcher.

-Jamie

|||

Thanks Jamie, that works - I assume as File watcher notices a new file, it triggers the following control/data flow.

Is there a way to set the Excel file's name to be something dynamic? For example, to watch for an Excel file of any name, not just the name of the Excel file specified in the Excel connection manager? File watcher appears to only watch a path, which it can pass to the connection manager when triggered by a new file upload, but the Excel Connection Manager needs a static Excel name.

And what is the task for deleting the Excel upon completion of the service?

Thanks for your time! I am new to SSIS and coming to grips with it :)

-James

EDIT:

Sorry, let me rephrase:

I set the File Watcher Output Variable Name to "User::NewExcelFile" - I assume that this contains either the path to the containing folder OR the containing folder + the name of the file.

If it is the path only, then how can I set the Excel Connection manager to get the name of the file as well?

And if it is the path and name, how do I parse the @.User::NewExcelFile to put the file path in ExcelFilePath and the name in Name?

Also, when the ExcelFilePath is set to @.User::NewExcelFile, I cannot set and save the file path in the Excel Connection Manager. It clears the field - this does not let me map the Excel field to the Sql destination.

|||

James,

The output variable will contain the path and the name of the file.

You need to put the whole thing (the path and the name) into ExcelFilePath property. So there's no need to parse it. The Name property is just the name of the connection manager. Its irrelevant.

Regarding the last point, the reason the field is cleared is because there is nothing in @.User::NewExcelFile. Put a path in that variable to an excel file that has the same metadata as the file you will be processing at runtime. This value is only used at design-time as it will be overwritten at runtime by the FileWatcher Task.

-Jamie

|||

I used file system watcher to read excel on my pc it worked fine, but when I tried to read the excel from SharePoint it did't work. The FileWatcher box showing the yellow color for long time that I had to stop the ssis.

So my question what is the cause of this. Do i need to set something or am i missing something? Please help.

Beginning SSIS - Sharepoint and Excel

Hi all, I have never used SSIS before, but am looking to use it in this aspect:

1) A user uploads an Excel file into Sharepoint. This will be through a Document Library in a Sharepoint page.

2) When this file is uploaded, I'd like SSIS to notice there is a new file and process it - it will pull information from the Excel file, put it into a database, and - if this is possible - delete the new file.

3) This is iffy. Can SSIS then generate an InfoPath document from the information stored in the database? If not, I can just have a InfoPath query form.

I'd just like to know if this is possible. If you have any helpful links, please let me know - I would greatly appreciate it!

Thanks,

James

James,

2) SSIS can do all of this. You can use the FileWatcher task as provided on SQLIS.com. Deleting the file is easy.

3) No idea. If there is an API for InfoPath then it should be possible.

-Jamie

|||Thanks for the reply, Jamie. I'll take a look at that.|||

Alright, well I've taken a look and I'm having some issues -

In Control Flow, I have the file watcher watching a directory for any new Excel files and the output of the filewatcher is User::NewExcelFile, which I understand will be a string with the information of the type "file.xls".

I also have an Excel connection manager which points to the format of the Excel file that will be used. Then I have a SQL server destination that is mapped to the database the information will be put to. How does the File Watcher control flow task pass the information of the filename to the Excel/SQL data flow tasks? They don't allow variable inputs.

Thanks,

James

|||

The Excel source component uses an Excel Connection Manager which has a property called ExcelFilePath that contains the name and location of the file.

You can set this property using a property expression. (Select the connection manager, press F4, expand 'Expressions' in the properties pane). Set it to the value returned by the File Watcher.

-Jamie

|||

Thanks Jamie, that works - I assume as File watcher notices a new file, it triggers the following control/data flow.

Is there a way to set the Excel file's name to be something dynamic? For example, to watch for an Excel file of any name, not just the name of the Excel file specified in the Excel connection manager? File watcher appears to only watch a path, which it can pass to the connection manager when triggered by a new file upload, but the Excel Connection Manager needs a static Excel name.

And what is the task for deleting the Excel upon completion of the service?

Thanks for your time! I am new to SSIS and coming to grips with it :)

-James

EDIT:

Sorry, let me rephrase:

I set the File Watcher Output Variable Name to "User::NewExcelFile" - I assume that this contains either the path to the containing folder OR the containing folder + the name of the file.

If it is the path only, then how can I set the Excel Connection manager to get the name of the file as well?

And if it is the path and name, how do I parse the @.User::NewExcelFile to put the file path in ExcelFilePath and the name in Name?

Also, when the ExcelFilePath is set to @.User::NewExcelFile, I cannot set and save the file path in the Excel Connection Manager. It clears the field - this does not let me map the Excel field to the Sql destination.

|||

James,

The output variable will contain the path and the name of the file.

You need to put the whole thing (the path and the name) into ExcelFilePath property. So there's no need to parse it. The Name property is just the name of the connection manager. Its irrelevant.

Regarding the last point, the reason the field is cleared is because there is nothing in @.User::NewExcelFile. Put a path in that variable to an excel file that has the same metadata as the file you will be processing at runtime. This value is only used at design-time as it will be overwritten at runtime by the FileWatcher Task.

-Jamie

|||

I used file system watcher to read excel on my pc it worked fine, but when I tried to read the excel from SharePoint it did't work. The FileWatcher box showing the yellow color for long time that I had to stop the ssis.

So my question what is the cause of this. Do i need to set something or am i missing something? Please help.

Beginners questions about Reporting Services Admin

I'm looking at the Properties tab of a report In the Report Manager. If I go
into 'New Role Assignment', I see a 'Group or user name:' textbox.
Do I understand this correctly?: Any name I enter for a new role must match
an existing SQL Server user or group? (If so I would expect some sort of
lookup/selection list, which is the main cause for my confusion.)No, this has nothing to do with SQL Server. RS is an asp.net application and
it uses roles to manage who gets to run a report, create subscriptions, etc.
Assuming that you are using integrated security (the default) then what you
are doing is assigning a user or group to a particular role. If you are in
the local administrators group for the server (not SQL Server, but the
server RS is running on) then you are automatically part of the Content
Manager role.
When you create a datasource then you deal with the credentials for
retrieving the data for the report.
So, two different things which is good. Remember, you can connect to many
different sources for the data for the reports and they can all be using
different credentials for retrieving that data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
news:18CD1BB0-50CD-422B-8A8B-2770B1B7CBFB@.microsoft.com...
> I'm looking at the Properties tab of a report In the Report Manager. If I
> go
> into 'New Role Assignment', I see a 'Group or user name:' textbox.
> Do I understand this correctly?: Any name I enter for a new role must
> match
> an existing SQL Server user or group? (If so I would expect some sort of
> lookup/selection list, which is the main cause for my confusion.)|||Ok, admittedly I am not a security expert. I should have said Windows, not
SQL Server. Let me rephrase the question.
Are you saying in the New Role Assignment window, the name I enter in the
'Group or user name' box must match exactly an existing Windows group or
user?
(And again I say that I am rather confused that it is a textbox rather than
a selection list.)
"Bruce L-C [MVP]" wrote:
> No, this has nothing to do with SQL Server. RS is an asp.net application and
> it uses roles to manage who gets to run a report, create subscriptions, etc.
> Assuming that you are using integrated security (the default) then what you
> are doing is assigning a user or group to a particular role. If you are in
> the local administrators group for the server (not SQL Server, but the
> server RS is running on) then you are automatically part of the Content
> Manager role.
> When you create a datasource then you deal with the credentials for
> retrieving the data for the report.
> So, two different things which is good. Remember, you can connect to many
> different sources for the data for the reports and they can all be using
> different credentials for retrieving that data.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
> news:18CD1BB0-50CD-422B-8A8B-2770B1B7CBFB@.microsoft.com...
> > I'm looking at the Properties tab of a report In the Report Manager. If I
> > go
> > into 'New Role Assignment', I see a 'Group or user name:' textbox.
> >
> > Do I understand this correctly?: Any name I enter for a new role must
> > match
> > an existing SQL Server user or group? (If so I would expect some sort of
> > lookup/selection list, which is the main cause for my confusion.)
>
>|||Yes, you are mapping a Windows user/group to a RS role. What I do is I
create a local group on the box specifically for this. I add individual
users and domain groups to that local group. I then assign that group to a
role.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
news:6C2052BC-4ACE-4D8F-84A5-A9316D86F0B7@.microsoft.com...
> Ok, admittedly I am not a security expert. I should have said Windows,
> not
> SQL Server. Let me rephrase the question.
> Are you saying in the New Role Assignment window, the name I enter in the
> 'Group or user name' box must match exactly an existing Windows group or
> user?
> (And again I say that I am rather confused that it is a textbox rather
> than
> a selection list.)
> "Bruce L-C [MVP]" wrote:
>> No, this has nothing to do with SQL Server. RS is an asp.net application
>> and
>> it uses roles to manage who gets to run a report, create subscriptions,
>> etc.
>> Assuming that you are using integrated security (the default) then what
>> you
>> are doing is assigning a user or group to a particular role. If you are
>> in
>> the local administrators group for the server (not SQL Server, but the
>> server RS is running on) then you are automatically part of the Content
>> Manager role.
>> When you create a datasource then you deal with the credentials for
>> retrieving the data for the report.
>> So, two different things which is good. Remember, you can connect to many
>> different sources for the data for the reports and they can all be using
>> different credentials for retrieving that data.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
>> news:18CD1BB0-50CD-422B-8A8B-2770B1B7CBFB@.microsoft.com...
>> > I'm looking at the Properties tab of a report In the Report Manager.
>> > If I
>> > go
>> > into 'New Role Assignment', I see a 'Group or user name:' textbox.
>> >
>> > Do I understand this correctly?: Any name I enter for a new role must
>> > match
>> > an existing SQL Server user or group? (If so I would expect some sort
>> > of
>> > lookup/selection list, which is the main cause for my confusion.)
>>|||I'm still having problems. I was under the impression that access was
controlled solely by RS itself. I know you suggested created a dedicated
group. However, I started small, with a single network ID that had
absolutely no presence on this particular machine.
I created a server role consisting of all the 'View' options. I started by
assigning the test id this 'read-only' role at the site level and then worked
down to the report. (It appears that I have to assign security at all the
levels, site-folder-report, before the user can see the actual report. Is
that right?)
However, once the Test ID finally had access to the report and started it, I
got the error message: An error has occured during report processing.
Cannot create a connection to data source 'DataSource11'. For more
information about this error navigate to the report server on the local
server machine, or enable remote errors.'
I really can't find anything in the error logs that seems relevant and I'm
not sure I understand some of the other references to this problem on this
group. Any suggestions?
"Bruce L-C [MVP]" wrote:
> Yes, you are mapping a Windows user/group to a RS role. What I do is I
> create a local group on the box specifically for this. I add individual
> users and domain groups to that local group. I then assign that group to a
> role.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
> news:6C2052BC-4ACE-4D8F-84A5-A9316D86F0B7@.microsoft.com...
> > Ok, admittedly I am not a security expert. I should have said Windows,
> > not
> > SQL Server. Let me rephrase the question.
> >
> > Are you saying in the New Role Assignment window, the name I enter in the
> > 'Group or user name' box must match exactly an existing Windows group or
> > user?
> >
> > (And again I say that I am rather confused that it is a textbox rather
> > than
> > a selection list.)
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No, this has nothing to do with SQL Server. RS is an asp.net application
> >> and
> >> it uses roles to manage who gets to run a report, create subscriptions,
> >> etc.
> >> Assuming that you are using integrated security (the default) then what
> >> you
> >> are doing is assigning a user or group to a particular role. If you are
> >> in
> >> the local administrators group for the server (not SQL Server, but the
> >> server RS is running on) then you are automatically part of the Content
> >> Manager role.
> >>
> >> When you create a datasource then you deal with the credentials for
> >> retrieving the data for the report.
> >>
> >> So, two different things which is good. Remember, you can connect to many
> >> different sources for the data for the reports and they can all be using
> >> different credentials for retrieving that data.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
> >> news:18CD1BB0-50CD-422B-8A8B-2770B1B7CBFB@.microsoft.com...
> >> > I'm looking at the Properties tab of a report In the Report Manager.
> >> > If I
> >> > go
> >> > into 'New Role Assignment', I see a 'Group or user name:' textbox.
> >> >
> >> > Do I understand this correctly?: Any name I enter for a new role must
> >> > match
> >> > an existing SQL Server user or group? (If so I would expect some sort
> >> > of
> >> > lookup/selection list, which is the main cause for my confusion.)
> >>
> >>
> >>
>
>|||I am experiencing the same issue as B.Chernick.
Just as B.Chernick explains, I have also created a Test user that
access the report, but gets errors during processing.
However, I noticed that if I gave my Test user domain adminstrator
privileges (which is not possible in the production environment) I am
able to view the report without any difficulty. This leads me to
believe that there is a permissions issue someplace along the line.
Any ideas?

beginner: "Login failed for user sa."

Hello,
(sorry for my English...)
Could you help me with a SQL Server 2005 problem?
I had installed SQL Server 2005 and then I tried to setup some application
using SQL Server. Unfortunatelly setup fails because the application cannot
logon to SQL Server in SQL Server Authentication mode (user 'sa', password
'sa'). I checked Server Management Studio Express: I have sa/sa account
(because I've prepared it), but although I can logon in Windows
Authentication mode, I cannot logon in SQL Server Authentication (sa/sa)
because of:

Login failed for user 'sa'. The user is not associated with a trusted SQL
Server connection (Microsoft SQL Server, Error: 18452).

Could you help me plase? I suspect that solution is simple but my experience
is not enough.
Thank you very much.
/RAMtake a look here
http://sqlservercode.blogspot.com/2...reason-not.html|||Andrzej Magdziarz (andrzej.magdziarz@.wp.pl) writes:
> Could you help me with a SQL Server 2005 problem? I had installed SQL
> Server 2005 and then I tried to setup some application using SQL Server.
> Unfortunatelly setup fails because the application cannot logon to SQL
> Server in SQL Server Authentication mode (user 'sa', password 'sa').

That is not a very good password. :-)

> I checked Server Management Studio Express: I have sa/sa account
> (because I've prepared it), but although I can logon in Windows
> Authentication mode, I cannot logon in SQL Server Authentication (sa/sa)
> because of:
> Login failed for user 'sa'. The user is not associated with a trusted SQL
> Server connection (Microsoft SQL Server, Error: 18452).
> Could you help me plase? I suspect that solution is simple but my
> experience is not enough.

By default, SQL Server accepts only logins through Windows authentication,
and you must explicitly permit SQL authentication. Your first chance
to so is during setup, but you can also do this from Mgmt Studio.
Right-click the server itself in the Object Explorer, select Properies
and then find the Security page.

--
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

Beginner Help Wanted With PL/SQL

Hi I was wondering if anybody could help me with this problem:

Step 1: Create a table to store text strings entered by a database user. When a text string is entered to the database, various information about the entry should be recorded including:
A unique identifier for the entry that will be the primary key. You must set the primary key using a constraint
The actual text string entered by the user
The name of the database user that entered the text string
The date that the string was entered

Step 2: Create a sequence that starts off with an initial value of 100 and increments by 10

Step 3: Declare a PL/SQL block that:
Declares four appropriately named variables using anchored datatypes that match each column in the table defined in Step 1
Prompts the user to enter a text string
Assign the value entered by the user to the variable defined to store the string entered
Assign the date, user and next value in the sequence to the other variable defined previously. Note that you should use the SELECT INTO FROM DUAL method of assignment
Within the PL/SQL Block, insert the information assigned to the variables into the table defined in Step 1Which specific part are you having trouble with? Clearly you are not asking us to do all your homework for you!|||Homework? Yeah hold on I have to go ask my mammy if I can use the computer!|||Originally posted by iknownothing
Homework? Yeah hold on I have to go ask my mammy if I can use the computer!
Don't forget to say "please"!|||Jaysus you're hilarious!!|||Originally posted by iknownothing
Jaysus you're hilarious!!
You are too kind. But seriously, is there any specific help you want with your "homework" ("class assignment", call it what you will), or did you just want someone to do it all for you? You will find that people round here are very helpful if you are prepared to put in some of the effort yourself. On the other hand, people are less inclined to help when it appears that someone just wants to pass off someone else's effort as their own.

So: what have you come up with so far, and where are you stuck?|||Well, you are already using a cursor-based record! :-

student_val c_student%ROWTYPE;

i.e. the record type is defined in terms of the cursor c_student.

The table-based cursor would be student%ROWTYPE.

If you use a cursor FOR loop you can get rid of the declaration altogether, along with a lot of other code:

SET SERVEROUTPUT ON;

DECLARE

CURSOR c_student IS
SELECT * FROM student;

begin

open c_student;

for student_val in c_student loop

DBMS_OUTPUT.PUT_LINE('Student Details: ' || student_val.salutation || student_val.first_name
|| student_val.last_name || student_val.phone || student_val.Registration_date );

end loop;

end;|||Yeah I figured it out later and deleted the post coz it was pointless but what I need to know now is how to change it from a cursor to a table based! Im in the process of trying but am getting nowhere!!

Before Update/Delete Trigger

Is there a way to create a trigger that will keep a user from updating or deleting a record? Thanks, JeremyRead about INSTEAD OF triggers in BOL|||Hi JCScoobyRS,

Originally posted by JCScoobyRS
Is there a way to create a trigger that will keep a user from updating or deleting a record? Thanks, Jeremy

h, why don't you revoke the user the UPDATE and DELETE permission?|||Very good idea BUT I'm trying to help a buddy out that needed the ability described in my first post. Is there a way? I'll check with him to see if that will work but I wouldn't mind an answer anyways. Thanks for your help, Jeremy|||Originally posted by JCScoobyRS
Very good idea BUT I'm trying to help a buddy out that needed the ability described in my first post. Is there a way? I'll check with him to see if that will work but I wouldn't mind an answer anyways. Thanks for your help, Jeremy

In this case you should go for INSTEAD OF triggers|||Okay...that sounds good. Here is an example of what I need to do:

I'm trying to prevent the UPDATE and DELETE on a table after a certain field has been entered(not null). Here is the trigger right now:

CREATE TRIGGER TRANSACTION_REPORTEE ON [dbo].[TRAVAUX_COMMANDE]
FOR UPDATE, DELETE
AS
IF [dbo].[TRAVAUX_COMMANDE].[Id_Transaction_GL] IS NOT NULL
BEGIN
RAISERROR ('Impossible de modifier une ligne reporte',10,1)
ROLLBACK TRAN
END

This is what my buddy has. Is there anyway to take what he has here and revise it with your idea in it for testing? Thanks alot, Jeremy

before delete triggers in MSSQL

Hi
I've got several tables that have foreign key relationships with a 'users'
table - for example tasks (assigned to user) and customers (liason of
customer). When I delete a user, I would like to set all the foreignkeyed
rows to have null as a user, rather than doing a cascading delete. This
could be done in a stored procedure, but the problem is that the application
has multiple modules that can be added and removed, so I don't know at
execution time what tables there are. The only way to do this I could think
of was with triggers. I know this is supposed to be a big no no, but
couldn't think of anything else. Not that it matters, because triggers won't
work here as the trigger is fired after the delete is done and hence bombs
out due to violated constraint checking. I can't use an 'INSTEAD OF' trigger
unfortunately as I need the facility to have multiple triggers. I see that
Oracle has a BEFORE trigger, imagining that this would solve the problem. Is
there similar functionality in SQL, or another way to do this?
I was hoping that this is a common task and that there is an easy way to do
it, but no luck so far with searches
Thanks
JoeOn Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:

> The only way to do this I could think
>of was with triggers. I know this is supposed to be a big no no, but
>couldn't think of anything else.
Hi Joe,
Why do you think triggers are a big no no? Of course, they shouldn't be
your first option and you should prefer DRI over triggers where possible,
but there are situations where triggers are an invaluable instrument.

> Not that it matters, because triggers won't
>work here as the trigger is fired after the delete is done and hence bombs
>out due to violated constraint checking.
That's correct. You either have to remove the foreign key constraint and
do the checking in the trigger as well, or you have to use INSTEAD OF
triggers.

> I can't use an 'INSTEAD OF' trigger
>unfortunately as I need the facility to have multiple triggers.
Maybe I'm missing something, but why don't you just combine the actions of
those various triggers into one trigger?

> I see that
>Oracle has a BEFORE trigger, imagining that this would solve the problem. I
s
>there similar functionality in SQL,
The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
has to offer.

> or another way to do this?
As I already indicated, you could move the constraint checking to the
trigger as well. But that's a bad idea, since that would force you to
write and maintain more trigger code, it would slow things down and it
would deny the query optimizer the knowledge of this constraint, so that
it can't use this knowledge to optimize query execution.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have to agree 100% with Hugo. Use instead of triggers, or drop your
relationships and implement them in triggers (which will be just as good,
but will be pretty painful to implement consiering you can just do it in the
instead of trigger.) You can have as many actions in the trigger as you
want.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:cva111hh4p6m600c28jagd6m348r6g2jii@.
4ax.com...
> On Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
>
> Hi Joe,
> Why do you think triggers are a big no no? Of course, they shouldn't be
> your first option and you should prefer DRI over triggers where possible,
> but there are situations where triggers are an invaluable instrument.
>
> That's correct. You either have to remove the foreign key constraint and
> do the checking in the trigger as well, or you have to use INSTEAD OF
> triggers.
>
> Maybe I'm missing something, but why don't you just combine the actions of
> those various triggers into one trigger?
>
> The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
> has to offer.
>
> As I already indicated, you could move the constraint checking to the
> trigger as well. But that's a bad idea, since that would force you to
> write and maintain more trigger code, it would slow things down and it
> would deny the query optimizer the knowledge of this constraint, so that
> it can't use this knowledge to optimize query execution.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)