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.
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.
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.
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?
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)
Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so created a new database with text indexing enabled and the following table create table support (problemId VARCHAR(50) NOT NULL PRIMARY KEY, problemTitle varchar(50) NOT NULL, problemBody text NOT NULL, linkOne varchar(50), linkTwo varchar(50), linkThree varchar(50), linkFour varchar(50), ftid int NOT NULL) next create fulltext catalog remoteSupportCatalog create unique index ui_remotesupport ON support (ftid) then create fulltext index on support(problemBody)key index PK__support__7C8480AE on remoteSupportCatalog -------- I then populated some rows and issues a quesry Select * from support where freetext(problemBody, 'test database') it works pulls back all the data I expected it to pull back
In my asp page I created a database connection with the folling select command <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>" SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @srchBox)">
created the search parameter<SelectParameters> <asp:ControlParameter ControlID="srchBox" PropertyName="Text" Type="String" Name="srchBox" /> //this is a text box that is searchable with a button </SelectParameters> and it doesnt give me back an error or data it does nothing. What am I missing?????
I hate making complecated queries....but this doesn't seem too hard and I cannot figure out where my error is. The message is "Incorrect syntax near the keyword 'FROM'."
The query is: SELECT tblUsers.lUserID GROUP BY tblUsers.lUserID HAVING Count(tblDLHistory.lDownloadID) = 0 FROM tblUsers LEFT JOIN tblDLHistory ON tblUsers.lUserID = tblDLHistory.lUserID WHERE tblDLHistory.lVersionID = 5
I need to search the database and pull up all customers who have a 'device' and their email address. I have watered down my select statement, but the following is the basics of it. I just cannot figure out how to also append the email. I have tried many different attempts and have come up dry.
Select a.company From dbo.contact1 as a, ( Select DISTINCT accountno From dbo.contsupp Where contact LIKE 'Product Inventory' AND contsupref LIKE '%device%' ) as b Where a.accountno = b.accountno
Below are some sample databases to get an idea of some possibilities. There will not always be an entry in dbo.contsupp for an email address. There will not always be an entry in dbo.contsupp for a device.
I have a small table to manage orders in my company. When a new order is entered, the script makes use of the last row of the table to find out the last order, increments it and creates the new order number. The problem is, a few days ago the last row got stuck. New insertions to the table all got the same order number and are placed above the last row. Anybody has any idea what's going on?
Heres is a shorten downed example of what I am trying to achieve. I have four tables. The first being OrderHeader, the second OrderLines, the third StockCategory. The OrderHeader table contains basic order details, which in turn is linked to the OrderLines table which show if the order in the OrderHeader has a single or multiple order lines. The StockCategory table shows what stock group the item in each order line is associated to.
Here is an example of the tables in a shorten downed version (in both data and fields):
OrderHeader: Sales Order Ref, Order Date, 1, 01/05/2008 2, 01/05/2008 3, 02/05/2008 4, 02/05/2008
OrderLines: Sales Order Ref, Part Number 1, 222 1, 234 1, 333 2, 222 2, 555 2, 444 3, 333 3, 111 4, 222
StockCategory
Stock Category, Part Number, A, 222 B, 234 C, 333 D, 444 E, 111
The thing I am trying to do is assign each of the orders to a Stock Group which I can do. The thing that has baffled me is if an two of the order lines in one order are assigned to different stock groups. If this occurs I want to assign the order to the order to the stock group with the highest priority (1 being highest, 3 being lowest) for example if one order line in the order was assigned to Berr (priority 1) and the other to DFID (priority 3) the order would be assigned to Berr.
SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = @Period SELECT @EndDate = [EndDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT COUNT(oh.[Sales Order Reference]) FROM dbo.OrderHeaders AS oh LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference] LEFT JOIN dbo.StockCategories AS sc ON ol.[Part Number] = sc.[Part Number] WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate AND sc.[Stock Group] IN (SELECT sg.[Stock Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'Berr') AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference] FROM dbo.CancelledOrderLines AS col WHERE col.[Part Number] = ol.[Part Number])
Been spinning on this for whole weekend I can't seem to get what you I want. I have the following xml result from my query. As you notice one of the child elements has the tag identifier VJobs, how can I make it so it says 'task' instead?
Here is the query, which details jobs to be done on different equipments SELECT EquipmentID + 1 as id, EquipmentDescr as [name], '#99ccff' AS color, 'true' AS [expand], (SELECT JobID + 2AS id, 'Layout#' AS [name], '#99ccff' AS color, (SELECT [taskproperty-id] AS [taskproperty-id], [value] AS [value] FROM dbo.JobDetails customproperty WHERE customproperty.JobID = VJobs.JobID FOR XML AUTO, TYPE) FROM VJobs WHERE VJobs.EquipmentID = task.EquipmentID FOR XML AUTO, TYPE) FROM VEquipments task ORDER BY EquipmentDescr FOR XML AUTO, TYPE
Hello, Well actually I am beginner to ASP.Net, I am working on Microsoft Virtual PC with VS 2005 and SQL Server 2005 installed. Now when Create web application in Studio all works fine with databases but when i try to write some code for accessing SQL Databases I keep getting this error PLZ heLP.Following is my code<% @ Import Namespace="System.Data" %><% @ Import Namespace="System.Data.SqlClient" %><html><script language="C#" runat="server" Debug="false" >SqlConnection sqlcon;protected void Page_Load(Object Src, EventArgs e){ sqlcon=new SqlConnection("Data Source=VS02005;Initial Catalog=Gaurav;Integrated Security=True"); SqlDataAdapter sqlcom = new SqlDataAdapter("select * from Employee", sqlcon); DataSet ds = new DataSet(); sqlcom.Fill(ds,"Employee"); DataGrid1.DataSource = ds.Tables["Employee"].DefaultView; DataGrid1.DataBind();}</script><form runtat="server"> <asp:datagrid id="DataGrid1" runat="server" /></form></html>This is the error i am gettingServer Error in '/' Application.--------------------------------------------------------------------------------SELECT permission denied on object 'Employee', database 'Gaurav', schema 'dbo'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: SELECT permission denied on object 'Employee', database 'Gaurav', schema 'dbo'. I am stuck dont know what to do, I hve checked all permissions for user gaurav, I also gave administrator rights to gaurav but nothing is working PLEASE help me.
Sorry for all the code below. I am realizing that my DB design is bad but I already have 7 pages built around it that work fine...until now, so I would really like to not change the DB if possible. My table has 22 columns: iID which is the identity colum. Then there is iAsmtID which is the assessment ID. Lastly there are 20 colums- q1 through q20, each of which will have a 1, 2, or 3, depending on the radio buttons the user clicked. Nows my problem. I have to find a percent for the assessment. It works like this. 3s are NA so we are not worried about them now. I need to find the number of ones and the number of twos for each assessment ID. Then add those together and divide by the number of ones. How can I find the number of the ones and twos. I have below but its not working. Says there is incorrect syntax at the ',' which is a different color below. Any and all help appreciated.'Open connectionset conn=Server.CreateObject("ADODB.Connection")conn.open My_Connset rs = Server.CreateObject("ADODB.Recordset")str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color:#FF0000]CountOfOnes,SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE iAsmtID="&iAsmtIDresponse.Write(str)rs.open str, connif rs.eof = true then ' response.Write("<h2>No count done</h3>") response.End()else'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos")end ifrs.closeset rs = nothingconn.close'set conn = nothing
I have a job that extracts data from 10 different tables to 10 diffrenet tables of MS-Access (.mdb file). The job ran okay since last one month but yesterday it ran and did not stop at all .Status is shown as "Executing" .. I tried to stop it but it is not stopping . I tried to kill associated process (spid) but it allows me to kill spid but it shows there running.
Any idea why it is happening ?
If any body ahs any solution you can call me at 1 860 520 7454.
I have a table with 4 relevant fields (blank lines added for clarity). State, City, Name, Primary_Contact IL, Springfield, Bill, n IL, Springfield, Frank, n IL, Springfield, Larry, n
IL, Bloomington, Steve, n IL, Bloomington, Chris, y
IL, Chicago, Betty, n IL, Chicago, Linda, n IL, Chicago, Sue, n
I need a query to return the state and cities that don't have a Primary_Contact='y' So the results would be: IL, Springfield IL, Chicago
I need the results from the following query to be with the results of the second query. Any ideas?
Code:
SELECT PR.WBS1, PR.WBS2, PR.WBS3, PR.Fee, PR.ConsultFee, PR.ReimbAllow, PR.LongName, PR.Name, CL.Name AS CLIENTNAME, CLAddress.Address2 AS CLIENTADDRESS2, CLAddress.Address3 AS CLIENTADDRESS3, CLAddress.Address4 AS CLIENTADDRESS4, CFGMain.FirmName, CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, CFGMain.Address4, Contacts.FirstName + ' ' + Contacts.LastName AS CONTACT, LB.AmtBud, LB.BillBud
FROM PR LEFT OUTER JOIN Contacts ON PR.ContactID = Contacts.ContactID LEFT OUTER JOIN CL ON CL.ClientID = PR.ClientID LEFT OUTER JOIN CLAddress ON CL.ClientID = CLAddress.ClientID LEFT OUTER JOIN LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 CROSS JOIN CFGMain Where pr.wbs1 = '001-298' and pr.wbs3 != 'zzz'
and
Code:
SELECT * FROM LD WHERE (BilledPeriod = '200408') AND (WBS1 = '001-298')
I have a sql problem I'm trying to solve. I'm selecting from a table based on a foreign key, and the select returns 2 rows. The table has a column called type, and each row for the foreign key has a different type value. Hopefully the example below can help to explain:
Case 1:
PK | FK | Type | Text -------------------------- 1 | 226 | 0 | some text goes here 2 | 226 | 1 | NULL
Case 2:
PK | FK | Type | Text -------------------------- 3 | 334 | 0 | some text goes here 4 | 334 | 1 | actual text I want to select is in this cell
I'm trying to create a select statement to grab the text for the foreign key I'm looking up. In case 2, I want the text where type=1 but in case 1 I want the text where type=0.
I had started writing it as
select text from table where fk=334 and ( (type=4 and text is not null) or type=0 )
but this returns both rows. What I what is something that I think is more akin to
case a || case b
expression in programming - if case a evaluates as true, use that, otherwise evaluate case b and use if true, otherwise return false.
I hope you can understand what I'm trying to get and any suggestions would be much appreciated.
