June 1, 2010 at 7:25 pm
Hello SQLServercentral.com
Firstly, I would like to deeply thank all the gurus in this area for writing up such a good posts in the forums and also the articles. I am very new SSIS but not to SQLServer as a DBA. I have been reading different forums and different articles in this website to travel to a reasonably long distance in my project. Please be patient in reading through my post and please answer my question.
I am trying to deploy a script (Index level frag) from one single server onto different servers in the network. I was able to make the package to go through different servers and collect the information which I need. There are few servers in which database names have space in between (no kidding). I know for sure it is nothing wrong with my package. I would like to know from the gurus if there is a way to raise an error or something like that for that single database/server and feed that particular error phrase into a log. My ultimate goal is to avoid the package to end up with failure because of these silly errors. However, i would like to know if there were any errors crept in during the runtime, so that I can go to that particular server and investigate the problem by re-running this script only on that particluar server.
Please kindly give me some ideas in this regards.
Thanks
Ali
SQL DBA
June 2, 2010 at 2:46 am
Are you working with SSIS 2005 or 2008?
I know you can do the following in 2008:
Try
' Test here for the space in the name and put all your other code here.
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
Dts.Log("Someone placed a space in this servername", 999, Nothing)
Dts.TaskResult = ScriptResults.Success
Due to the result set to success, your script will always succeed, but it will write a log entry if something went wrong.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 2, 2010 at 2:27 pm
Hello da-Zero
Thank you very much for the quick reply. my package is on sql 08. But, my package will traverse through different servers which includes sql 05. Does this matter?
Also, I am using execute SQL Task not the Script Task. Sorry, if this is a dumb question, can I copy all my script into Execute Script task and use the code snippet that you have posted?
Let me explain you some more in detail, so that you folks can give me optimized solution. Please excuse me for this long writings. This task is basically in a for each loop container to which I will feed all the server list. my execute sql task will go through each variable which is the server name and execute the script on each server. So, in my scenario, say for server x, database y whose database id is 7 has a space. when the script hits this database, I am facing this error:
Could not locate entry in sysdatabases for database 'First half part of the database name(left part from the space)'. No entry found with that name. Make sure that the name is entered correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
After this error is posted, my package stops going into other variables (servers). I have changed the options"fail package on failure -- false" and "fail parent on failure -- false" in the properties of this execute sql task.
Please suggest me.
June 3, 2010 at 6:15 am
Ali_SQLDBA (6/2/2010)
My package is on sql 08. But, my package will traverse through different servers which includes sql 05. Does this matter?
No, the version is important because the .NET code that I wrote is to be executed in a script task in a SSIS 2008 package. In SSIS 2005 the code will have to be adjusted. Since your package is in SSIS 2008, it doesn't matter.
Ali_SQLDBA (6/2/2010)
Also, I am using execute SQL Task not the Script Task. Sorry, if this is a dumb question, can I copy all my script into Execute Script task and use the code snippet that you have posted?
Unfortunately, no 🙂 The code in your Execute SQL Task is T-SQL, while the Script Task expects C#.NET or VB.NET (my example is written in VB.NET).
Ali_SQLDBA (6/2/2010)
Let me explain you some more in detail, so that you folks can give me optimized solution....
I think the following solution is the easiest:
* Keep your Execute SQL Task in the For Each Loop
* Add a variable Correct_Server_Name of type Boolean.
* Add a Script Task in the For Each Loop before your SQL Task. In this script task, use my code snippet. Just check if the server name is valid. If not, log an item and set the variable Correct_Server_Name to false. If it is correct, do not log anything and set the variable to true.
* Finally, add a precedence constraint between the Script Task and the SQL Task. Double click on the arrow to get into the editing screen. There set the precedence constraint to "Success and Expression". Use the following expression:
@Correct_Server_Name == True
This set-up will do the following:
for each server, the script task will check its name. If it is correct, your SQL code will be executed. If it is not correct, logging takes place and thanks to the precedence constraint the SQL code is NOT executed.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 3, 2010 at 1:33 pm
Perhaps I am not understanding but can't you just put your db name in brackets?
[DB name with spaces] and have your code work?
June 3, 2010 at 1:44 pm
My DB Name is picked by the cursor dynamically. It is not just one database or one instance.. I am running this script using ssis on all sql servers in my network.
This is what I have done yesterday. I have used ssis logging to log the error in a txt file and set the on failure fail the package option to false. it is actually working now. I am able to traverse through all the test servers in my network.
Even though, my package is running fine now, in my last test, sql server has logged almost 20 databases from different servers into this .txt file. Now, my problem is I don't know which database belongs to which server.
Please suggest me the correct path. I feel like I am there but not yet...
June 4, 2010 at 1:39 am
Can't you put the DB Name in the string that you log?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 4, 2010 at 3:14 am
I am using SSIS -> Logging from the menu bar in bids to do the logging. There is no option for db name or server name in those enlisted feilds. But, with that being said, there is an option called load when we traverse into this particular logging window. May be if I can create some XML file to add extra columns to the exsisting will do my job.
Evey thing else in the package looks great except this small silly part. Please suggest me.
June 4, 2010 at 3:18 am
Well, if you use the Script Task that I gave as an example, you can write your own custom log entries.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply