OLEDB Initialization Props In A Configuration File?

Mar 5, 2008

Howdy Folks!

I'd like to put all the properties from the DBPROPSET_SSCE_DBINIT propset into a configuration file (XML probably) such that I can read in and dynamically adjust my SSCE connections. This way stuff like max database size and buffer size can be changed without a recompile.

My idea was to have an XML in the form:

<PropertySet Id = DBPROPSET_SSCE_DBINIT>
<Property Id = DBPROP_SSCE_AUTO_SHRINK_THRESHOLD>
<Options>DBPROPOPTIONS_REQUIRED</Options>
<Type>VT_I4</Type>
<Value>100</Value>
</Property>
<Property Id = DBPROP_SSCE_MAX_DATABASE_SIZE>
<Options>DBPROPOPTIONS_REQUIRED</Options>
<Type>VT_I4</Type>
<Value>1024</Value>
</Property>
.
.
.
</PropertySet>

Now my question is...can y'all think of any better way to convert these strings that I'll retrieve (e.g. "DBPROP_SSCE_MAX_DATABASE_SIZE") to their enum'd equivalents than to do the following:

if(PropertId == L"DBPROP_SSCE_AUTO_SHRINK_THRESHOLD") return DBPROP_SSCE_AUTO_SHRINK_THRESHOLD;
if(PropertId == L"DBPROP_SSCE_MAX_DATABASE_SIZE") return DBPROP_SSCE_MAX_DATABASE_SIZE;

It's seems entirely too unelegant and wasteful...but maybe I'm being too picky. It also has the disadvantage of needing to be updated if more properties are added with subsequent versions.

What do y'all think? Thanks as always!

View 5 Replies


ADVERTISEMENT

Reporting Services Configuration NO Initialization

Mar 29, 2007

I am installing sql server 2005 reporting services on server running sql server 2005. I get to the reporting services configuration page and configure...
report server virtual directory
report manager virtual directory
windows service identity
web service identity
database setup
encryption keys
when I get to Initialization the button is grey and inactive so I can't initialize reporting
services, Do I need to restart server?

View 1 Replies View Related

Instant File Initialization And DMZ

Jul 16, 2007



I have been trying to use Instant File Initialization feature on one of the servers which is a part of DMZ. One of the requirement, I understand, for that feature to work is to have SQL Server configured to use NT Authentication. My server is running in Mixed authentication, using local administrator account. I understand I should practice principle of least privilege, but question at hand is how I could use instant file initialization when SQL Server is not using Domain account.



Thanks,

Asaf

View 3 Replies View Related

Sybase Oledb Configuration

May 22, 2008

Hi,

i'm a little bit in trouble. before i have syabase 12.x and
successfully linked our sybase database to sqlserver 2k and
2k5. when our company upgraded sybase to 15.x. i followed
the document
http://www.sybase.com/content/1029118/1029118.doc in linking
sybase 12.5 to sqlserver. but when i install the sybase
15.x openclient i can find the SYBASE OLEDB CONFIGURATION.
where i can find it?? or sybase change the SYBASE OLEDB
CONFIGURATION????

please help...


tengtium

View 1 Replies View Related

Instant File Initialization And Non-service Accounts

Jan 6, 2015

My 3rd party backup product uses a non-service account login to perform tasks. If the account that it uses has been granted Perform Volume Maintenance tasks on the server, will it use IFI when restoring? Or do I need to have it use the service account login specifically to benefit from that?

View 2 Replies View Related

Changing Table Props On Replicated Tables

Dec 13, 2001

How can I change a field size that is set to numerical 9,2 to 9,3? I need to allow 3 decimal places.

Thank you,
Kameron

View 2 Replies View Related

OLEDB Provider=MSDAORA SQL Configuration TABLE In SSIS And Password Not Been Transfered (Project Real Drama)

Apr 1, 2008

dear list can anyone figure out a workaround as to why OLEDB Provider MSDORA cannot store passwords
I have all the info stored in a table (Project Real Best Practise)
The user id I have stored in string ConfiguredValue gets transfered to OLEDB Provider MSDORA named (SQL_REAL_Source_myoradb) but not the password
To workaround this bug only with MSDORA can aynonne sugest a setting I should use in package security
ie the default is EncryptSensitiveWithUserKey?

thanks Dave


CREATE TABLE [admin].[Configuration](
[ConfigurationFilter] [nvarchar](255) NOT NULL,
[ConfiguredValue] [nvarchar](255) NULL,
[PackagePath] [nvarchar](255) NOT NULL,
[ConfiguredValueType] [nvarchar](20) NOT NULL
) ON [PRIMARY]


