Is It Possible To Read Configuration From An INI File ?
May 19, 2008
Hi guys,
That's the question. I'm sure I will be encourage to use xml or other configuration types rather than INI files. But INI file has the advantage of not embedding any object property path and can be used for both packages and other applications.
Imagine a large implementation with hundreds of packages and dozens of class libraries and applications which are configured from the same INI file.
Imagine also that the production environment must support both DTS 2000 and SSIS 2005 packages as it's impossible to migrate all at once.
I've read that SSIS would support INI configuration files:
In Package Configurations Kirk says:
"...The two types of package configurations that haven't been documented yet in books online is INI and SQL configurations. INI package configurations have basically been provided for backward compatibility..."
And in BOL, in the DTSConfigurationType Enumeration description we can find that the INIFile configuration Type is also mentioned.
Anyway, a possible workaround is building a custom task with an UI that allows to assign INI keys to object properties, but I wanted to know if it's possible to avoid this.
Thanks in advance
Leandro
View 1 Replies
ADVERTISEMENT
Nov 26, 2007
OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File
</a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
View 1 Replies
View Related
May 13, 2008
Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
//Some code to read file and write it into new file
return DTSExecResult.Success;
}
public const string Property_Task = "CustomErrorControl";
public const string Property_SourceConnection = "SourceConnection";
public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)
{
if (node.Name != Property_Task)
{
throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));
}
else
{
try
{
_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;
}
catch (Exception ex)
{
infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);
}
}
}
public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)
{
try
{
// // Create Task Element
XmlElement taskElement = doc.CreateElement("", Property_Task, "");
doc.AppendChild(taskElement);
// // Save source FileConnection
XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);
sourcefileAttribute.Value = _sourceConnectionId;
taskElement.Attributes.Append(sourcefileAttribute);
}
catch (Exception ex)
{
infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);
}
}
In UI Class there is OK Click event.
private void btnOK_Click(object sender, EventArgs e)
{
try
{
_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);
btnOK.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
#endregion
}
View 10 Replies
View Related
Jul 2, 2015
I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.
Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
View 3 Replies
View Related
Mar 24, 2008
Hi All,
I need to read a csv file, which is in remote server using SQl Bulk Insert Command.
Can I read a file Which is in remote server using BULK INSERT.
Thank you.......
View 1 Replies
View Related
Jan 25, 2007
Hi,
I cannot get the log file path read from the configuration.
If the path in the Connection Manager is invalid, package throws an error "SSIS logging provider has failed to open the log" instead of reading it from the config.file. What am I doing wrong?
Here is the portion of the config file. Everthing else is read from the config file correctly.
- <Configuration ConfiguredType="Property" Path="Package.Connections[SalesforceConnectLog].Properties[ConnectionString]" ValueType="String"> ConfiguredValue>D:APPSBACIARSalesforceConnectSalesforceConnect.log</ConfiguredValue>
</Configuration>
Appreciate a help.
Gulden
View 6 Replies
View Related
Jan 8, 2002
Anyone reading XML disk-files into SQL Server?
I have a process that I may want to do this with.
It would be a stored procedure that would read the XML attributes into 2 tables, the number of attributes could be 1-N, so I thought XML would be a good choice. Also, one of the attributes could be up to 4000 characters. I think this may limit our options, can 100-150 4000 character strings be passed in a standard call to a query/proc in SQL?
Currently the client application makes round-trip network calls to save upwards of 100 pairs of data. 1 header row, and many detail rows. All within a transaction.
I think If we move an XML file to the SQL box, then do all the import/save work on the "Server" side it would be much better. Cutting the transaction time down a lot by not doing so many round-trips at network speed...
Thoughts?
View 1 Replies
View Related
Jan 4, 2006
Is there a configuration file in SQL Server where I can find basicconfiguration of the server such as instance name, home folder, datafolder, etc.
View 1 Replies
View Related
Oct 1, 2006
I have a simple SSIS package (stored in the file system) that gets a file path from a configuration file. The configuration type is an indirect XML configuration file that uses an environment variable to store the location of the configuration file.
When I run the package using dtexecui, or just dtexec from a command line, the package successfully picks up the file path from the configuration file (for verification I am writing out the variable containing the path to the log file). However when I run the package from a SQL Agent job it appears that the configuration file is not being used (the path is set to the same dummy path that I used during development). I have tried running the job as both a CmdExec and an Integration Services job and both fail on the same thing (invalid file path).
Both the SQL Server Agent service and the Integration Services service use a domain account as their start up account. This domain account has been included in the local administrators group on the server (in case it was having trouble accessing the environment variables).
What is the problem here €“ surely changing the way in which the package runs should not affect the configuration file settings! Any help would be appreciated€¦
View 8 Replies
View Related
Aug 28, 2003
Any one can help me how to read a file(.txt) with in the store procedure?
View 2 Replies
View Related
Jan 4, 2006
hi,
i have a requirement in which i need to read from a .ini file in the stored procedure of sql server 2K.
is it possible? i tried searching on google but i cannot find anything that can help.
View 1 Replies
View Related
Feb 14, 2007
Can any one tell me how to read the XML file using sql syntax.
I hav also posted my full requirement why I need to read the XML file in Transact_Sql forum list.
View 1 Replies
View Related
Jun 6, 2007
Hi All
I am trying to load DBF files into SQL server within CLR (actually if just running the select statement outside, say within the SQLQuery window, i got the same result), but with the following error:
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "MYDBF" does not contain the table "T8866064". The table either does not exist or the current user does not have permissions on that table.
I created the linked server in this way
EXEC sp_addlinkedserver
@server = 'MYDBF',
@provider = 'VFPOLEDB',
@srvproduct = 'My Data',
@datasrc = 'c:data'
i did not create the login since my SQL instance is running under a superaccount with all privilege.
What frustrates me is that i can read most of the dbf files, but just a few of them is not readable.
Can anyone give me some hints on it?
by the way, i am using vfp9.0
thanks
michael
View 7 Replies
View Related
Apr 3, 2007
We are deploying our SSIS packages into different folders. For example:
Test1
est2
production
Test1 points to the Test1 database, etc.
So, I configured my SSIS package to use a database connection called dbMAIN.
I then setup the SSIS File Configuration (XML) so that "dbMain" points to Test1. This xml file, called Global.dtsConfig, sits in C: est1, the same place as my ssis.dtsx file.
I want to copy my dtsx file and my dtsconfig file into est2 and production. The problem is that the the location of dtsconfig is HARDCODED in the ssis package!
How do I dynamically change it?
I tried to hardcode the location to ".Global.dtsconfig", but that did not work! Please advise!
View 1 Replies
View Related
Mar 29, 2008
Hi,
I'm sitting down again to see if I can get configuration files to work. To test, I've set up a simple package that reads a flat file and writes to a table.
I've enabled configurations by clicking the checkbox in the configurations window. I am using an xml file on the local server, and there is an absolute path defined to the file. I also added a password to the xml file so the connection will work. To this point, everything works.
However, when I go into the configuration file and edit it to point to another server, it doesn't pick it up!
That is, when I run my package, it still writes the data to the old server. It's like it's completely ignoring the configuration file and just using the OLE DB connection manager instead.
However, if I delete the OLE DB conn mgr, the package doesn't work.
Please help... this is where I got stuck before.
Thank you
View 9 Replies
View Related
Jan 28, 2008
The configuration file name "C:......XMLSSIS2005_ConStr.dtsConfig" is not valid. Check the configuration file name.
The path is correct but we got this error. Please help us.
View 1 Replies
View Related
May 14, 2007
Hi,
Im loading the packages with a dutil-batch from the file-system into the sqlserver.
All the packages have xml-configuration-file.
for %%f in (*.dtsx) do dtutil /FILE %%f /COPY SQL;%%~nf /DestServer server /QUIET
When I try to run the packages in the sqlserver environment there is no configuration file found.
Is there a possibility to transfer also the link to the xml-configuration-file xml-configuration-file with dtutil?
Thanks Gerd
View 5 Replies
View Related
Jan 18, 2008
I created an SSIS package that is connecting to 2 SQL Databases, one connection is using windows auth and the other database connection is using SQL Auth to connect to the database. I setup a config file to use for my ID, servername, database, and password but when I run my SSIS package it fails stating'
"[Execute SQL Task] Error: Failed to acquire connection "dbConnection". Connection may not be configured correctly or you may not have the right permissions on this connection."
If I open up my connecting dbConnection in Connection Manager the username is filled it but the password is blank, if I enter in the password and click 'test connection' it passes. I have the ProtecionLevel = EncryptSensitiveWithUserKey but the password is still 'blanked out' So my question is
1) how can I use the config file to store my id and pwd for my db connection?
2) how can I get my package to read the config file to get the ID and password for my database.
3) is there another way to do this? Would a varaiable work for this, if so how do they work?
View 1 Replies
View Related
Oct 9, 2007
Hello,
I have a package where I store connection managers in a configuration file (XML file). I updated manually the XML file to add the passwords.
When I launch the command line generated by dtexecui, everything is good:
"dtexecui /FILE "E:mssql90DTSPackagesBVOMDWRefreshStage Refresh EUR 1.dtsx" /CONFIGFILE "E:mssql90DTSPackagesBVOMDWRefreshEnv_Prod.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW "
When I launch the command line with dtexec:
"dtexec /FILE "E:mssql90DTSPackagesBVOMDWRefreshStage Refresh EUR 1.dtsx" /CONFIGFILE "E:mssql90DTSPackagesBVOMDWRefreshEnv_Prod.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW ",
I have the familiar error: "Failed to decrypt protected XML node "DTSassword""
I have the package level set to "EncryptSensitiveWithUserKey", but I think I don't need to update it to "EncryptSensitiveWithPassword" because it works with dtexecui.
I don't understand... What I should do?
Thanks,
Guillaume
View 7 Replies
View Related
Mar 26, 2008
I have sucessfully merged and recreated my 1st DTS package to SSIS. now I am working on the configuration file and deploying it. I have a question about config files. I have set one up to use with my package pointing to proudction data sources. If I wanted to to set one up for a development source do I just create another config file for development. How do I tell it then which config file to use when I am testing?
IE we only have one server but will have a production and a test db. Right now just have prodcution. We have just merged over from 2000 to 2005. Just wanted to make sure I understand how to point it to development when I want to to this. '
Stacy
View 7 Replies
View Related
May 28, 2007
Hello All,
In my project, developers enable configuration on the packages they develop. This means that if a project has 100 packages, each one has its own configuration file.
If our primary database server changes, I have to go an update 100 configuration files.
This defeats the purpose of having a configuration file in the first place.
I want to have a configuration file which contains common settings for all the packages in the project.
At the same time, I should have the flexibility of having individual configuration files for the project.
Is this possible and how can I do this?
View 8 Replies
View Related
Jul 6, 2007
I have a package for which I have set ProtectionLevel to DontSaveSenstive.
I connect to a SQL Database which supports mixed authentication.
To connect to this server in a flexible way, I have enabled SSIS package configuration.
I have edited my dtsconfig file with notepad and put the right password there (I am able to use management studio to connect to that database using same password).
Now when I come back to BIDS and double click on my connection manager, it doesn't read the configuration from the config file. (it just has a blank password). So it seems that BIDS doesn't read the config file.
What can I do to make BIDS read the config file.
Also, is there a way to encrypt the password (without keys) in the configuration file?
regards,
Abhishek.
View 3 Replies
View Related
Aug 30, 2006
Hi,
I have about 40 packages in my job. One of the problems that I have encoutered is that I don't know how I could use same XML dtsconfig file for all of my files. Each SSIS calls different file.
The XML dtsconfig file is a replacment for my *.ini file in sql 2000. The dtsconfig file will hold paths to all different files. Each SSIS will take different file name at a runtime.
How can I have all the files paths in one dtsconfig file - and is it even possible to do that in SSIS?
Any help greatly appreciated....Thank you,
Maria
View 2 Replies
View Related
Jun 6, 2007
Need help reading a binary file see below for details...
I have uploaded a csv file into a sql table.
Now i want to extract the data and insert the data in the csv file into another sql table.
What commands can i use in sql to extract/ read the data ?
View 7 Replies
View Related
Nov 4, 2003
Hi, I wrote a report builder that creates a CSV file but I can't redirect to the file to view it. ERROR Cannot find the Server!. This works fine on the dev machine but deploy it and it does not.
Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("ConStr"))
Dim Cm As SqlCommand
Dim dr As SqlDataReader
Dim FileName As String = Guid.NewGuid.ToString & ".csv"
Dim FilePath As String = Server.MapPath("") & "CSVFiles" & FileName
Dim fs As FileStream = New FileStream(FilePath, FileMode.Create, FileAccess.Write)
Dim sw As StreamWriter = New StreamWriter(fs)
Dim Line As String
Dim lItem As System.Xml.XmlElement
Dim i As Int16
If ValidatePage() Then
Cm = New SqlCommand(BuildQuery, Conn)
Conn.Open()
dr = Cm.ExecuteReader()
Dim lXmlDoc As New System.Xml.XmlDocument()
lXmlDoc.LoadXml(txtHXml.InnerText)
For Each lItem In lXmlDoc.DocumentElement.SelectSingleNode("SELECT").ChildNodes
Select Case lItem.InnerText
Case "chkPRId"
Line &= "PR Id,"
Case "chkDateIssued"
Line &= "Date Issued,"
Case "chkOriginator"
Line &= "Originator,"
Case "chkBuyer"
Line &= "Buyer,"
Case "chkVendor"
Line &= "Vendor,"
Case "chkCostCode"
Line &= "Cost Code,"
Case "chkOracleRef"
Line &= "Oracle Reference,"
Case "chkTotal"
Line &= "Total,"
End Select
Next
Line = Line.Substring(0, Line.Length - 1)
sw.WriteLine(Line)
Line = ""
While dr.Read
For i = 0 To dr.FieldCount - 1
Line = Line & dr(i) & ","
Next
sw.WriteLine(Line)
Line = ""
End While
dr.Close()
Conn.Close()
sw.Close()
fs.Close()
Response.Redirect(FilePath)
End If
ResetPage()
PopulateListBoxFromXML()
View 1 Replies
View Related
Jan 17, 2002
Hi,
I received a SQL Server 6.5 database (.dat and .Log files). I'm currently running 7.0. Is there any way to read/import this data?
I tried to do this:
I have installed SQL Server 6.5. And After install, I have created a database with the same names (.dat an .log) and make it bigger than the first .dat and .log files.
Then, I have stoped the SQL server service, have deleted the .DAT and .LOG files that i have created, and have copied the first .DAT and .LOG in their place. I have Started the SQL server service and the database was beeing suspected. I stil unable to read data.
Can you help me?
View 1 Replies
View Related
Aug 16, 1999
Hi,
I need to read a binary file with extention *.dat like this:
tat.dat and import it in ms sql server 7.0.
Thanks for any help
Dr Bangaly Diané
View 2 Replies
View Related
Feb 16, 2008
hi
how can i read a txt file by using T-SQL commands.
thanx
View 12 Replies
View Related
Dec 14, 2001
How can I read one external file (*.txt,*.csv) through
stored procedure in MS Sql Server 2000 ?
On the database Interbase its very simple :
[CREATE TABLE table [EXTERNAL [FILE] ’ <filespec>’]
( <col_def> [, <col_def> | <tconstraint> …]);
EXTERNAL [FILE]“<filespec>
” Declares that data for the table under creation resides
in a table or file outside the database;
<filespec> is the complete file specification of the
external file or table]
Please advice
Thanks
Patricio
View 1 Replies
View Related
Mar 24, 2006
Hi,
Ik like to read data from a file (well formatted) into as MS-SQL database.
What is the best way to do this.
Jim
View 1 Replies
View Related
Apr 30, 2006
I am programming to read read sqlserver log file without shutdowning sqlserver. However it is said that the file is used by another process which I believe is sqlserver.
Is there any way to open log file without shutdowning server? I know that Log Explorer can read online log file. But I do not know the technology they are using.
View 3 Replies
View Related
Mar 31, 2004
I need to know how to read an xml file using SQL. (in SQLServer)
Then i will manipulate the data and update my tables.
May be i can put the xml file in the IIS root.
Then what is required for the rest..
Thanks in advance
Benny
View 4 Replies
View Related
Jun 2, 2004
I need to create/read files from t-sql? Does anyone know how can I do it?
Thanks
View 3 Replies
View Related
