SSIS : Using Windows Environment Var's In Stored Procedures ..

Jan 29, 2008

Hi,

I have a small requirement because of which i am facing some migration problems.
For changing the variables say InitialCatalog and Custom logging destination path, i am making use of XML configuration files. Till here i have no issues.
But problem starts here. Basically DTS package is called by calling a stored procedure from a front end application.
Within the stored procedure the package location say (D:Packages<packagename.dtsx>) is being hard coded due to which i need to change the package
location paths each time when i am migrating the stored procedures to Testing and Production environments.

Is there any efficient way of avoiding the hard coding of the package paths using Windows Environment vars or something else.
If so please help me out.
Any help would be greatly appreciated.

create procedure spexecDTS_Pkg
@g_p1 varchar(50),
@g_p2 varchar(50)
AS
declare @jid uniqueidentifier
declare @cmd varchar(4000)

SET @cmd = '"C:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /F "D:Packages est.dtsx" '
SET @cmd = @cmd + ' /SET "Package.Variables[User::userid].Properties[Value]";'+'"'+@g_p1+'"'
SET @cmd = @cmd + ' /SET "Package.Variables[User::cname].Properties[Value]";'+'"'+@g_p2+'"'

DECLARE @jname varchar(128)
SET @jname = cast(newid() as char(36))

-- Create job
exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = 'Samples',
@delete_level = 1,
@job_id = @jid OUTPUT

exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = '(local)'

exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'Execute DTS',
@subsystem = 'CMDEXEC',
@command = @cmd

-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid


Thanks in advance.

View 5 Replies


ADVERTISEMENT

How Use Stored Procedures In Multi-user Environment?

Apr 19, 2007

Hi!



In my database I have all business logic in stored procedures. For example there are procedures: ReadBike and UpdateBike. Bike is business object stored in 4 tables.



On my system work 100 employees and we have one problem with this. The stroy is;

1. User A reads data about Bike1

2. User B reads data about Bike1

3. User A updates data about Bike1 (user B have old data)

4. User B updates data about Bike1



So user B don't know about chnages made by user A. How to solve that ptroblem?



It's probably solved in ADO, but I want use business login in procedures.



Regards,

Walter

View 1 Replies View Related

Executing SQL Server 2005 Stored Procedures On Windows 2003

Jul 18, 2007

I'm a new developer to both SQL Server 2005 & Windows 2003, so forgive me if this question seems a little too basic. I'm coming from a Oracle and UNIX background.



I've create a stored procedure in SQL Server 2005. I now want to execute this from the command line in Windows 2003. Eventually, I want our UNIX scheduler, autosys (which runs on a different UNIX machine obviously) to be able to execute this. In my old environment, I created a UNIX shell script as a wrapper let's say 123.sh. This shell script would accept as a parameter the name of the stored procedure I wanted to execute. If this stored procedure also had parameters it needed to be passed to it, I would have strung these values out in the command line in UNIX. Two examples of how the command line in UNIX I used to execute the Oracle stored procedure might look are listed below.



123.sh sp_my_stored_procedure input_parm1 input_parm2



123.sh sp_different_stored_procedure input_parm1



This way anytime I created a new stored procedure, I could reuse the shell script wrapper 123.sh and just pass in the name of the newly created stored procedure and any parameters it needed.



How can I accomplish this same type of functionality in the SQL Server 2005/Windows 2003 environment.



Thanks, Jim

View 4 Replies View Related

DataTables In SQLExpress Database Are Not Updating When Using Stored Procedures From Windows Application

Jan 30, 2008

I'm writing a Windows application (Visual Studio 2005, c#) utilizing a local SQLExpress database. It consists of about 10 tables and I've created about 15 Stored Procedures to address various functions... I can run the update Stored Procedures interactively within the designer and the data tables update as designed. However, if I run the update Stored Procedures from within my windows application is where they fail. I get no error messages, if I return a rowcount variable from the Stored Procedure it tells me that one row was updated (SELECT @RtnVal == @@rowcount)... but when I open the subject table in the designer, there is no new data.

My update queries address both insert and update functions, so if it is new row of data, it performs the insert action, otherwise it updates an existing row.