ConfiguredValue = Provider=MSDAORA.1;Password=abc;User ID=myuserid;Data Source=myoradb;Persist Security Info=True


PackagePath = Package.Connections[SQL_REAL_Source_myoradb].ConnectionS

View 5 Replies View Related

Write To Csv File Oledb

Jul 27, 2007

How do I write the record set to a .csv file using oledb in vb.net? If anyone has sample code to write to .csv file, I greatly appreciated.

View 1 Replies View Related

Log File Path In Configuration File Does Not Work.

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

OLEDB Source To Flat File

Jun 7, 2007

Hi,



I'm using an OLEDBSource to select some data and then putting to in a Flat File destination.



However, when I look at the data in the OLEDBSource, they´re like this:

1. id

2. name

3. address



...but in the flatfile it comes out in the wrong order.



How can I fix this?



Thank you so much.

View 1 Replies View Related

How To Map Columns Between Flat File And Oledb Destination

Mar 11, 2008

i am trying to load almost 15 csv files to my oledb destination can i use for each container to map the source columns dynamically to destination table during data flow task


FLAT FILE SOURCE ------------------------------------ > OLEDB DESTINATION

1.Product.csv Product Table (Different mappings between columns)
2.Depot.csv
...
....


No.of columns also differ in csv files...


P.S. : Dont ask to me to do individual data flow task for each csv files.

View 4 Replies View Related

OLEDB To Flat File Destination - Comes Out In Wrong Order

Jun 8, 2007

Hello,



My OLE DB Source is getting data from the following column types:

ID varchar(50), Name varchar(100), Date datetime, Currency char(3), Cost numeric(30,10)



My OLE DB Source outputs my information in the following order when I click Preview:

ID Name Date Currency Cost



When I connect the OLE DB Sorce to a Flat File Destination, it comes out in the wrong order.When I examine the "line" between them (Data Flow Path Editor) I get:



Currency DT_STR Length: 3

ID DT_STR Lenght: 50

Name DT_STR Lenght: 100

Date DT_DBTIMESTAMP

Cost DT_NUMERIC



What is the easiest way for me to change this so the Flat File Destination will output my data in the same order as the OLE DB Source:

ID Name Date Currency Cost



Thank you very much!

View 6 Replies View Related

Decimal Formatting From OLEDB Source To Flat File

Feb 21, 2006

I have a
table with column (call it Rate) defined as decimal(18,4). This column
can have any positive decimal value. When the value is less than 1
(e.g. 0.1234) strange things are happening in SSIS.

To set the scenario ...

If I do a select in Management Studio, the value is correctly displayed as 0.1234

If I do the same select from the command line via OSQL, I get .1234

If I do SELECT CONVERT(char(6), Rate) from OSQL, I get 0.1234 (which is what I want)

In SSIS I have an OLE DB Source which defines the SELECT statement to use, and that maps straight to a Flat File Destination.

The
default column type in the flat file connection manager is DT_NUMERIC,
Precision 18, Scale 4. I Set InputColumnWidth to 6 as that's how I need
it in the flat file. The value appears in the file as .1234

I
change the SELECT in the OLEDB Source to apply the CONVERT(char(6),
Rate), and change the column type in the connection manager to DT_STR
length 6. Output is *still* .1234

How can I get the output column in my file to show 0.1234 ??

Greg.

View 3 Replies View Related

Merge Data From Oledb Source And Flat File

Feb 26, 2008



Hi All,
In one of my SSIS Interface I have to Merge data from a Oledb source and a Flat file source.But after I read from the flat file I have do a basic validation of the file for the length of header,detail and trailer records and then process further.The above Validation I am doing within Script Component.If the validation fails the flow should pass out of the DataFlowTask without Initailsing the Oledb source.

But the problem is i am not able to connect anything to the Oledb source,i.e Oledb source is not taking any incoming Pointers.
Earlier I had done the same Validation in Control Flow Task,but then the interface was reading the same file twice,once in the Control Flow Task and then again in the DataFlowTask.Which i should avoid now.

I hope many of you could have come across such a problem.
Any help on this will be appreciated.

cheers
Srikanth Katte

View 5 Replies View Related

Execute A Flatfile Source To Oledb Destination For Each File In A Folder.

Jan 24, 2007

Hello, I wanted to do the following.

Copy a full directory from source to destination (Done)

then for each file on the destination directory,it must process that file and insert rows on the table.

So I created a foreach loop, and created a varriable aclled CURRENTFILENAME, and assigned it into the foreachloop to index 0.



