Using Configurations Class Within Script Task
Oct 4, 2006How do I access the configurations collection of a package within a Script Task component? Is there any sample code anywhere?
Thanks!
ADVERTISEMENT
Access To TaskHost From Derived Task Class.
Jan 2, 2007Hi,
for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).
For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?
Any hints?
Thanks.
Thorsten
OleDB Connection Class In Custom Task?
Jun 9, 2006I have OLE DB Connections set up in my connection manager (Native OLE DBMicrosoft OLE DB Provider for SQL Server). I would like to reference and query these connections from a custom task, written in C#. I currently reference it as follows:
using System.Data.OleDb;
...................................
OleDbConnection connection = (OleDbConnection) connections["MyConnection"].AcquireConnection(null);
What may be obvious to some (though wasn't to me, as I am new at this), when I run the task, I get an error saying that I cannot make this cast. After perusing the boards, I understand that this is because I am not making a cast to the right connection type. Well, that is where I am lost. What connection type (and corresponding library) do I need to reference? I want to continue to use the "Native OLE DB..." connection.
Thanks!
SqlDatareader Belongs To Which Class Is It Sealed Or Static Class?
Feb 21, 2008Hai Guys,
I have a doubt Regarding SqlDataReader
i can able to create object to Sqlconnection,Sqlcomand etc...
but i am unable to create object for SqlDataReader ?
Logically i understand that SqldataReader a way of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited.
sqlDatareader belongs to which class is it sealed or static class?
can we create own class like SqldataReader .......
Reply Me ...... if any one know the answer..............
Can SqlDataSource Class Inherite To Another Class
Feb 1, 2008Does any one inherit SqlDatasource class?
I treid it as :
public class MYDataSource : System.Web.UI.WebControls.SqlDataSource
{public MYDataSource(){
}
}
Debugger dont give any error or warning when i buld project. But when i use it in a page Visual studio is crashed.
Can any one help me ?
Send Mail Task Problem Using A Combination Of ForEach Loop, Recordset Destination, Execute SQL Task And Script Task
Jun 21, 2007OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
Try
ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
thanks
John
Configurations
Jun 15, 2007Hello, I had a question I thought someone might be able to answer before I test my theory.
If I run an outer package through SQL Server Agent and specify it to use a configuration file, will the children packages it call also use the configuration file? My instinct is no it will not.
If this is in fact the case, would it be considered best practice to just specify the configuration file for each package through an indirect environment variable? Is it possible to set an environment variable on the set values tab within sql server agent?
Then my only concern is how would developers test packages on their machines, should they also set up the same environment variable on their machine?
Any thoughts?
Configurations
Apr 24, 2006I have read many posts about configurations. However, in practice, I cannot solve a problem that is bothering me. I have connection managers (that manage SQL Server 2005 connections) configured in a local package. I have ProtectionLevel = 1 (By the way, where do you state that you want it "DontSaveSensitive", etc?). When I deploy the package to another computer (using the deployment utility) though, I get the error "The AcquireConnection method call to the connection manager ... failed". Could someone tell me, very explicitly, how can I use configurations to solve this problem? Or are there other ways to solve the problem? The problem, of course, is that the connection managers' passwords aren't being migrated from a computer o another. Thanks a lot.
Pedro Martins
Transaction Log Configurations
Apr 4, 2004Hello, everyone:
How to configure transaction log file size, transactional backup interval and log shipping intereval, exactly? Someone told me it depends on the transaction speed. Is it correct? I have an average transaction speed about 10/min., one day, on SQL 2K/W2K. How should set above issues?
Thanks a lot.
ZYT
Configurations For Partitions.
Aug 23, 2007Hi experts,
In SQL Server 2005 database we have partitioned a very big table into 30 partitions each holding few million of records.
Im just curious to know whether there are some configuration related to processors or system hardware in order to benefit from partitioning ? (Ex : If we have multiple processors Whether they need be configured to do a parallel processing ? )
Any real time experience (other than referring links) would be really helpful for me.
Thanks in advance,
Hariarul
Package Configurations ...
Feb 6, 2008I have a question about Package Configurations. I had developed a prototype that has connections defined to 2 DEV SQL Servers and runs fine in DEV. I created a Configuration XML file and changed the XML file settings to point to our 2 TEST SQL Servers. I ran this package without issues. However, I was expecting the 2 SQL Server OLE DB connections within BIDS to point to the TEST Server after the package ran. I still was pointing to DEV. Am I missing something here? Thanks in advance.
View 5 Replies View RelatedPackage Configurations
Oct 19, 2006Hi,
I have a package configuration for my SSIS packages to allow me to dynamically change the connection string of the connections in my packages. This was done so that when I deploy my packages to my development or test server, the packages would use either the development or test server name in the connection string. I have set the configuration up to use an environment variable to store the location of the config file (an xml config file). The package however does not seem to be using the environment variable though. If I change the location of the config file in the variable's value, it still points to the old location. Can someone please help.
Thanks
Package Configurations
Feb 12, 2007Hi,
i keep struggling with dtsconfig.
i have 4 packages which all have the same datasource based on the global-datasource.
is there a way i can save that global-datasource, instead of configuring all 4 packages to use a config-file?
Package Configurations
Aug 5, 2007Before I started using package configurations, I made several (wrong) assumptions. My primary use for configurations are to move packages between environments (dev, test, prod), so my config files only contain connection information.
My first assumption was I could create 3 different configuration files (XML) with the connection string properties for each environment. Because I use shared data source connections in my packages, these are the only properties I should have to store within my configuration file. Then I use indirect configurations, which I had a system environment variable set up on each server, called SSISConfigPath. On all servers, I have it pointing to a shared folder on our network so us developers can update the configuration files at will, if needed.
Now, in theory if I deploy all of my packages to the SQL Server MSDB on each of the servers, and schedule a job on each server, everything should work, right? Wrong.
First of all, we had to restart the SQL Agent service in order for the package to recognize there is a new system variable (similar to the Indirect Configuration "gotcha" that Jamie blogged about, but in this case, it wasn't a cmd window, but a windows service).
Next, an SSIS package doesn't first read the configuration file, but it tries to validate the connections that were originally stored in the package to begin with before replacing them. This is a problem when the environment you develop on is your local PC (laptop for example), and deploy to production, the SSIS package tries to connect to your laptop PC first, throwing an error because it cannot find it. If it can find your laptop, it succeeds, and only then switches the connection info with what is in the configuration file.
Can this "validation" step be disabled? It seems changing the "Delay Validation" = true on the package doesn't work for connections.
And when you open BIDs to run a package, the "Syncronize Connection Strings" dialog always appears. When you say OK, it doesn't stick and always asks you each time you open the file.
Please let me know what I should be doing, because this doesn't seem to work as flawlessly as I thought it should.
-Kory
SQL Server Vs. XML Configurations
Jan 5, 2007I have been experimenting with both XML and SQL Server Configurations. Management would really like to go with the SQL Server Configurations, but I seem to be having problems with them.
The main goal is to be able to create a configuration for each connection and have every package refer to its specific configuration, therefore in the future we could change say our SA password and only have to update this in a few places rather than for every package.
In order to experiment with the problem I am having I developed 2 identical packages, except one uses SQL Server Configurations and the other uses XML. The configurations save the exact same properties. With the XML package I can go into the configuration file and change say the password and initial catalogue properties and have these changes go into effect. With the SQL Server package these changes do not seem to take place. So I guess what seems to be happening is with the XML package the connectionstring seems to be generated with the other properties that have been saved in the configuration, but in the SQL Server package none of this seems to take place and the connectionstring just stays exactly as it is saved in the configuration file.
I was wondering if I just seem to be missing something or if anybody else has experienced this problem and found a solution.
Any help is much appreciated,
Sam
SQL Package Configurations
Nov 15, 2006Hi,
Within the SSIS package i am currently developing I have a number of variables relating to folder locations. I have added these to a SQL configuration package as i'd like to be able to change these prior to the SSIS package being executed. I plan to launch the package by calling a SQL Agent job from a web page.
On this web page i'd like to be able to change the values in the SQL database so that the package runs with User specified folder locations.
If anyone has done this before i'd appreciate if you could suggest how best to accomplish this. At present i'm thinking of just interogating the created database but as there are no keys etc set up this may not make updating the table easy.
Many thanks,
Grant
Package Configurations
Mar 25, 2008I have an SSIS Package that utilizes two Configurations at design time...
1. Environment Variable Configuration sets the root folder variable and subsequently several other variables within the package using expressions (adapted from Jamie Thompson's template package - thanks Jamie)
2. Indirect XML Configuration to set the connection string to the appropriate db.
I've got 3 environments in play.
1. My workstation, using BIDS to design / debug the package. For my PC, the Indirect Config sets the connection to our test server/database.
2. A test server. For the test server, the Indirect Config on the test server sets the connection string to the test database on that server.
3. A production server. For the production server, the Indirect config on the prod server sets the connection string to the prod database on that server.
Problem...
Running the package in BIDS correctly runs against the test db, no problems.
Running the package on the test server in SSMS (either in the Execute Package Utility or using the Agent) without specifying any Configurations on the Configuration tabs, runs fine against the test db.
Running the package on the prod server in SSMS (again, in the Execute Package Utility or using the Agent) without specifying any Configurations on the Configurations tabs runs the package against the test db.
Is this because I'm not specifying the Configuration I have stored on the production server to use the connection string for production?
Curiously, the Indirect Configuration I have setup to read an environment variable to set the root folder (for logging, etc) works fine without having to specify any configurations on the configuration tabs in SSMS. In fact, I don't think you can specify environment variable configurations on these tabs - they open a Browse window to search the filesystem for a config file.
Any clarification anyone can give me regarding these Configuration tabs, indirect configurations, and what their purpose is would be great. I've spent days researching, reading through the two books I have, etc, and there's not much out there on this.
Thanks!
Jamie Pick (not Thompson)
Pls Help With Package Configurations
Nov 7, 2007
Hi
I created an xml configuration file and specified all the details,i have saved the package with protection level as "dont save sensitive".We are running the package thru autosys.But the package is repeatedly failing,not sure whats wrong with this one.
Pls can someone help me with this??
Thanks in Advance.
SVGP.
Question About Configurations
Jun 29, 2006I've been reading up on package configurations, but I'm not quite sure how to use them correctly. If I am developing a package, I want to connect to a testing server. Then, when it is working correctly, I want to connect to a production server. Are configurations a helpful way of doing this, or would it only be helpful if in the future, I need to make the packages reference a different server?
View 4 Replies View RelatedDeployment - Database Configurations
Nov 14, 2007Greetings,I've been looking around all morning and can't seem to find a good answer on this so I thought I'd post a message here.I'm using the recommended practice of keeping the ASPNETDB.MDF data separate from my site data. (I have my site data located in separate databases but still keep them in the App_Data directory.)I'm using SQL Server 2005 Express on a Server 2003 box and I've implemented scheduled backups on the databases in the Sql Server.The questions I'm having are:1. Why isn't the ASPNETDB.MDF database getting attached to the server? Is it using User Instances?2. If it is using User Instance then isn't this degrading performance? If so, how to force it to not use User Instance?3. If User Instance is how it is supposed to work then how are we supposed to do a full backup of the ASPNETDB.MDF database if it isn't attached in the server? (xcopy doesn't seem to work on User Instances because of the dreaded "is being used by another process" error.)Any recommendations, advice, links, or answers to my questions would be greatly appreciated.
View 1 Replies View RelatedClient Configurations----immediately
Aug 2, 2000Hi ,
I am configuring odbc connection at my users place, i tried to change his client configuration , but it is not going in. What was the problem why it is not going in.pls suggest me.
i am having sql7.
--chowdary
Security Configurations Report
Mar 6, 2008Hi all,
I would like to retrive system report that provides the following security configurations for MS SQL
1. Minimum Password Length
2. Initial log-on uses a one time password
3. Password composition (e.g., alpha/numeric characters, not words in
dictionary)
4. Frequency of forced password changes (password expiration/aging)
5. The number of unsuccessful log-on attempts allowed before lockout
6. Ability of users to assign their own passwords
7. Number of passwords that must be used prior to using a password again
(password history)
8. Idle session time-out
9. Logging of unsuccessful login attempts
do i have to script it or is there any alternate option
thanx in advance
Database File Configurations
Apr 3, 2012We have been given a new Database server with SAN drives attached for storage. We will see the SAN as 4 separate drives on the server (D,E,F,G). Drives D and E will be a 7 disk array using RAID 5. F and G will be s sperate 7 disk array using RAID 5 also.how best to configure our databases on these drives. I was thinking of splitting the systems db's, user db's, indexes and logs like this:
D: System DBs
E: User DBs
F: Indexes
G: Logs
I wanted to keep data and indexes seperate as well as data and logs seperate. I'm not sure if it makes sense to keep the system dbs on there own filegroup or not.
SSIS And Dynamic Configurations
Oct 9, 2007I want to see if anyone in this community has come across this and has found a solution.
we have 3 different SQL Servers because we are still porting over SQL 2k databases 2k5. We have multiple databases of course. We would like to create some kind of template that would create the connection manager based off of the information in our XML configuration file. We are thinking we would have 2 files for this. One would house the actual server information but omit the database name and username/password. The second will be a list of all the databases with their usernames/passwords for each one.
If we put all of these in the configuration files and do not have a connection for each one, SSIS will throw the error stating it is missing connection managers for those items. We would like to see about maybe specifying the database name and have the connection manager dynamically pull the database specific information into the package. This would also need to be able to handle more then 1 database should there be the need.
I am new to SSIS. It seems pretty nice and extensible but my learning curve on it is horrible as I never programmed DTS packages before.
I hope I am making sense here. Thanks for any help offered!
- Taco
Package Configurations Problem
Aug 30, 2006Hi all,
I have just set up my first deployment utility for the Integration services package i've been building. I seem to be getting problems with the package Configurations. I added an XML config file so that i could change the values of my SQL connection manager at deployment time. This was so that i could deploy it on both a test environment and the live environment. Any other variables can be changed in code by the calling application. As soon as i added the options for the sql connection manager and enabled package configuration i got errors when running the application:
[Execute SQL Task] Error: Failed to acquire connection "InternalProductionData Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection.
This is before i even deploy the project. If i disable the package configurations everything works as expected. Can anyone help suggest why this might not be working.
Many thanks in advance.
Grant
Package Configurations Stored In SQL
Jan 3, 2008I'm relatively new to SSIS, but I have read all the info/threads on Package Config. and I'm really confused.
Our production env. is clustered, and we are running multiple sql instances, so the sys admin does not want me to use Env. Variables or XML files for the package configurations (it's too hard for him to maintain the info across multiple instances, etc). So, I am storing the package configurations in SQL.
Just as a simple test, I used the wizard to create the 1 package configuration for production. The configured value as a complete connection string
When I run the package, it completely ignores the package configuration. How do I change the connections on my tasks to read from the package configuration instead of the connection manager they are currently associated with?
TIA,
Mary Jo
Dynamic SSIS Configurations
Aug 23, 2007Hey there,
I know that many articles have been written describing configurations for packages but I have yet to have found one that describes if it is possible to use SQL Server configuration type for a package that is to be tested on DEV, then UAT, then PROD boxes.
I would like to know if there's a way to store values in a config table in a database on DEV, UAT and PROD but never have to change anything in the package.
I mean, I wonder if I can pass a parameter that defines the server to go get the configurations from.
The 3 servers will contain a database with a config table named the same on each server but have different configuration values to point them to the proper sources and destinations depending on which server the configuration database resides.
Thank you,
Robin
SSIS Package Configurations
Dec 13, 2007
I have a problem where in I am trying to set up the package in a new environment.
I just need to change the server to which the data flow task will be populating.
I know we have xml configuration file. Is there any way i can read the files from .NET code and change the server name.
Thanks
Sai
Missing SSIS Configurations...pls Help.
Feb 6, 2006http://img96.imageshack.us/my.php?image=untitled5ia1.jpg
how come my SQL 2005 surface area configuration don't have the configuration for SIS and other settings? seems to be missing alot of settings...it only say SQL 2005 Express when I m using SQL 2005 enterprise....
Managing Package Configurations
Nov 5, 2007Hello,
I have a solution with a lot of packages, and each of them has configurations set on an SQL Server table. Right now the information in the table is outdated and I'd like to overwrite it with the new values on the properties of each package (and its elements). Is there another (faster) way to do this without having to disable/enable or recreate all configurations manually?
Thanks in advance
SSIS File Configurations
Apr 10, 2007Hello, I have a SSIS package that is making one database connection (dbMAIN). I am using the XML File Configuration to specify dbMAIN's properties (server, database, password, etc).
The problem is that the File Configuration contains entries for multiple databases (dbMAIN, dbTEST, dbSTAGING, etc)
When the SSIS is run through dtexec.exe, I get this error:
Error: 2007-04-10 16:52:23.39 Code: 0xC001000E Source: TEST Description: The connection "DDMAIN" is not found. This error is thrown by Connections collection when the specific connection element is not found.End Error
I do not want it to give me an error. The XML File must house all our database connections. What can I do?
`Le
Error Using Configurations Files
Jun 4, 2007I wanted to add all my configurations/vairbles in a config file, so I€™d tried to use the Package Configuration Organizer, to create and enable package configurations€¦
If I want the config to contain information regarding on of my connection manageres, the package fails during test, with the following msg:
Information: 0x40016041 at OrcaleToSQLServer: The package is attempting to configure from the XML file "BDK_LABasedataDTS_config.dtsConfig".
SSIS package "OrcaleToSQLServer.dtsx" starting.
Information: 0x4004300A at Transfer data from Oracle To SQL server, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at OrcaleToSQLServer, Connection manager "XXXXX.xxxx": An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Der opstod en eller flere fejl ved en OLE DB-handling på flere trin. Kontroller alle OLE DB-statusværdier. Handlingen blev ikke udført.".
Error: 0xC020801C at Transfer data from Oracle To SQL server, OLE DB Source to Orcale [1]: The AcquireConnection method call to the connection manager "LASYS.lakig" failed with error code 0xC0202009.
Error: 0xC0047017 at Transfer data from Oracle To SQL server, DTS.Pipeline: component "OLE DB Source to Orcale" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Transfer data from Oracle To SQL server, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Transfer data from Oracle To SQL server: There were errors during task validation.
SSIS package "OrcaleToSQLServer.dtsx" finished: Failure.
Translation Danish line:
Der opstod en eller flere fejl ved en OLE DB-handling pĂĄ flere trin. Kontroller alle OLE DB-statusværdier. Handlingen blev ikke udført -> One or more errors regarding a OLE DB-action on serveral steps. Check all OLE DB-statevalues€¦ the action was not carried out..
Multiple Mirroring Configurations.
Mar 18, 2007Hi all,
as we are looking to a HA-solution for our SQL-environment we're expecially looking for Clustering or Mirroring.
Because we're the central IT-departement for diferent departements in our company we have at this moment seperate SQL-severs for each departement.
The problem is that there is no HA-option configured for none of them.
The mirroring-solution looks the best option for us. But even when we consolidate our bunch of SQL-servers, we end up with 2 machines.
The question is, is it possible to have a single server who can serve as 'mirror' for 2 'principals'?
The idea is to set up a seperate mirror configuration (one witness, one principal and one mirror in each config) for the 2 production servers but where the mirror is serving in both the configurations?
Does anyone have an idea if this is possible, recommendable, supported,... ?
other suggestions are welcome too.
Thanks in advance.
Joeri