I can query the data using my stored procedures to load default values into my windows form, I can search and find client records to display in the form... I just can't update records. I'm of the opinion that it is a rights issue, but I can't find any resources that address user access accounts with SQLExpress and windows apps. The current connection string for my local database is set for Integrated Security = true; User Instance = true

I've tried two approaches... one utilizes my stored procedure....


bool bSave;
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand("SavAuthTesterData", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CtrId", SqlDbType.NVarChar, 20).Value = TestCenterID.ToString();
cmd.Parameters.Add("@AT_Id", SqlDbType.NVarChar, 15).Value = GnuAuthTesterId.ToString();
cmd.Parameters.Add("@AT_Name", SqlDbType.NVarChar, 50).Value = GnuAuthTesterName.ToString();
cmd.Parameters.Add("@AT_Pwd", SqlDbType.NVarChar, 15).Value = GnuAuthTesterPwd.ToString();
cmd.Parameters.Add("@Maint", SqlDbType.NChar, 1).Value = GnuAuthTesterStatus.ToString();
cmd.Parameters.Add("@ModBy", SqlDbType.NVarChar, 15).Value = sAuthTester.ToString();
try
{
conn.Open();
cmd.ExecuteNonQuery();
bSave = true;
}

Then I tried a direct insert...

SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand();
SqlTransaction myTrans;
conn.Open();
cmd.Connection = conn;
myTrans = conn.BeginTransaction();
cmd.Transaction = myTrans;
string disDte = DateTime.Now.ToShortDateString();
try
{
string sCmdText = "INSERT INTO Tester (CenterId, AuthTesterId, AuthTesterName, AuthTesterPwd, "
+ "CreateDte, Maint, ModBy) "
+ "VALUES ('" + TestCenterID.ToString() + "','"
+ GnuAuthTesterId.ToString() + "','"
+ GnuAuthTesterName.ToString() + "','"
+ GnuAuthTesterPwd.ToString() + "','"
+ disDte.ToString() + "','"
+ GnuAuthTesterStatus.ToString() + "','"
+ sAuthTester.ToString() + "')";
cmd.CommandText = sCmdText;
cmd.ExecuteNonQuery();
myTrans.Commit();
bSave = true;
}


Both fail... which points to the only common point in both approaches, the connection string and user rights... but I can't find any place to address user accounts or configurations for windows apps.

Thanks for whatever help you can offer...

Jim-

View 5 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

Can We Use Stored Procedures In SSIS..?

May 25, 2007

Hi,
First Question is can we use Stored Procesdures in SSIS..if yes where i mean by useing which transformation how..?

Secondly i am stuck up here..:

SELECT JAM_ID = a.JAMGCD,
JAM_NM = a.JVDQVN,
JAM_DESC = CONVERT(varchar(50),SUBSTRING(a.JAMATX,1,CHARINDEX('..',a.JAMATX )-1)),
JAM_CODE = ?
from table1...

I am using this Query in the OlEDB Data flow task to retrieve the columns which i need and do some transformations..on them...now i get JAM_CODE by using JAM_DESC can i use it...i think i cant
....can anyone suggest how to deal with this.?

thanks
ravi

Nothing much that i can do..!!

View 2 Replies View Related

Using Stored Procedures In SSIS

Aug 22, 2007

Once again I have a configuration database (CD) question. I am trying to use SP_HELPSRVROLEMEMBER and SP_CONFIGURE in the CD. I am having difficulty calling the stored procedures in the data flow task. What I would like to do, is to just call both SP's in the OLE DB Source and insert them into the OLE DB Destination. However, this doesn't seem to be that easy. Does anybody have any ideas as to how to insert the results from a SP into a central server? Any ideas would be great.
-Kyle

View 5 Replies View Related

Transact SQL :: Get Windows Environment Variables

Aug 31, 2015

I am migrating the BE of an Access app. to SQL server 2012. I need to get the user's login name (Windows Authentication login). This can be done using xp_cmdshell, but, xp_cmdshell is considered dangerous and I wouldn't be able to run it once I deploy the app. to the company servers (currently I have SQL server on my computer and as an admin I can enable xp_cmdshell to run, but IT doesn't allow it in company servers for security reasons).

Another question, is it possible to send data from the logged in user from Access to SQL server? What I need to do is let SQL know the username of the logged in user, then, use it to filter the data on SQL. Idea is that user can only run queries for his data (he can't view other user's data unless he is a manager or an admin (currently the app. in Access logs the user in automatically if his Windows Domain username is found in the user's table, and set's his role found in the Roles table). It is this functionality that is giving me some problems to migrate to SQL.

I created a function that uses the System_User SQL built-in function, this retrieves the SQL login username, but, the app. uses 1 SQL local account to connect to the server, so in essence it doesn't work as I need the Windows Domain account username.

View 10 Replies View Related

Data Warehousing :: Run Stored Procedures On PDW Via SSIS

Aug 4, 2015

How do you run a stored procedure on PDW via SSIS? I've tried Execute SQL Task and Execute T-SQL Task but in both cases the task will run and complete almost immediately. Task shows success, no errors, but nothing happens in PDW.   PDW admin console does not even register the query. Procedures run fine manually from SQL Server Object Explorer connection.

View 3 Replies View Related

Data Warehousing :: How To Use Stored Procedures In SSIS

Jul 23, 2015

How to use Stored Procedures in SSIS?

View 2 Replies View Related

SSIS / MSDB Stored Procedures / Permissions

Jul 23, 2007

All,

Here is my problem, its very simple, But I dont have a solution.
To run / import / what ever I else I forgot (?) SSIS in SQL SERVER, what are permissions I'll need.

So far I have developed everything in BIDS, when I try to migrate it to a sql server by using Import package in Integration Services I got the below error.

TITLE: Import Package
------------------------------
The EXECUTE permission was denied on the object 'sp_dts_listpackages', database 'msdb', schema 'dbo'. (Microsoft SQL Native Client)

------------------------------
ADDITIONAL INFORMATION:
The EXECUTE permission was denied on the object 'sp_dts_listpackages', database 'msdb', schema 'dbo'. (Microsoft SQL Native Client)

The error is very clear in itself, While I have raised a request for the execute permission of this stored procedure, i also like to know what kind of permissions I will need in MSDB to work with out any problems. So that I dont have to go to DBA for execute permission for each error I may get for this.Right now I dont have execute permission on any of the Stored Procs in MSDB.

If any body can show any pointers that would be help full.

View 4 Replies View Related

SSIS, DataReader And Parameterised Stored Procedures

Sep 26, 2007



Hi, i have a text file destination which i am trying to output data to via an SSIS package. I cannot seem to be able to set up the package so that i execute a parameterised stored procedure and insert the data into a text file. I have set up the following

connections:

a. ADO.net connection to the SQL 2005 database
b. Flat file destination, which links to an empty file on the network : D:GCDReportsfeedsgmr_p201.fs_20070724

ANy quick help would be greatly appreciated.

David

View 3 Replies View Related

SSIS Updating Package Variables From Stored Procedures

Jul 31, 2007


I have been looking at the project Real reference implementation for doing auditing of data uploads. The tables and store procedures are in place using identical field and variable names. However, when running the package it does not update the relevant log entry with the end time and status.

I have performed the process manually running the stored procedures, providing the values directly and everything works fine.

I can only assume that the LogID variable is not being updated during the run and therefore the onEnd procedure cannot update the relevant log entry.

One side effect is that it does update the record when it€™s the first record entered into the table but not on any other inserts. Clearing the table each time is not an option.

This has become rather frustrating and would appreciate any assistance.

Thanks.

View 1 Replies View Related

Performance Comparision - Stored Procedures VS SQL (Inside SSIS)

Mar 23, 2007

I am working on a technical design of data integration ETL package which will be moving data from SQL Server Source to DB2 destination. I currently have two options, when moving data to DB2(IBM AS400). I can call a AS400 Stored Procedure, and pass in the data to the stored procedure, and perform the insert processing within the AS400 environment or I could do inserts from SSIS in a DFT and write individually to AS400 tables. My question is from a performance and good practice perspective, which method should I move forward with. I need a possible list of pros- and cons when using AS400 Sproc vs using SQL within SSIS? I would really appreciate response from individuals who have done something similar in the past. Thanks a lot and I am really looking forward to responses.

View 1 Replies View Related

Displaying Print Statements Of Stored Procedures In SSIS Logs

Sep 6, 2007



Hi

I have few print statements in a stored procedure that gets called from the SSIS package. How do I make sure that these are captured in the SSIS log meaning how do I get them to be displayed in the Package Explorer window when running from the Business Intelligence Studio IDE.

View 1 Replies View Related

Getting Replication To Work On Windows 2003 Server X64 Environment Using SQL 2000

Feb 28, 2006

I have a mobile device application using mobile sql 2005 replicating with sql 2000 in a x86 environment. This works fine!

I'm having issues getting this to work under Windows Server 2003 X64.

I've got all the components installed under the X64 environment including CLR 2.0 X64 and the mobile sql tools. the but when I run the Configure Web Synchronization Wizard I get the following error. SQL Server 2005 Mobile Edition Server Tools were not found on the IIS server. Run the SQL Server 2005 Mobile Edition Server Tools installer....

My question is: Were do I get the X64 version of these tools?

sqlce30setupen.msi
sql2Ken@P4.msi

The SQL environment is X86 as follows: SQL2000 SP4

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Any help would be much appreciated!

View 6 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

SQLServer 2005 JDBC Driver - Date Rendered Correctly On Local Environment(windows), But Not On Websphere Server (unix)

Dec 6, 2006

Hi,

We are trying to move our application from SQL2005 to SQL2005, we are using 2005 jdbc driver 1.1.

The problem we have is, when a date is retrieved from the database, only the year part got correctly, but the date is automatically set to January 1 of that year. The interesting thing is this problem doesn't occur on local development environment (developer's windows platform), it only happens when we deploy the application to production server (unix).

Does anyone know what is going on, how to fix it?

Thanks.

Cathie

View 12 Replies View Related

How To Open SDF File Stored On Windows Mobile Device From A Windows Desktop App?

Apr 14, 2008

Long story short -

Have two C# applications - one WinForms desktop app, the other a WinForms smart device app. Both use SQL Server Compact CE 3.5 to store data locally. Both work just fine.

Now the desktop app needs to open and update the .SDF file that is stored on the Windows Mobile device. (Device will be docked via USB.)

What format would the path to the .SDF look like that the WinForms application would need to specify in order to open it?

Thanks for reading.

View 3 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

Shared SSIS Staging/Dev Environment

Jul 16, 2007

We are in the process of setting up a shared staging/development enviroment in which multiple developers will deploy their packages for testing etc.

The plan is to allow the developers to remote in to execute their packages, but we are worried about the concurrency limit of 2 users per terminal services.

It is my understanding that you cannot execute packages remotely (ie you have to be on THAT box to kick it off. (FYI - we are not planning on using SQL Agent at this point - That will be with DBAs in UA)

Please let me know what options are available/best practices...

Thanks!

View 5 Replies View Related

SSIS Package Deployment In Different Environment

Sep 21, 2007



Hi, All,

I am using Package Configuration to simplify SSIS package deployment process. All the configuration information are stored in XML file. So far so good, However, since I have many, 20, packages. For each package, there is one configuration file to it. During the deployment process, I have dynamically modify connecting string (server name, DB name) to new ones. It ends up 20 or more modification and it's eaily for me to make mistake. Is there any workaround such as setting up environment variable, I guess, to allow me only modify once and apply it to all the packages?

TIA,

John

View 5 Replies View Related

SQL 2012 :: Getting Environment Variables For SSIS Project?

Sep 9, 2015

Is it possible to export the environment variables for an SSIS 2012 project? And if so how is it achieved?

View 2 Replies View Related

Moving SSIS Pacakges To The Server Environment

Feb 7, 2008

Greetings and warm thanks to those of you that have been so helpful on this forum.

We recently started using SQK 2005 SSIS. Using Business Intelligence Studio (Nice Job Microsoft!!) we created and tested packages on a developer workstation using a local instance of SQL Server.

Now we want to run these packages on the development server. You'd think this would be as easy as publishing a web page using ftp but we are stuck!

What pieces do you need on the server to run the packages and where do you find them? We have the super-duper MSDN premium, planning on purchasing SQL Server Enterprise Edition once we move our project into the production arena.

Thanks very much we are all scratching our heads today on this.

View 3 Replies View Related

SSIS Package Run Fails In Deployed Environment

Mar 20, 2008

This has only started happening in the last two days

When we invoke our SSIS package from a web service it used to work fine but now gives the following error. the DLL name changes all the time. Any help would be appreciated





Code SnippetSystem.IO.FileNotFoundException: Could not find file 'C:WINDOWSTEMPvrjeaanf.dll'.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] sources)
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Evidence evidence)
at System.Web.Services.Protocols.XmlReturn.GetInitializers(LogicalMethodInfo[] methodInfos)
at System.Web.Services.Protocols.XmlReturnWriter.GetInitializers(LogicalMethodInfo[] methodInfos)
at System.Web.Services.Protocols.MimeFormatter.GetInitializers(Type type, LogicalMethodInfo[] methodInfos)
at System.Web.Services.Protocols.HttpServerType..ctor(Type type)
at System.Web.Services.Protocols.HttpServerProtocol.Initialize()
at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)