Inside the foreachloop I created a dataflow task, in the dataflow task I dragged a flat file source and an oledb destination, but I noticed that the flat file source requires flat file manager, and the flat file manager requires a unique FILE NAME. I cant put this c:copia*.txt.

I took a loot at flat file source properties and it has associated the flat file manager, but I can not assign the filename to the variable from the foreach.



Any ideas please



View 5 Replies View Related

Configuration File

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

Configuration File Not Always Used

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

Integration Services :: SSIS OLEDB Data Source - Flat File Generation

Apr 20, 2015

I am working to archive some old data from a data warehouse using SQL server and SSIS.  The data will be read and denormalized, then shipped out to a delimited text file.

The rowcount of the incoming data is significant, call it 10M+ rows per unit of work (one text file).

There are development advantages of using a stored proc for the data source - mainly ease of changing the denormalization logic as required.  Wondering if there are performance advantages of an embeded query for the data source instead?

It was mentioned by one developer that when using a stored procedure, the output stream from the proc and subsequent SSIS steps cannot start until the full procedure processing is complete; i.e. the proc churns out its' result set in one big chunk. 

He hinted that an embedded query does not have this same effect, but I am not sure that is accurate.

View 4 Replies View Related

[help] SSIS File Configuration (XML)

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

Configuration File Issue

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

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 View Related

The Configuration File Name Is Not Valid

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

Dtutil And Xml Configuration File

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

Configuration File Issues

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

Dtexec Vs Configuration File

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

Configuration File Question

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

SSIS Configuration File

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

Configuration File Woes!!

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

Package Configuration With XML File

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

Error When An OLEDB Source Points To An OLEDB Destination.

Oct 10, 2006

Hi all,

I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.

But i got the below error:

Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.

Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow TaSK: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)



View 5 Replies View Related

Which Configuration File Can Disable Traces

Nov 29, 2011

When I restart the server that hosts the database engine SQL Server 2005 Standard Edition SP4, the trace gets up mytrace-5.trc with a size of 100 MB and id is 2, leaving the hard disk space, then the SQL Server stops execution of the trace due to lack of space. I do not know how to erase the trace, because I do not know where is it.

The archive log looks like : SQL Trace ID 2 was started by login "sa"

Trace ID '2' was stopped because of an error. Cause: 0x80070070(There is not enough space on the disk.). Restart the trace after correcting the problem.

I only know how delete a trace, knowing id.

EXEC sp_trace_setstatus 2, 0
EXEC sp_trace_setstatus 2, 2

Which configuration file I can disable all SQL Server traces. If I run select * from sys.traces, I just get the default trace:

id = 1
status = 1
path= D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGlog_32736.trc
max_size = 20
stop_time = NULL
max_files = 5
is_rowset = 0
is_rollover = 1
is_shutdown = 0

[code]....

View 4 Replies View Related

Package Not Finding Xml Configuration File

Nov 26, 2007

Hi,

I'm having difficulty configuring a package to look for an xml configuration file that contains values for source/destination connection strings.

The goal is to be able to have a package and its configuration file on one file system and to be able to copy/move the file dtsx and dtsx.config between build/staging/production servers while only having to set the xml value for the source/destination configurations.


In the package, I've created an xml configuration file called zzz.config. I took the absolute path out of the location of the xml file so that I have
xml location = zzz.config
instead of
xml location = e:ssiszzz.config
I'm assuming that when it gets to the point of validating the location of that file, it starts by looking in the current directory where the package is found.

All is well as long as I execute the package through the execute package utility by double clicking on the package file.

The problem lies when configuring a job in the sql server agent, it doesn't seem to pick up the xml configuration values because its pointing to the old server and not the current server stored in the xml file. I'm assuming its because the current directory is not the package directory at this point.


How can I get around this? Do I have to move the xml config file (zzz.config) to another directory where the agent will look for?

I know I could configure this as an environment variable but this contradicts the point of having movable packages without any extra tweaks.

Any help would be appreciated.

Thanks,

Robin

View 4 Replies View Related

Configuration File Reused By Different Packages

Jun 1, 2006

I have two packages share same configuration file. There are 4 connections in the first package and 2 connections in the second. Configuration file contains properties for 4 connections. First package opens in VS designer with no error. The problem with second package.
I get error that connection exist in the configuration but not in the package
and
"Could not copy file " Master_Configuration.dtsConfig" to the deployment utility output directory. Master_Configuration.dtsConfig' already exists."
Any help?

View 3 Replies View Related







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