HI,
I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed
for this i need a table and procedure to complete family tree
Thanks
@mbi
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP
SELECT * INTO Emp FROM (
SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL
SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL
SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL
SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b
*/
[code]....
Does anyone know what is the Equivalent of Top in Oracle.
SELECT TOP 2 from TableNAME --> SQL SERVER.
What is the equivalent of this in Oracle
nevermind. please ignore this. theres too much extra data that id have to provide, but it would confuse the question so much that it couldnt be answered properly.
View 4 Replies View RelatedHi All,
We are evaluating DB mirroring for our production box. But we have some queries with the operating mode available for mirroring.
1) I set the database miroring operation mode to full safety mode which is with synchronous. And Then executed a command to insert data in bulk in principal, the changes were commited to principal even when they are still getting mirrored to mirror server.
But as per the mirroring documentation from microsoft says this
"If transaction safety (or just 'safety') is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk."
Is this a bug or I am doing anything wrong.
2) Mirroring documentation also says that when the transaction is on full safety mode, and if mirror and witness goes down then principal goes to read only mode. But again I saw deviation from the documentatin because when I turned mirror and witness off, principal went to "In recovery" mode.
Please help incase anybdy have faced this before or can help me if I am interpreting it wrong.
Thanks
Sachin
Hi,
I am using LIKE operator for a query to get wild card searched data.
for example if i give AN, should get for %AN% only. But it is returning data for %NA% data too. I cross checked by giving simple data. Please help me on this.
Thanks in advance
Functionally, is there any difference between DATEPART for m, d, and yyyy, verses simply using MONTH, DAY, and YEAR functions respectively?
For all intents and purposes, they seem identical, so is there any performance considerations using one verses another?
I have a table in one database (source) and an equivalent table in another (destination) and I need to move from my source to my destination database.
When I move the data I need to do the equivalent of an updategram, so for instance compare primary keys and if it exists in both do an update, if it is in source but not destination do an insert, if it is in destination but not source do a delete.
Is there already a process for doing this, as it seems pretty fundamental but I've not really found anything that gives the result I need?
If not, is there a way of doing this with what is available within Data Flows or should I just go ahead and go through the pain of creating my own data flow destination object?
TIA,
CD
I design a reporting services solution based on the standard Report Manager web site.
I configure IIS using basic authentication and passing the user credential to SSAS 2005 DB for securing data access.
Everything funtions well .... but now my client ask me for a new 'funcionality'.
They ask me to add a logoff button to change the current logged user.
Any suggestion how to implent that functionality with the minum development effort ???
Thanks
Cosimo
Hi,
I have 3 tables that i gather a single field from each and put them into another table.. simple enough you might think
but when i start using a lookup funciton to check if i have already entered an item (this package could be run a number of times) some duplicates slip though, and as i continue to run the pakcage less and less appear, but never reaching 0
very strange..
doing select statements on the target table reveal strange behaviour.. after the first 'iteration' there are only unique entries in the target table (exactly what i want). After the 2nd, 3rd, 4th etc duplicates appear of upper and lower case
i can only assume that there is something wrong with my query,,, but running it in SQL Manager reveals ~40,000 rows no matter how many times i run it...
more magically appear when hte same statement is ran in SSIS!!
I have tried a view, and using test tables (rather the live ones i am working on) and the reuslt is the same...
any help would be muc happreciated
regards
Chris
currently have my website that is functional...but would like to add some navigation to my photos which are displayed using a repeater tied to a SQL database. (live example can be viewed here)
the above link shows a gallery page that displays all the photos within a given shoot (using a querystring to display the unique ID of the photo shoot). When you click on one of the images within the shoot a page called is called displaying the selected photo by passing the shoot ID and the unique ID of the photo itself through a querystring.
I would like to add navigation to the page displaying the photo that would allow the user to go to the next photo, previous photo, first and last phot within the specific SHOOT ID.
FirstNextPreviousLast
I am using a repeater to display the photo with the following sqlDataSource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Connection %>"SelectCommand="SELECT usr_Photos.*, usr_PhotoShoot.* FROM usr_Photos INNER JOIN usr_PhotoShoot ON usr_Photos.Shoot_ID = usr_PhotoShoot.Shoot_ID WHERE usr_Photos.Photo_ID = @ID AND usr_Photos.Shoot_ID = @Shoot"><SelectParameters><asp:QueryStringParameter DefaultValue="" Name="ID" QueryStringField="ID" /><asp:QueryStringParameter DefaultValue="" Name="Shoot" QueryStringField="Shoot" /></SelectParameters>
</asp:SqlDataSource>
I am having trouble figuring out a way to add the functionality i would need to change photos within the specific photo shoot without having to go back to the gallery page. any help or ideas you could point me in would be great...thanks in advance...
Hi,
Is is anyway to print out the result from OLAP cube?
Thanks in advance.
David
Hello, everyone:
In the BOL about Designing a Backup and Restore Strategy, it is mentioned there is a Base Functionality Script that should be run after SQL Server restored. What is Base Functionality Script? Just DBCC CHECKDB? Anything else?
Thanks
ZYT
Hi,I've used the minus functionality which is available in Oracle andi would like to use it in SQL server, but i don't know how to. Thefolllowing is how it works in OracleSelect symbols from symbol_tableminusselect tsymbols from tradeIt returns a list of all the symbols from symbol_table which are notpresent in trade.Similarly, the intersect will return only those which are common toboth.I was wondering if someone throw some light on this problem for me.Thanks in advance,Sumanth
View 1 Replies View RelatedI'm not seeing any built-in functionality for Report Builder to get Top N values. Am I missing something? Is there a way to do this? If not, is there a way to simulate this functionality?
Thanks.
PJJ
I am fairly new to MS SQL and an wondering if the following is possible;
Step 1 - person fills out a form on our website and submits it to our SQL DB via a stored procedure
Step 2 - the stored procedure inserts some of this information into a DB table and sends some of the information via the inbuild DBMail in an email.
Firstly, is this possible?
Sencondly, is there a way of encrypting this email before it is sent?
There is a view on our server that has fields for project name and then 25
months, each row contains the descriptive name and the forecasted budget in
each month. I need to create a chart showing the total budget for month and
a running total for the year to date. In Excel this is easy with a Pivot
Chart, how do I do this in SSRS 2005?
TIA
Dean
In one of my packages, I am trying to create functionality that is similar to the fuzzy grouping transformation in that it takes a set of records and returns a subset of unique records. However, rather than fuzzy-matching columns which creates significant overhead, I just want to compare columns exactly, similar to how the lookup transformation works. At this point, data has already been fuzzy unique/duplicate seperated, this step is to only keep one record for each exactly similar dupe for an alias table.
At the moment, I am doing this (with lack of scalability foresight) with a fuzzy grouping transformation with all columns set to exact except for one which is set to fuzzy with a minimum similarity of 0.99. While this worked during development when I was testing with a small subset of data, it is unreasonable for the ~5mil records of production data it needs to operate on.
I am sure there is an efficient way to do this. I have thought about using a lookup transformation or combinations thereof, but I have not come to a definite solution yet.
Does anyone have any experience doing something similar to what I am trying or have any suggestions/ideas/questions/etc? Any help will be greatly appreciated.
Hi,
I have a doubt regarding use of navigation controls in a report and exporting the report. For example using a drilldown functionality in a report. After the report is generated one can export the report in whatever format he wants (PDF,TIFF, XML,...).
In this case, with the drill down functionality in the report, the details of the report are visible only when the plus sign is clicked. Suppose now the plus sign is not clicked and one exports the report in a PDF or TIFF format.
In the PDF file, the navigation or the drill down functionality is now not useful right ? There is no way to view the detail fields of the report now, using the PDF file right?
Thanks in advance,
Hi All,
In my Report using SSRS 2005, I am displaying data using table and this table has table footer in which I am calculating "average" using Avg() function.The values in table rows can be either floating numbers (for eg; -1,-99,5.005) or "N/A". I use the following function to calculate Average
=Avg(Iif(Fields!Channel1.Value="N/A",CDbl("0"),CDbl(Fields!Channel1.Value)),"MainDS")
where "Fields!Channel1.Value" are values in table rows.
Everything works fine if all the table rows are floating point numbers and not "N/A", but as soon as table rows values are "N/A" I encounter "#Error" in the table footer.
Can anyone figure out how to get this code working. Thanks in advance.
Regards,
abhi_viking
see article: Article ID: Q216700 .
Within a trigger on SQL Server 6.5 Service Pack 5 or 5a, the IF UPDATE clause will evaluate to true for all columns when an INSERT is performed, even if there is no value specified and no default value exists for the column
http://support.microsoft.com/support/kb/articles/q216/7/00.asp
sdcsdcsdc
View 15 Replies View RelatedHello,
I have the problems with UserSort functionality. I have attached the report to show what I mean. textbox3 located in the table group has the following user sort property value:
<UserSort>
<SortTarget>table1</SortTarget>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
</UserSort>
I expect that the sorting will be performed within the entire table(data in both of groups will be sorted alphabeticaly by company name).
Instead of it, the sorting is performed independently for every group of table. What is the reason??
Here is the report
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<ConnectString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:wind.mdb</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<InteractiveWidth>8.5in</InteractiveWidth>
<Body>
<ReportItems>
<Table Name="table1">
<Left>0.375in</Left>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox3">
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>MediumTurquoise</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortTarget>table1</SortTarget>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
</UserSort>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="CountryGroup">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Width>4.5in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<TableColumns>
<TableColumn>
<Width>3.15in</Width>
</TableColumn>
<TableColumn>
<Width>1.08in</Width>
</TableColumn>
<TableColumn>
<Width>0.27in</Width>
</TableColumn>
</TableColumns>
<Height>0.5in</Height>
</Table>
</ReportItems>
<Height>0.75in</Height>
</Body>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>select * from customers where country='Mexico' or country='Austria'</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
</Field>
<Field Name="City">
<DataField>City</DataField>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Is there a way to add additional functionality to the report parameter boxes?
For example, when a user enters an invalid choice into a report parameter box, I want a popup balloon that tells them to try again.
I know how to do this for a regular Winform combobox, but can I implement an interface to override the default behavior of the report parameter boxes?
Hi folks,
We had a minor data loss snafu (nothing major, and easily recoverable with our backup strategy), but I don't seem to be able to use the ol' DBCC LOG trick to get insight about when the deletion occurred. Obviously, since the log was backed up and truncated at least once since the deletion, there's nothing in the log file to analyze. I've tried restoring to a temporary database, but hit a couple of snags. First of all, running DBCC LOG on a database that's in standby mode simply shows the logged activity of the recovery process, and not any of the activity that was in the original transaction logs. Furthermore, running DBCC LOG against a database that's in recovery fails pretty ungracefully with a severity 20 error (I obviously didn't care to experiment with that approach any further).
Is there any way I can peek into the original transactions contained within a series of full and log backups to get a little who/when information? Or is this something that only Lumigent can do? I don't mind really ugly hacks here, since I'm simply doing read-only business with the backups. I'm not scared of a hex editor if it comes down to it.
As so rightly stated in http://www.microsoft.com/technet/prodtechnol/sql/70/books/inside6.mspx
"Currently, UDDTs don't support the notion of subtyping or inheritance,
nor do they allow a DEFAULT value or CHECK constraint to be declared as
part of the UDDT itself. These powerful object-oriented concepts will
likely make their way into future versions of SQL Server. These
limitations not withstanding, UDDT functionality is a dynamic and often
underused feature of SQL Server."
There's no point bothering with using a UDDT in T-SQL if you can't apply constraints to the UDDT!
1 vote for the introduction of the mentioned features into T-SQL
comments anyone?
I have a reporting server that needs to be utilized now but the person that set it up is no longer with the company.
We have no idea what login he used to install the server and nobody was granted admin rights to the server.
is there anything we can do to give someone admin access without having to reinstall (I don't think that is an option)??
Hello,
I have the problems with UserSort functionality. I have attached the report to show what I mean. textbox3 located in the table group has the following user sort property value:
<UserSort>
<SortTarget>table1</SortTarget>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
</UserSort>
I expect that the sorting will be performed within the entire table(data in both of groups will be sorted alphabeticaly by company name).
Instead of it, the sorting is performed independently for every group of table. What is the reason??
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<ConnectString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:wind.mdb</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<InteractiveWidth>8.5in</InteractiveWidth>
<Body>
<ReportItems>
<Table Name="table1">
<Left>0.375in</Left>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox3">
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>MediumTurquoise</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortTarget>table1</SortTarget>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
</UserSort>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="CountryGroup">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Width>4.5in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<TableColumns>
<TableColumn>
<Width>3.15in</Width>
</TableColumn>
<TableColumn>
<Width>1.08in</Width>
</TableColumn>
<TableColumn>
<Width>0.27in</Width>
</TableColumn>
</TableColumns>
<Height>0.5in</Height>
</Table>
</ReportItems>
<Height>0.75in</Height>
</Body>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>select * from customers where country='Mexico' or country='Austria'</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
</Field>
<Field Name="City">
<DataField>City</DataField>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Hi All
I have a problem getting the full-text search service working on my SQL Express install.
I can see the "SQL Server FullText Search" service running in the SQL Server CM but when I run:
select fulltextserviceproperty('isfulltextinstalled') it returns false.
Can anyone explain what I must of missed or point me in the right direction?
Thanks
Mark
Hello All,
When i am calling the Report from ASP.NET getting the below error:
An error has occurred during report processing.
Query execution failed for data set 'dsOpenDefect'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
SP:
@DefectImpact varchar(max) = '',
@DefectNumber nvarchar(255) = '',
@DefectApplication nvarchar(255) = '',
@DefectBusinessFunction nvarchar(255) = '',
@DefectRelease nvarchar(255) = '',
@DefectStatus nvarchar(255) = '',
@Owner nvarchar(255) = ''
AS
set concat_null_yields_null OFF
Begin
declare @sql nvarchar(4000)
declare @whereClause nvarchar(max)
declare @sqlWhere varchar(max)
declare @Debug nvarchar(max)
select @sql = '
SELECT Defect.DefectNumber, Defect.DefectApplication, Defect.DefectBusinessFunction,
Defect.DefectRelease, Defect.DefectStatus, Defect.Owner, Defect.DefectImpact FROM Defect '
Set @sqlWhere = ''
if @DefectImpact <> '' or @DefectImpact IS NULL
begin
set @whereClause = ' where DefectImpact '
set @whereClause = @whereClause + ' = ''' + @DefectImpact + ''''
end
set @sqlWhere =@sql + @WhereClause
PRINT @sqlWhere
EXEC(@sqlWhere)
End
ASP.NET Code:
ReportParameter[] parm = new ReportParameter[7];
parm[0] = new ReportParameter("DefectImpact", txtValue3.Text.ToString());
parm[1] = new ReportParameter("DefectNumber", txtValue3.Text.ToString());
parm[2] = new ReportParameter("DefectApplication", txtValue3.Text.ToString());
parm[3] = new ReportParameter("DefectBusinessFunction", txtValue3.Text.ToString());
parm[4] = new ReportParameter("DefectRelease", txtValue3.Text.ToString());
parm[5] = new ReportParameter("DefectStatus", txtValue3.Text.ToString());
parm = new ReportParameter("Owner", txtValue3.Text.ToString());
RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
RptViewer.ServerReport.ReportServerUrl = new System.Uri("http://RSName/ReportServer");
RptViewer.ShowParameterPrompts = false;
RptViewer.ServerReport.ReportPath = "/Reports/Subbu_Sample";
RptViewer.ServerReport.SetParameters(parm);
RptViewer.ServerReport.Refresh();
Can anyone please help me in this regard.
Thanks,
SR.
I am creating a simple report in reporting services. It contains basic fields such as first_name, last_name, ID#, location.
Is there a way to create a search/find function that lets me input a field and it will find the results from any column?
I think I can figure out how to search by 1 single column but want the user to input one thing and search multiple fields.
For further clarification...if they know the last_name, but want the id#, they can search for last name and get all the columns for the particular search....same would work for, if they know the ID#, but want the last_name, they could search for ID# and get all columns in the result.
Is this possible???
i m using Visual studio 2005
sql server2000
and i have installed the membership database in my same database as for my rest of the tables
my login page is placed with a login control
database is having a user named : "bbbb" with password !@#$%^& (Shift+1234567)
the code is working very well on my local host
but when it is uploaded to the web server it is not validating the users
u can check the error my url is Http://www.webgrowthengine.com
click on the login button and provide the above credentials
this is the code of my WEb. con fig file
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="job" connectionString="database=gcs;uid=nitin;pwd=temp"/>
</connectionStrings>
<system.web>
<customErrors mode="Off"/>
<roleManager enabled="true" defaultProvider="JobRoles">
<providers>
<add connectionStringName="job" name="JobRoles" type="System.Web.Security.SqlRoleProvider"/>
</providers>
</roleManager>
<pages maintainScrollPositionOnPostBack="true"></pages>
<siteMap defaultProvider="LC" enabled="true">
<providers>
<add name="LC" type="System.Web.XmlSiteMapProvider" siteMapFile="web.sitemap"/>
<add name="Admin" type="System.Web.XmlSiteMapProvider" siteMapFile="web2.sitemap"/>
<add name="RC" type="System.Web.XmlSiteMapProvider" siteMapFile="web3.sitemap"/>
</providers>
</siteMap>
<compilation debug="true">
<assemblies>
<add assembly="System.Management, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies></compilation>
<authentication mode="Forms">
<forms name="ad" loginUrl="Frmlogin.aspx"></forms>
</authentication>
<membership defaultProvider="JobMember">
<providers>
<add connectionStringName="job" name="JobMember" type="System.Web.Security.SqlMembershipProvider"/>
</providers>
</membership>
<httpHandlers>
<add verb="GET" path="CaptchaImage.aspx" type="WebControlCaptcha.CaptchaImageHandler, WebControlCaptcha"/>
</httpHandlers>
</system.web>
</configuration>
I've been working for the last few days on some custom "configuration-like" functionality that allows me to store configuration information external from the SSIS packages that need it, without relying on the built-in Configuration features of SSIS. (Of course, if someone wants to tell me how to implement Configuration encryption and a few other features while using the built-in tools, I'm more than eager to hear that too. ) I've never had the need to do this before, and although everything is going relatively smoothly, there are a few things that I'm lacking, and the main one is validation.
Currently in order for me to use my new functionality, I need to set DelayValidation = true on my data flow tasks, or else they will fail validation (and thus fail the package) unless the design-time connection information is valid. I would strongly prefer to leave DelayValidation = False so I can more easily have "deployment sanity checks" without needing to actually execute my packages.
So here is my question:
Is there a mechanism by which I can have my own logic (implemented in a Script Task currently) executed before package validation so that I can assign values to variables which are used by connection managers, so that the end product is that the package passes validation without needing to set DelayValidation = true, and fails validation if the information in the external "configuration store" is not valid for the current deployment environment?
Any tips or tricks would be very much appreciated - thanks in advance! Thanks in advance!