Configuration Variables
Apr 1, 2008
Hi,
How to access "Package Configuration" variables values inside the VSA.
For Ex:
When I create the Package, at the Package Configuration Level I have created 2 variables. One is for MDB file Location and another is for SQL Server configuration details.
Now, When I move from Dev. server to Prod Server, the environment would change therefore I want to access these variables inside the design script (VSA).
Anyone could advice in this.
Regards,
View 1 Replies
ADVERTISEMENT
Feb 22, 2006
Hi ..
I need some information on the package configuration files. i.e how to set the configuration parameters from outside the packages. Pls provide me some detailed information along with examples. I want to make my packages to read paramaters from the configuration files as we have in .NET(web.config)////
Secondly I want to know how to play well with variables.. i.e setting a variable.. reading a variable.. passing a variable in the SSIS package dataflows and datatasks.. respectively. Please provide some infomation and a good exam[ple on it I need
View 1 Replies
View Related
May 4, 2006
Hi,
We can set the connection parameters using Variables as well as thru Package Configuration. My question is, if we use both to pass value for a connection string, which one will take precedence or in other words, which value will be used?
Advance thanks for your help.
View 1 Replies
View Related
Nov 13, 2007
I have three variables that are stored in an SQL Server configuration table. When I execute the package, they do not seem to be getting updated. At what point do the configurations get loaded?
View 4 Replies
View Related
Mar 26, 2007
It is pretty easy to create the package configuration to the shared, never-changing path to XML file. But to get this to work, I have to add a bunch of package variables which are driven from the configuration. I then use expressions to compute actual paths to source files and packages.
This business of copying a block of variables from one package to another is extremely tedious, and at least to me, something of a maintenance issue. I have found if I get ahead of myself and forget to create the variables first, then Visual Studio is NOT very forgiving, and will complain repeatedly just prior to crashing without the offer to save anything. If I add a new variable, it is like an easter egg hunt. This does NOT feel very single point of maintenance when variables must be used in this way to really effectively apply configuration, and it is so tedious and error prone, and unforgiving. I seem to spend a great percentage of time copying the names and datatypes and values of variables ONE AT A TIME. What is that all about?
I hear people telling the praises of configurations, but I must be missing something.
With DTS I used a system where global scope settings were "injected" into packages as they ran, and I never had to worry about anything, and it was damn hard to goof it.
Please someone, evangelize me!! Are people bypassing VS and using NOTEPAD on the packages or something like that to save time?
View 2 Replies
View Related
Nov 15, 2007
Hello,
We have an existing OLD System in SQL Server 2000 DTS Packages.
The Whole application runs on DTS.
There are several Packages which are called from a Master Package. Each Child packages have their own Global Variables. Most of them are the File Location variables to have the Source Location of the Input Data, mainly from the Excel Files.
Now, even if the Global Variables are there to change whenever they want to change the Locations of the Files, they have to goto each child package and change the variable themselves.
To resolve this issue, they want a configuration File (INI) / Table which would store those Variables. My thought is to read from that File/Table and Update all the packages' global variables through an ActiveX Script as the First Step of the Master package. That would eliminate the need of changing anything in the existing System.
But the Problem is the management (PM / DBAs / Team members) have different views to store the Configuration data.
1. Some wants it into a Different Database, having one table for this application so in future they can also add another table for some other application.
2. Some wants to store it in a Table in the Same Database of this Application.
3. Some wants to save it as a INI file.
As i'm the one who's going to really implement it, they have asked me to research for a best solution out there.
so I request to help me to decide which is a good solution and why.
Best Regards,
Deepak
View 4 Replies
View Related
Sep 22, 2007
I am getting following error when trying to install SQL express 2005 on XPSP2.
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine SIGMA-805539A79 Error:2147944122 (0x800706ba).
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=70342
I tied re-installing WMI using http://blogs.msdn.com/jpapiez/archive/2004/12/09/279041.aspx link but could not get it working.
Do i need IIS installed? Its not installed on this box...
please suggest something... i am stuck...
Thanks,
View 3 Replies
View Related
May 2, 2007
I have just finished installing SQL 2005 Ent Edition on Win 2000 Adv Server, SQL2005 SP2, and SP2 Hotfix KB934458. After the installation, I could see and configure all services via SQL Configuration Manager and SQL Server Surface Area Configuration tools. This worked for a couple of days and now both configuration tools no longer detect SQL2005 components. SQL Server Surface Area Configuration issued an error that said "No SQL Server 2005 components were found on specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLAC)". SQL Configuration Manager did not list any installed services. I don€™t know what caused this. Anyone has any idea? Please help! Below is the Installation Report which shows installed components.
===================================
The following components are installed on this server
DEV
Analysis Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Database Engine
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Reporting Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
QA
Analysis Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Database Engine
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Common components
Integration Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Notification Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Workstation Components
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
===================================
View 1 Replies
View Related
May 23, 2006
Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do
thanks in advance Dave
Background:
SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go
http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/24/6843.aspx
Wizard results:
Name:
ETL
Type:
SQL Server
Connection name:
ETLConfiguration
Any existing configuration information for selected configuration filter will be overwritten with new configuration settings.
Configuration table name:
[dbo].[SSIS Configurations]
Configuration filter:
PT_CUST_ABR
Target Property:
Package.Variables[User::gsPreLoad].Properties[Value]
Package.Variables[User::gsPostLoad].Properties[Value]
Package.Variables[User::gsLoad].Properties[Value]
Package.Variables[User::gsFlatFilename].Properties[Value]
Package.Variables[User::gsFileName].Properties[Value]
Package.Variables[User::gsCDOMailTo].Properties[Value]
Package.Variables[User::gsCDOMailSubject].Properties[Value]
Package.Variables[User::giRecordCount].Properties[Value]
Package.Variables[User::giFileSize].Properties[Value]
Package.Variables[User::giBatchID].Properties[Value]
Package.Variables[User::gdFileDateCreated].Properties[Value]
Package.Connections[MyDatabase].Properties[ServerName]
Package.Connections[MyDatabase].Properties[InitialCatalog]
USE [ETLConfiguration]
GO
/****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
View 3 Replies
View Related
Sep 4, 2006
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
View 8 Replies
View Related
Jan 24, 2006
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
View 2 Replies
View Related
Jan 14, 2006
It seems to me, that the best way is to have one Environment Varible containing the name of the SQL Server, so that you can look up the configuration in the SSIS Configuration Table when you run the package.
Is this the preferable way of doing it ? I would like to hear some positive/negative comment of why chosing a configuration type instead of another.
It seems to me that putting all of the configuration in the Environment variable is harder work but most secure (server breakdown vs table corruption/database error...)
Let's have some comments
View 3 Replies
View Related
Mar 6, 2006
Hi Guys,
Here's the current scenario:
I am creating a windows application to manage my ssis packages.
My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.
However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.
The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.
Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.
Thanks,
kervy
View 3 Replies
View Related
Apr 2, 2001
I have a Sql7.0 box inside a firewall and Sql6.5 outside the firewall. I would like to use a DTS to transfer data. I followed the KB artice on configuring SQL to listen on a specfic port, but still have problems. I added entries in Client Config on both machines. Still no luck, looks like the 7.0 box not talking on new port...
Any suggestions ????
View 1 Replies
View Related
Sep 25, 2000
Hi
I know that "50" = binary.
"54" = ?
help!
View 1 Replies
View Related
Mar 12, 2001
I have a question about SQL Server configurations, especially those related to memory.
We are running SQL 6.5 SP5a on a 400 MHz machine with 1GB of RAM.
Our memory setting is 409600 (800 MB), which according to all the documentation that I have read is a conservative figure. The problem I believe is in our settings for Locks and Open Objects. The settings that are in place are Locks=3000000 and Open Objects=500000.
I am not a DBA, I am a developer, but have somehow become part of the DBA team. These settings seem out of proportion with the memory we have. The reason given for 3 million locks, by other members of the team, is that our biggest unit of work is a query that performs a select into of approx. 3 million records. To further compound things, we are getting an out of memory error on the server after only a day of running.
The other members of the team have implemented a start and start of the service to occur at 4 AM every morning, but I am convinced that this is not the appropriate action, but they will not action any configuration changes unless I can prove that it will help the memory problem and not effect the queries.
If anyone has any information or links to information regarding Locks and how they relate to query performance, or at least a sample of what your settings are it would go a long way towards convincing either myself or the rest of the team what direction to go.
Thank you,
Jeremy N. Sutka
View 1 Replies
View Related
May 28, 2008
Hello,
I have SQL Reporting Services 2005 installed. When I go into the SQL Reporting Services Configuration Manager I immediately get an error that says that "No report servers were found on the specified machine" then Details: Invalid Namespace.
The machine that it is running on is our report server. The name in the Machine Name says the name of the report server not the name of the specific machine so maybe this is the problem? I think the name of the report server should go in instance? Problem is that it won't let me change anything and it just gives me that error and everything else on the screen is disabled.
Has anyone experienced this?
View 3 Replies
View Related
Mar 24, 2008
Hey guys, got a question regarding hardware configuration for a new SQL server we are installing here. We are migrating from a Windows 2000 standard, SQL 2000 standard box with 4GB of RAM to a Windows 2000 datacenter, SQL 2000 enterprise box with 32GB of RAM.
What would be the best way to set this up in Enterprise Manager? Should I just let SQL Dynamically use the RAM or should I select a fixed amount? Also with the processors, should I allow SQL to use all available processors or just 3 of the processors? Also with such a large amount of RAM, I've read people setting there pagefile to 0 so there is no load on the hard drives.
Let me know what you guys think, thanks!!
View 3 Replies
View Related
Oct 2, 2007
We are running SQL 7 on a server, and are moving to a new server and will beupgrading to SQL 2005 at the same time. Currently, both the old and the newservers have two drives, one for programs and one for data.With the current configuration, SQL 7 and the data are both installed on thedata drive, in the MSSQL7 directory. Our sys admin wants to install SQL 2005on the program drive of the new server, while putting our databases on thedata drive. I argue that if SQL itself is on the program drive, then thesystem databases will be in one place, while our databases will be inanother. So I'd prefer to have SQL 2005 installed on the data drive, as itis now.I'm interested in any feedback regarding what you guys think is the betterconfiguration, and also if there's any performance hit from having theprogram and the database on two drives of the same machine.Thanks,Neil
View 11 Replies
View Related
Nov 11, 2005
Hi All,
I tried to install SQL Server 2005 Developer Edition RTM version and SCC
View 6 Replies
View Related
Apr 25, 2008
In my Package Configurations, I have three different configurations. Two are of the type, SQL Server and one of type XML. I want to get the currently used Configuration name, Configuration String and the configuration type in my Script Task code. Can anybody help me with this code.
Thank you.
View 7 Replies
View Related
Dec 6, 2006
We store our configurations in a SQL Server configuration table. We have SSIS packages that can be used in different senarios. Each of these senarios requires different configuration values.
Is it possible to change the SSIS configuration key used for a package at run time? i.e. we'd like to be able to use a job that says run this SSIS package but use configuration values with that name.
View 6 Replies
View Related
May 12, 2008
Hello,
I've installed SBS 2003 with SQL 2005. The MBSA shows that the SQL service and agent should not be run as a Local System or Administrator. I've tried to create a new user and start the service with the new user, but no matter what I do, I cannot start the service with the new user. I've tried to add the user to the SQL* groups and Domain Users group, but no luck. I've tried adding permissions to those groups to all the SQL directories, but again, no luck. I'm getting error 5 - permission denied. Anyone got a good HOWTO?
Thank you!
View 1 Replies
View Related
May 28, 2008
Hi,
I am very new to SSIS and SSRS.I just installed in sql server 2005 professional in my office machine. I am going to work on SSIS and SSRS. My machine itself will be the server.Could you please tell me what are the configurations I should do?.. Thanks in advance.
-Selva
View 4 Replies
View Related
Jun 30, 2006
Hi,
In my package I am using some 3 to 4 database connections. Hence I can say 3 to 4 connection managers I have. Other than that I have 2 Flat file connection manager.
What will be the best way to configure the same? and Please let me knowwhy that is the best choice.
Thanks
View 1 Replies
View Related
Jul 13, 2007
What is @@variables means in sql server?
View 3 Replies
View Related
Jan 21, 2004
Is there a way to use the LIKE keyword with variables like below?
DECLARE @Name CHAR(10)
SET @Name = 'MyName'
SELECT * FROM table
WHERE my_name LIKE @Name
This won't work, but you get the idea of what I want. Any thoughts?
Thanks,
View 1 Replies
View Related
Jun 4, 2004
Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?
View 7 Replies
View Related
Jun 8, 2005
Anyone know how to write the portion in red in a stored procedure?LEFT OUTER JOIN TITLE AS T ON (POS.TITLE_ID = T.TITLE_ID)JOIN DISTRICT_LOCATIONS AS DL ON (POS.DISTRICT_LOCATION_ID = DL.DISTRICT_LOCATION_ID)WHERE POS.PRIMARY_IND = 1 IF @DISTRICT_LOCATION_ID != 'All' BEGIN and DL.DISTRICT_LOCATION_ID = @DISTRICT_LOCATION_ID ENDIF @ATTENDANCE_STATUS_ID!= 'All' BEGIN and AST.ATTENDANCE_STATUS_ID= @ATTENDANCE_STATUS_ID ENDUNION ALLSELECT DISTINCT 5 AS TAG ,3 AS PARENT ,convert(varchar,getdate(),101) as [ACTIVITY_REPORT!1!REPORT_DATE] ,AX.ACTIVITY_CLASS_ID AS [ACTIVITY!2!ACTIVITY_CLASS_ID] ,NULL AS [ACTIVITY!2!ACTIVITY_NAME]
View 2 Replies
View Related
Feb 16, 2000
Hi,
I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename.
I get the error that it can't open a database @databasename.
Can I declare the database object in T-SQL?
Thanks
View 1 Replies
View Related
Nov 2, 2000
I have this script:
.....
USE master
go
/* Get Name of Server & declare variables */
declare @sname varchar(30)
declare @db1 varchar(30)
declare @db2 varchar(30)
declare @db3 varchar(30)
declare @dbf1 varchar(30)
declare @dbf2 varchar(30)
declare @dbf3 varchar(30)
select @sname = rtrim(substring(srvname,5,30)) from sysservers
print 'The name of this server is: ' + @sname
Set @db1 = @sname + 'database1'
Set @db2 = @sname + 'database2'
Set @db3 = @sname + 'database3'
Set @dbf1 = @db1 + 'RL_log'
Set @dbf2 = @db2 + 'RL_log'
Set @dbf3 = @db3 + 'RL_log'
print @db1
print @dbf1
go
ALTER DATABASE @db1 MODIFY FILE (NAME = @dbf1, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db2 MODIFY FILE (NAME = @dbf2, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db3 MODIFY FILE (NAME = @dbf3, MAXSIZE = UNLIMITED)
go
.....
When I run it, I get the following errors:
.....
The name of this server is: KANSASCITY
KANSASCITYdatabase1
KANSASCITYdatabase1RL_log
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@db1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db2'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db3'.
.....
Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.
WHAT I AM TRYING TO ACCOMPLISH:
I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.
Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?
Thank you (in advance)
View 2 Replies
View Related
Mar 28, 2003
Hi
I think this is an easy question.
Is it possible to use a variable in a create database statement
i.e.
declare @db_name varchar(20)
select @db_name='new_db'
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
DROP DATABASE @db_name
CREATE DATABASE [@db_name] ON (NAME = @db_name, FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataew_DB_Data.MDF' , SIZE = 300, FILEGROWTH = 10%) LOG ON (NAME = N'new_DB_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataImport_Utility_DB_Log.LDF' , SIZE = 30, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
Also, if the above is possible, how can i pass a database name to the script if I am calling it from a batch file (using osql).
Thanks,
Jim
View 2 Replies
View Related
Jul 26, 2000
I want to change database in my SQL-script like this:
DECLARE @DB_NAME varchar(30)
SELECT * FROM @DB_NAME.dbo.TableName,
but it is syntactically incorrect.
I could use
USE (@DB_NAME)
but I write Stored Procedure, it is not allowed using
USE statement in any Stored Procedures.
View 2 Replies
View Related
