SQL Overview SSIS Package III - Full Package

Question

Post reply

SQL Overview SSIS Package III - Full Package

Viewing 15 posts - 16 through 30 (of 64 total)

You must be logged in to reply to this topic. Login to reply

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 1

Try using

SELECT name

FROM master.dbo.sysdatabases

WHERE status&512 = 0

and status&32 = 0

Any databases that are offline or being loaded will be skipped.

For a list of all the status values take a look at this post by

Lowell

David Bird

ppreston Mr or Mrs. 500 Points: 588 More actions · Answer 2

Hello, this SSIS package is something me and my coworkers are really looking forward to implementing on our server.

I seem to be having a problem with the "Collect Server Info" step. It's only reading / loading information from one server, instead of 2 or 3. I have updated the "SSIS_ServerList" table to use/read information from multiple servers but still not luck. What could I be missing for this step?

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 3

Here are some things to check:

1) Confirm the skip indicator in the table SSIS_ServerList is not set for any of the servers.

2) Check for any connection errors in SSIS_Errors.

3) Make sure the table SSIS_ServerList you are referencing in the SSIS package is correct.

4) Run the package through SQL Server Business Intelligence Development Studio and watch it connect to each server

5) Remove the working server from SSIS_ServerList and see if it still collects the same information.

David Bird

Kurt-378850 SSC Rookie Points: 26 More actions · Answer 4

Hello

I am getting this error at job step s01:

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near '('.

-- Get Job Name

SET nocount ON

DECLARE @JobName sysname

SELECT @JobName = [name] FROM msdb.dbo.sysjobs

WHERE Job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))

PRINT '>'+@JobName+'<'

Any ideas? I am running SQL 2005 9.0.3159 32 bit

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 5

I was able to run the SQL you posted without problem on SQL Server 2005 Standard 9.0.3159 32 bit.

I did get that error while editing the job step and clicking the parse button. When I ran the job with just the code you posted, it ran successfully.

David Bird

Kurt-378850 SSC Rookie Points: 26 More actions · Answer 6

Hi David

I got that last one to work, however, I ran into another problem that maybe you can help out with. On job step 2, 'Recently Started', I get multiple errors that say this:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Incorrect syntax near the keyword 'SET'.

The variable name '@dtLastRun' has already been declared. Variable names must be unique within a query batch or stored procedure.

The variable name '@Subject' has already been declared. Variable names must be unique within a query batch or stored procedure.

The variable name '@Count' has already been declared. Variable names must be unique within a query batch or stored procedure.

The variable name '@tableHTML' has already been declared. Variable names must be unique within a query batch or stored procedure.

Incorrect syntax near '@subject'. (Microsoft SQL Server, Error: 156)

Donatello SSC Eights! Points: 853 More actions · Answer 7

Hey, I am having trouble with the MultiServer connection manager in the SQL_Overview_Package. It is having trouble making an initial connection. I have tried to configure the MultiServer connection manager to be as much like the the MultiServer connection manager in SSIS_MultiServer as possible ... but not seem to be working. What should I be doing to fix it?

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 8

You might want to look at the discussions for SQL Overview Part I for additional ideas on how to fix the MultiServer issue.

The article provides details on how to configure the connection and the discussions contain some advice on how to get it to work.

David Bird

Donatello SSC Eights! Points: 853 More actions · Answer 9

David Bird (7/24/2008)
You might want to look at the discussions for SQL Overview Part I for additional ideas on how to fix the MultiServer issue.
The article provides details on how to configure the connection and the discussions contain some advice on how to get it to work.

That is what I meant by "I have tried to configure the MultiServer connection manager to be as much like the the MultiServer connection manager in SSIS_MultiServer as possible". I was using that article to review the multi server connection.

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 10

Here are somethings to check

Variables:

1) Check variable names and make sure they use the same case. Their scope is your package name SQL_Overview_Package.

2) Set the default value of the variable SRV_Conn to the instance with your SQL Overview tables.

Verify Connection

a) Test the MultiServer connection. Open the MultiServer connection in the connection manager. The Server field might be blank. Enter a valid server and click test connection

b) Confirm MultiServer provider is Native OLE DB|SQL Native Client

Task: Populate ADO Variable

Confirm result set > Result Name=0 and Variable Name= User:::SQL_RS

Task: Collect Server Info

Confirm it is a ForEach Loop Container

DataObjectVariable User::SQL_RS

Variable Mappings > Variable USER::SRV_Conn and Index 0

Remember variables are case sensitive and trailing spaces could be a problem. If your database is case sensitive, some of the SQL may not work.

David Bird

Donatello SSC Eights! Points: 853 More actions · Answer 11

In table Database_Info, what are the meanings of the columns Size and SpaceUsed? Size and SpaceUsed are not exactly the same, so what is it that makes them different?

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 12

Size is the size of the Database file.

Space Used is how much space in the database file that is actual used.

MaxSize is the maximum size a file can be.

David Bird

Donatello SSC Eights! Points: 853 More actions · Answer 13

David Bird (7/28/2008)
Here are somethings to check

Thanks for the detailed reply.

What I actually wound up doing was buiding the project a little bit at a time in another Visual Studio window. This helps me understand what is going on ... and how to create something similar tailored to my concerns.

It is a very helpful application.

BigSam SSCrazy Points: 2146 More actions · Answer 14

I'm having trouble getting the steps in Collect Database Info to execute. For example the Update Data Used Size step is failing. The temp table isn't being created. If I execute the query in the Management Studio, I can get the table created but the remainder of the query fails. I don't understand the DECLARE ? statements, which seem to be at the root of the problem.

Thanks

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 15

You can try manually running each step in the "Collect Database Info" container on the server you are having troubles with. You might get an error message that will identify the cause of the problem.

I do not see any “DECLARE ?” statements. There are some '?' used when executing sp_MSForEachDB in the "Get Database Info" step. Those '?' are used by sp_MSForEachDB to substitute database names in the SQL executed.

If you have not already done so, please check the table “SSIS_Errors” for any errors messages.

David Bird