I am going thru the Integration Services Tutorial found in Books Online and I'm on Lesson 3. It has to do with creating a configuration file for the source file location. I followed the steps and now I need to open the .dtsConfig file and type in the folder location.
The exact direction says,
"
In Notepad or any other text editor, locate and open the SSISTutorial.dtsConfig configuration file that you created by using the Package Configuration Wizard in the previous task.
Change the value of the ConfiguredValue element to match the path of the New Sample Data folder that you created in the previous task. Do not surround the path in quotes.
Save the change, and then close the text editor.
"
When I open the file, I see this. What am I supposed to do here?
When adding a SSIS DTSX package solution to Sourcesafe the SSIS Package Configuration file (.DTSConfig) is not included. We are using sourcesafe version 6.0d and Visual Studio 2005 Team Edition for Developers.
I'm trying to easily change a value in 80 ssis configuration files from "localhost" to "myservername". I downloaded WinGrep to do this, but it's balking on some of the files, saying that they are binary files.
Does anyone know how to make the *.dtsconfig files NOT binary in SSIS?
Any suggestions GREATLY appreciated.
I won't go into the reason I have my servername in 80 places...
Hi, i'm new in this forum and in SSIS, so I apologize if mine is a stupid question and also for my english.
I have this problem: i have to develop an ETL package and i have to set the transformation between input and output columns using a configuration file, i.e. my client want to change the transformation simply modifying a config file( for example from UPPER to LOWER ).
I tried using dtsconfig and setting EvaluateAsExpression with value 1( true ) and Expression UPPER[Column 2] but i get an error saying that Column 2 is not a variable.
I have an SSIS package with a small number of connections for various databases, both SQL2K5 and AS2K5.
When I build the package the .dtsConfig file is produced but the contents for some of those connections are not accurate relative to the package itself. There are some old values retained in the .dtsConfig file.
Worse, when I deploy the package to another machine the installation should allow me to change these values to ones appropriate for the new server. While I am able to change them they are not retained and the old values crop back up making the package fail during execution.
Any idea where I can smack this thing to unstick these values?
Would specifics help? I can post the file(s) if need be.
Apologies if this has been posted before and feel free to point me to an existing thread if it has.
I currently have a dev, stage and live environment for a package
i have created a load.dtsconfig file that contains various bits such as connection strings, email to, from, etc which i created via the package configurations in the dev package. I then made a few changes via XML Notepad
I went to use the same config file on stage and decided to add a further property. I selected package configurations on stage and selected edit it the package configurations box, added the property and clicked finish. However it reset all the existing properties to the values that exist in the actual package so all the changes i made via XML notepad were overwritten.
Is there anyway you can get the Package configurations editor to retain the values from the .dtsconfig file rather than reading in the values from the package???
hi, all; i'd like to resolve this problem: We are Developing packages in Dev.Server; if we are Deploying our packages to Prod.Server; we need to modify the connection string and related properties of the connection manager. 1. How to modify the connection manager while deploying to production server? 2. can we create 2 dtsconfig file for a single package; is there any mechanism for instructing sequel server to use a particular dtsconfig file for our package.
i modified the config file in order to change the connection string. but its affecting only the config file not the package. while running the package its reffering the old connection string only.
hi i want to know what are the purposes of dtsconfig file for the package. once we deploy our package, if we want to modify the connection string for the new excel source or data source or destination, how to modify the connection details at client site, or in the server,
please help me to modify these connection details after or before deployment at server machine.
it would be really useful. it looks like the .dtsconfig file needs to be maintained on each install independently. This makes maintenance a nightmare. it would be a lot nicer if the .dtsconfig files were more like templates rather than hard coded values to specific system resources.
after i attached the file in package configurations .
i was unable to see the db connection in connection managers.
i tried to attach the .dtsconfig file in (New OLEDB connection manager) but after clicking the test connection " Testconnection failed because of an error in intializing provider.The connectionString property is not intialized"
I was requested to build and move and existing SSIS package from the developers local PC to the Dev server. Both are running SQL 2K5, I built the following command file so SQL Agent can run the import job.
REM *** Run for FileImport set FileImportLoadData=C:SSISConfigDEVFileImportLoadData.dtsConfig "C:Program FilesMicrosoft SQL Server90DTSBinndtexec" /f "C:SSISConfigFileImportFileImportLoadData.dtsx"
All files are in the proper place but when the jobs runs the following message shows up in the history;
Message Executed as user: LIGHTHOUSE1SQLEXEC. ...system32>set FileImportLoadData=C:SSISConfigDEVFileImportLoadData.dtsConfig C:WINDOWSsystem32>"C:Program FilesMicrosoft SQL Server90DTSBinndtexec" /f "C:SSISConfigDEVFileImportLoadData.dtsx"Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:54:00 AM Warning: 2008-05-08 08:54:01.77 Code: 0x80012014 Source: FileImportLoadData Description: The configuration file "FileImportLoadData.dtsConfig" cannot be found. Check the directory and file name. End Warning Warning: 2008-05-08 08:54:01.77 Code: 0x80012059 Source: FileImportLoadData Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. End Warning Progress: 2008-05-08 08:54:01.82 Source... Process Exit Code 0. The step succeeded.
My questions is how do I fix this and is this really and error?
I use an XML configuration file for my SSIS package, and I have "Enable package configurations" checked. When I build the package, however, there is no dtsConfig file in the output (set to in) or the Deployment folder, and the config file is not included in the SSISDeploymentManifest file. There are no build errors or warnings.
This was working fine the last time I worked on this package (~9 months ago), but not now. I have since installed SP2, but can't confirm if the problem is related to a "fix" in SP2.
When I edit package configuration files inside of Visual Studio, it puts all the XML on a single line. Does anyone know how to automatically spread the information onto multiple lines, to make comparing file differentials easier.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:01:27 PM Warning: 2007-11-30 22:01:29.52 Code: 0x80012012 Source: Package Description: The configuration file name "C:DevelopmentdtsConfig.xml" is not valid. Check the configuration file name. End Warning ...
But then it goes on to read values from my Production config file. How can I stop the annoying warning about my Development config file-path (which doesn't exist on the Production machine)
I am migrating from local to Dev,QA and Prod. I created a .dtsconfig file containing database connection strings to Dev database. What is the "location" on the Dev server where this .dtsconfig file nees to be deployed to??
I have setup a package configuration and it ran fine; however, I would like to be able to use the script task to change content of the dtsConfig file. For example, I can get the newServerName using the SQL Task, then how can I assign the newServerName value to the DataSource in the package configuration file? Is this possible?
I've deployed my ssis pkg to the server and created a sql job to run this pkg. So far, everything is fine. Today, I got a request to change some variables inside the package which is part of the .dtsconfig. I want to edit the deployed .dtsConfig but it won't allow me and always complained that this file has been opened by another program. I am sure i've closed my ssis designer and other notpad, why can't I edit and save .dtsconfig file?
I have a job/step that call a Fileprocessor.dtsx package with 4 different config files. I need to make a enhancement to the job to call the same package using a new (parent) package with multiple iterations based on some business condition.
I am going to modify the job/step to call ParentFileprocessor now with same all 4 config files which in-turn have a call to run Fileprocessor.dtsx; my problem is, how do I pass all 4 config files for child call? Using which task?
I m not allowed to modify Fileproessor.dtsx (to be child pkg) SQL2012, MSDB package deployment model.
Hi all, I need one more help! we can select the dtsconfig file with the Environment variable(indirect configuration); but i need to select the configuration file at runtime; i've to load the package from the server and apply local configuration file to the package and run in a web server;
Requirements; 1. i have packages with its xml configurations for connection strings alone! 2. i deployed it on the server 3. Trying to execute the packages in a web page onclick event;
We have ASP.net WebPage; in On_Click Event, i have this code;
Application ap = new Application(); Package pk = ap.LoadFromSqlServer("\PROJECT", "itsssqldb", "pmo_package_user", "password", null); pk.ImportConfigurationFile(@"Packagesdev_staging.dtsConfig"); pk.Execute(); here PROJECT is the Package name; i'm loading the package from the Common Sequel Server 2005; and applying the configuration file to that package and trying to execute this; but its not using the config file what i mentioned; its returning failure!!
Can you please help me to resolve this issue!,
(The intention of doing this job is to select the configuration file( prod, dev) at runtime)
I have an existing table called OrderHeader with a column called ID. The ID column is currently set as an INT. I want to run a script to modify that column to be an IDENTIY. I've tried the following:
ALTER TABLE OrderHeader ALTER COLUMN [ID] INT IDENTITY
I am trying to modify data in a tble using the Stored Procedure below. It doesnt work properly. I seem to only be getting one character. I think it has something to do with me using "nchar" with the variables. The value I am changing is actually a string.
I have a DTS package package1 which imports an excel sheet into my DataBase.ANd its saved and scheduled and running fine.Actually the excel sheet will be located daily @ c:ruceexceldata.But now we wanted to change the location of the file to c:ruce1ewexceldata
I have a scheduled DTS package which gets the data from a text file and imports into a sql table.The table will be droped and created whenever the DTS package runs.previusly the table has 6 rows and now i wanted to add a column row_id which is having an idenity seed.So how can I modify my package so that I can get done with my requirements.
I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it.
What I want to add is the following data.
For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate
Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members.
Any assistance is much appreciated!!
thanks once again mike123
SELECT CONVERT(varchar(10),referDate,112) AS referDate,
SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0', SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1', SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2', SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3', SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3', SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent',
count(*) as totalRefers, count(distinct(referUserID)) as totalUsers,
SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail', SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk', SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca', SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk', SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail', SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol', SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo',
SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual', SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto'
INSERT @Request SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL SELECT '2232362', 'Buck', 'SA055', 67, 195, Null
INSERT @Call SELECT 111, 1, 12123 UNION ALL SELECT 112, 1, 12123 UNION ALL SELECT 114, 2, 12123 UNION ALL SELECT 123, 2, 12123 UNION ALL SELECT 134, 3, 12123 UNION ALL SELECT 143, 1, 6532 UNION ALL SELECT 145, 1, 6532 UNION ALL SELECT 154, 2, 6532 UNION ALL SELECT 185, 2, 6532 UNION ALL SELECT 195, 3, 6532
INSERT @CallDetail SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL SELECT 6532, 1, '11/5/2007 12:12:34 PM' -- --select * from @Request Query written to achieve the requirement UPDATE r SET r.UniqueNo = p.RecID FROM @Request AS r INNER JOIN ( SELECT r.RequestID, ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID FROM @Request AS r INNER JOIN @Call AS c ON c.CallID = r.CallID INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID ) AS p ON p.RequestID = r.RequestID WHERE r.UniqueNo IS NULL
I need help with modifying this procedure to join JobTypeGallery, Remodel on JobTypeGallery.TypeID and Remodel.TypeID. I would like for it the procedure to not allow deleting a record from JobTypeGallery if there are any records in Remodel Table that is associated with JobTypeGallery. Can someone please help me modify this stored procedure? Create PROCEDURE [dbo].[spDeleteJobTypeGallery] @typeid int AS delete from jobTypeGallery where typeID = @typeid GO
I probably know that I don't want to do this, but I have an odd case.
I have a database with a bunch of stored procedures and functions. 128 of them reference a different database than the one they're in. For testing puposes, I need to temporarily re-point these functions and procedures to a different database.
I suspect the answer will be a resounding 'No', but I was wondering if it was possible to somehow run a query against syscomments where I could update all of those objects with the temporary database name rather than edit each and every one of them. Conceptually, it would just be an UPDATE using REPLACE. Pretty comfortable with that, but I'm always very reluctant to mess with stuff like that.
I'll probably still just do it through QA, but I was wondering if it's possible to do something like that and thought it might be an interesting topic for discussion.
I want to drop a table from a publication , so that i can copy some data from another server . After the copy , i want to add the article or the table back again to the publication without making any changes to the subscribers configuration .
Is it really possible on SQL 7.0 ? Right now it does not allow me to copy the data to the published table or even drop the article (table) and it says that the article is published for replication and cannot be dropped or modified . The table is configured for transactional replication .
I will have to drop the entire publication and create all the subcribers again .
I've learned to create DTS packages, saving them in a SQL database. But how can one modify a saved DTS package? And how does one delete a saved DTS package?
I have a trigger that emails me each time an order is modified by a manager. I can't recall how to find out which user is performing the modification. Is there a global variable such as @@username?
