I have SSIS packages that send success/failure email upon completion, and I'd like to add a note that identifies the server and database used. I can certainly add variables to the packages and use them when constructing the email, but I'd prefer to get the information directly from the OLE DB connection itself. Is there a way to access the connection string from within a control-flow VB script task? Furthermore, can I get the data source and initial catalog from that connection string, or do I need to parse it myself?
I'm trying to get a better idea of how I should set some of the SSCE connection properties. I will be deploying the application on a WinCE5 board with a 4G flash storage card and 512MB RAM. The application must run 24/7 with a medium amount of traffic. At any given point I expect up to 3 connections to the same database. I'd really like to keep performance high without risking any database corruption issues. As such, I need to make sure my connection properties are optimized.
I read from Joao's article that setting DBPROP_SSCE_MAXBUFFERSIZE = 1024 gave a considerable performance increase, while anything higher gave diminishing returns. Is this for a certain amount of RAM, or is it uncorrelated to RAM size?
What's the best practice for specifying DBPROP_SSCE_TEMPFILE_DIRECTORY and DBPROP_SSCE_TEMPFILE_MAX_SIZE? Is it possible in WinCE5 to directly put it in RAM? How big do these temp files get?
I've read of some bad experiences with autoshrink. Are there any reasons not to set DBPROP_SSCE_AUTO_SHRINK_THRESHOLD to 100 if the database will be compacted regularly?
Any other advice for the more obscure settings such as DBPROP_SSCE_FLUSH_INTERVAL or all the lock settings?
I want to transfer data from one server to another by using SSIS. i want the connection string to be dynamic and also according to the some other variable, the transforming data is changing.
Could you provide me the solution thet how i am able to change my connetction string dynamically and the other variable too.
i am using VS 2003 as front end and SQL server 2005 as a backhand.
Due to VS.NET 2003 i am able to create DTS packages but i have to migrate it and then anly i am able to use it in JOB in SQL server agent of SQL server 2005.
is that any code or any stored procedure from which i am able to migrate DTS packages to SSIS packages.
I designed a DTS package with eleven different connections. When I try to see the proberties of a connection the enterprise manager immediatly closes without giving me a warning. This happens only with two connections (in my example M1 and M2). This mistake has no influence to the execution of the package. In my opinion it is a fault of the DTS package desinger but may be that I made something wrong. Is there anybody who knows this mistake? Thanks for your help.
I am working on a SSIS package which is using a Script task, now I have all the connection properties set up in the .NET script using connection strings, what do I need to do if I have to set this up using a config file or something else which is more secure (I dont want to leave the connection information in the script). Please Advice.
I used to do this in DTS but wondered how to do it in SSIS. I have a For Each ADO recordset loop that reads these four columns into variables:
DataSource Name, Name(for a WHERE clause), user and password.
For each "Building" there is a new server to connect to, with identical tables. I have 10 tables in the loop to pump from one database to a SQL 2005 database (i.e. 10 dataflows). I want to set the source connection information with the variables. My questions:
1. How do I set the connection information for the source connections? Do I just set the properties for the connection manager and then it sets for all the sources? What property do I set--do I need a script task?
2. How do I use the values from the "Name" column in a WHERE clause in each data flow?
Hi - Im migrating packages in dts 2000 to SSIS 2005 using package migrator wizard. It loads a CSV to a table in DB. I need to change the server and file location paths after migration. While changing this, im encountering error - "the acquireconnection method call to connectionmanager failed
How to use variables in Connection Manager's properties? I see some replies through Configuration Package. But what if, it is still in development stage? I mean, can I use the Variable tab and create some variables like
Then put them in Password and UserName property of Connection Manager? If this is possible, how and how can I set the values of those variables I mentioned when I am going to deploy the package in the Production?
As the title states I have a 64bit DSN but when I try to create ODBC datasource in Reporting Services it does not appear in the dropdown box. I have created as a system DSN.
I know that I think as sql2k programmer-dba yet but I can€™t avoid.
I€™ve got Flat File Connection Manager Editor dragged with a text file as €˜ragged right€™ format and CRLF as header row limiter. When from properties page and Columns option I€™m going to alter just a few colums I am not be able. It seems that you must erase all of them in order to define one or two. And in the case you€™d have 50???? When I ran sql2k DTS designer did that without problems, alter columns again and again.
As far as I know it€™s a lose of flexibility, or not? Or is there any way for do that without deleting nothing else?
I am testing SSIS and have created a Flat File Destination. I defined the Flat File Connection as New for the first time and it worked fine. Now, I would like to go back and modify the Flat File Connection in the Flat File Destination Editor, but it allows only to create a New connection rather allowing me to edit the existing one. For testing, I can go back and create a new connection, but if my connection had 50-100 columns then it would be an issue to re-create it from scratch.
I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
I have a package that uses a for loop to iterate through an unknown amount of excel files and pull their data into a table. However, there will be cases when the file is corrupted or has some sort of problem so that either the transformation will fail or the excel data source will fail with an oledb connection error. Could anyone suggest a clean way to trap these errors? Specifically, the "Cannot Aquire Connection from Connection Manager", which is the excel connection.
is there anyway to retrieve the database properties and display in an asp.net web form as a quick over view rather than logging onto the server to check, items such as size, space available, last backup data etc? Cheers
Hi and thanks for your help specifying the default database means what. for instance when I add alog in user and I have to choose a default database. Does it make a difference if the Default database is Master or any other user database name. What is the significant to select Master vs any other database.
Hello! We are having problems with a single SQL Server where the Server properties will not save, and always revert to the default. It occurs only on those tabs with "configured" and "running" options at the bottom. Other tabs will keep the settings we custom. The big problem is with the memory and processor tabs which even when we've made only a single change and stopped and started the service, reverts back to a default. Any clues - we are logged in as "sa" and have not removed the SA priviledges.
Plz help me to know whats wrong with following script:
EXEC sp_addextendedproperty @name = 'col_Programs_ID_Description', @Value = 'The Unique Identifier for a specific Program', @level1Type = 'Programs', @level2Type = 'ID'
I get the following error: Server: Msg 15600, Level 15, State 1, Procedure sp_addextendedproperty, Line 42 An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.
NOTE: If I dont use the last 2 parameters this procedure runs fine, but it adds the Extended Property to the Database Level while I'm trying to add it to the TABLE-->COLUMN level.
We installed an application named Streamserve. Now, if I go into Enterprise Manager I can expand the databases fine. But when I try to right-click properties Enterprise Manager goes bye bye. No error messages. I tried this on master and same thing. I currently have 2 different machines - a dev and a QA box and both are behaving badly.
Help;Im New with a company and the sql server ismaxing out the cpu'sWe have 3 web servers load balanced....large volume of datathe current PropertiesComputer:4 amd 2.88 processors4 g MememoryRecomendations of good sql setupmemory, cpu etcThank youmike
Im having abit off trouble with my images positioning..
I have 3 images in the header placed side by side with a rectangle around them sent to the back
In layout view the images look correct, however when i deploy the report the rectangle appears on top and then the images appear undernealth each other...when they should be inside the rectangle. is there something like float on top properties similar to objects in Word?
A separate question, I also have a table to the right off the table i have an image and under that image another image...when i preview the report the second image is pushed to the bottom of where the table ends...is there someway to make the image appear side by side to the table instead off getting pushed down to the bottom...something similar to a frame in HTML ?
Does anyone know of a good primer on extended properties and why I would want to use them. I have you the on-line books sections but that does a clear "why". I have search for white papers and similar documents.
I understand they are good for documentation, but I was looking for something that explain Microsoft long-term vision. How I might use them in template scripts, VSTS, etc.
Hi all, Is it possible when creating a stored procedure through visual studio using a sql server 2005 project, to set the sizes of the parameters that are specified. For example:
public partial class test{ [Microsoft.SqlServer.Server.SqlProcedure] public static void addRecord(string name, string details) { ................. some code ................. }} Would it be possible to set the size of the parameter 'details' to nvarchar(max) and 'name' to nvarchar(50) or something along those lines. The problem at the moment is that when this project is deployed both of the parameters are set to 4000 characters and sometimes the details parameter are greater than 4000 characters and it is being truncated.
I am currently running some sql that alters the procedure to set the sizes of the parameters but this isn't really an ideal solution.
I am trying to find information on Database properties - more specifically, what the 'Space Available' means, because right now when I look at the properties of a database I am setting up, which will grow substantially I am sure, it says the database size is 154 MB and the Space Available is 42 MB. What is the Space Available? What does this refer to and is it going to be restricting the growth of the database? Thanks.
I have a chart that is created in SSRS with the size property set to '15cm, 8.5cm', I want to set the height based on a parameter that is passed to the chart ( so that it fits onto a 1 page report it is fed through to), I have tried putting =Code.SetChartHeight() in the height property but it brings up an error saying it needs to be in a format like '8.5cm'. The SetChartHeight() function just returns the height parameter + 5.
Hi,I'm creating a User Interface to display Sql database Properties, but I cannot find the right query to retrieved the info on database properties. The status that I get is "ONLINE", but it should be "NORMAL". Cannot find the right query to get the date of last database backup, last transaction log backup, and maintenance plan, etc.. Please help me [:'(]
Is there no longer a graphic interface for the parameters that are output with sp_configure? There are many options that are settable that are not in the Sql Server Propreties, but these items were in the 6.5 graphic interface through EM ex. number of open objects. Is there a script to save these settings so the installation can be recreated?