View 5 Replies View Related

Execute SSIS Package In SQL Express Environment

Nov 24, 2006

Dear all,



I have an SSIS package and want to execute it on a computer that only
has SQL Express installed on it. Since SQL Express didn't brought
dtexec or dtui so what should I do ?

Sorry for my bad English...



Thanks for advance,

View 4 Replies View Related

Setting Environment Variables For SSIS Packages

Feb 29, 2008



Hi all,

We're just in the middle of performing our first release of SSIS packages through various environments.

The way we are set up currently is the developer will check the package(s) and related config files out of source control, develop on their own machine and check everything in again. Then we deploy the packages consecutively to the Dev, Tst and Prd servers.

We are going down the path of using one environment variable for every config file. some packages share config files (e.g. we've only one config file for each database or ftp connection etc.) and some config files are package specific (error log file connections and success/failure e-mail sources etc.).

What we want ideally is a script that we can check into source control that will create the environment variables on a server at deployment. The "set" command at the command line can be used to change the value of an environment variable, or to create a session-specific variable, but not to create environment variables.

So far the only method that we're using is manually typing in the environment variable names via control panel and copying and pasting the paths into the value fields. Given that we're deploying potentially hundreds of config files, it's obvious that this new-fangled GUI point-and-click and copy-and-paste method of deployment is absolutely foolproof and totaly not prone to any error whatsoever.

Please tell me there's a way to create and set environment variables without going through control panel. running a script or something to do it automatically will: Ensure that each environment is set up accurately and identically, eliminating human error. Ensure that when a developer checks out a package to their local drive, although they may have to change the variable values, he can at least create the relevant variables without having to type them in. Enable efficient migration to another new server (for example during Disaster Recovery).

Can anyone point me to some example scripts at all?

Kind Regards,
Andrew.

View 4 Replies View Related

Integration Services :: Environment Variable In SSIS Not Being Recognized In JOB

Jun 16, 2015

I've created a SSIS Package and it's connection is based on Environment Variable(please seeprocedure).

Now, I'm trying to create a job that calls this package and it seems that when you view Data Sources, it still pointing to the old server.

But when you open-up the package through BIDS in the same server, it's using the new reference that I have specified in the environment variable (please refer to the first image).

I came across this blog with the same issues as mine. He suggested to re-start the SSIS Service which I already did  but nothing happens. I even re-started the SQL Agent but still no luck.

I'm not sure what else is missing except for re-starting the machine which is the last thing I want to do as this is PRODUCTION server.

View 7 Replies View Related

SSIS Dynamic Configuration - Environment Variable Problem

Aug 24, 2007

Dear all,

I have a problem with SSIS reading an environment variable after deploying the packages to a server. I explain.

I have an Parent Packages ETL_MAIN_PACKAGE.dtsx that reads the child packages from a record set and loops on it to execute them with the Execute Package Task task. The first child package to be executed is called DIM_PERIODIC.dtsx.

On my local machine, the Parent Package is configured to read its database connections from an XML file SSIS_configfile.config located on my C: drive. The path (C:SSIS_configfile.config) to this file is stored in the environment variable BI_ETL.

When I run the Parent Package inside SSIS only machine, the connections are read and the package executes perfectly. Now, I want to deploy the packages on our server.

I copied the XML configuration file to the server C drive, I created the same environment variable BI_ETL and set its value to C:SSIS_configfile.config and I rebooted the machine (in case).

The execution of the Parent package is managed by a stored procedure. I use xp_cmdshell command. The command line generated is :


cmd.exe /c dtexec /file "C:ETL_DeploymentETL_MAIN_PACKAGE.dtsx" /CHECKPOINTING OFF /MAXCONCURRENT " -1 " /SET Package.Variables["P_PACKAGE_PATH"].Value;"C:ETL_Deployment" /SET Package.Variables["P_LOOKUP_PATH"].Value;"C:ETL_DeploymentETL_LOGS" /SET Package.Variables["P_SCHOOL_CODE"].Value;"007"

This command generates an error telling that the Environment variable is not found and it throws this error:

Error : 2007-08-23 18:59:10.25
Code : 0x80019003
Sourse : The configuration environment variable was not found. The environment variable was: BI_ETL. This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid.
End Error


Error: 2007-08-23 18:59:10.25

Code: 0xC001401E

Source: ETL_MAIN_PACKAGE Connection manager "Package Path Execute"

Description: The file name "C:ETL_Deployment" /SET Package.Variables[P_LOOKUP_PATH].Value;C:ETL_DeploymentETL_LOGSDIM_PERIODIC.dtsx" specified in the connection was not valid.

End Error

I run the package on the same server with a command line directly in a DOS window:


C:>cmd.exe /c dtexec /file "C:ETL_DeploymentETL_MAIN_PACKAGE.dtsx" /CHECKPOINTING OFF /MAXCONCURRENT " -1 " /SET Package.Variables["P_PACKAGE_PATH"].Value;"
C:ETL_Deployment" /SET Package.Variables["P_LOOKUP_PATH"].Value;"C:ETL_Deplo
ymentETL_LOGS" /SET Package.Variables["P_SCHOOL_CODE"].Value;"007"


I don't have anymore the error saying that the Environment variable is not found, but I still have the same second error :


Error: 2007-08-23 18:59:10.25

Code: 0xC001401E

Source: ETL_MAIN_PACKAGE Connection manager "Package Path Execute"

Description: The file name "C:ETL_Deployment" /SET Package.Variables[P_LOOKUP_PATH].Value;C:ETL_DeploymentETL_LOGSDIM_PERIODIC.dtsx" specified in the connection was not valid.

End Error

I conclude that the environment variable is not read at all.

Does anybody have an idea on how to solve this problem ?

Many thanks.

Sami



View 10 Replies View Related

How To Deploy Updated Database From Development Environment To Live Environment?

Nov 16, 2005

I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?

View 3 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

SQL Server Admin 2014 :: SSIS 2012 Environment Variables Are Not On Sort Order

Feb 10, 2014

I have SSIS 2012 Enterprise, using catalog deployment and have more that 50 environment variables for connection to databases across my enterprise.

The problem when i go to configure the packages after deployment and pick the proper env variables, that are not sorted, so i have to browse all entries in order to find the proper entry in environment variables.

View 1 Replies View Related

Quick Upgrade SQL Environment (Database Engine, SSIS, SSRS, Etc.) Exchange Hardware Plan

Feb 7, 2008

We have installation of Dbase Engine and SSIS that is PRODUCTION, and want to replace with newer hardware. In "the old days", we built "boxname_new" and installed SQL with "sqlname_new", took PROD users off-line, and quickly renamed original boxes/SQL and new boxes/SQL to original name, copied data and off we went with upgrade.



NOW, the "renaming" option for SQL tools is not supported, but with re-installation.



Has anyone developed game plan steps for accomplishing hardware upgrade, including SQL environment swap with MINIMAL downtime for PRODUCTION environment? Can you share?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved