Hello. Im trying to create an SQLDataSource control programmatically. I need to do this because I want to do some stuff on my MasterPage's 'Page_Init' event.
heres my code (Master.master.vb): Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
lblUser.Text = Page.User.Identity.Name
Dim PUser As New ControlParameter
PUser.ControlID = "lblUser"
PUser.Name = "LoginName"
PUser.PropertyName = "Text"
PUser.Type = TypeCode.String
PUser.DefaultValue = Page.User.Identity.Name
Dim SQLDS_Login As New SqlDataSource
SQLDS_Login.ID = "SQLDS_Login"
SQLDS_Login.ConnectionString = "I put conection string here. How do I use the one on my web.config?"
SQLDS_Login.SelectCommand = "SELECT [LoginID], [LoginName], [Role], [Status] FROM [myLogin] WHERE ([LoginName] = @LoginName)"
SQLDS_Login.SelectParameters.Add(PUser)
SQLDS_Login.SelectCommandType = SqlDataSourceCommandType.Text
I am trying to add a number of dates into a Sql database. Basically I want the user to add the start and end date and then all the dates in between are are added to a database in unique records. I can create an ArrayList but I don't know how to bind it to an SqlDataSource Dim startdate As DateTime = tbstartdate.Text Dim enddate As DateTime = tbenddate.Text Dim datediff As TimeSpan = enddate.Subtract(startdate) Dim noofdays As Integer = datediff.Days Dim ar As New ArrayList Dim i For i = 0 To noofdays ar.Add(startdate.AddDays(i)) Next Sorry if this is a total noob question....
I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find is http://forums.asp.net/thread/1233258.aspx but no answer was posted. What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0]) Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user? Thank you very much for your help.
Background - I have a page that uses a numeric value stored in a Session object variable as the parameter for three different SQLDataSource objects, which provide data to two asp:Repeaters and an asp:DataList. Also, in the Page_Load, I use this value to to seed a stored procedure and an SQLDataReader to populate several unbound Labels. This works fine. In addition, I have a collection of 6 TextBoxes, an unbound Listbox, and two Buttons to allow the user to do searching and selection of potential matches. This basically identifies a new numeric value that I store in the Session variable and PostBack the page (via one of the buttons). This also works fine.Problem - I have been tasked with taking a different page and adding six textboxes to collect the search values, but to post over to this page, populate the existing search-oriented TextBoxes, adn programmatically triggering the search. Furthermore, I have to detect the number of matching records and, if only 1, have the Repeaters and DataList display the results based on the newly selected record's key numeric value, as well as populating the unbound Labels. I have managed to get all of this accomplished except for programmatically triggering the Repeaters and DataList "refresh". These controls only populate as expected if a button is clicked a subsequent time, which makes sense, since that would trigger a PostBack and the Page_Load uses the new saved numeric key value from the Session.My history in app development is largely from Windows Forms development (VB6), this is my second foray into Web Form dev with ASP.NET 2.0. I am willing to acceptthat what I am trying to do does not fit into the ASP environment, but I have to think that this is something that has been done before, and (hopefully) there is a way to do what I need. Any ideas, oh great and wise Forum readers? *smile*
ello all Would someone be so kind as to save me from getting balder through pulling my hair out. My aim is to extract data from a database using SQLDataSource, then edit the data and update the database using the SQLDataSource. I have achieve the problem of retrieving the data from the sqlDataSource:DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty); //Int32 openRemindingSeats = SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView; foreach (DataRowView rowProduct in openReminding) { //Output the name and price lbl_NumOfSeatsLeft.Text = rowProduct["Remaining"].ToString(); } Within the sqlDataSource the sql code is as follows:SELECT [refNumber], [refRemaining] FROM [refFlights] WHERE ([refNumber] = @Number) So at the moment my problems is being able to edit and update data to the same SELECTed data.Thank you for any help that you might have... SynDrome
Hi, All I'm using Gridview and SqlDataSource to dynamically display the contents in different tables, as followed: <% dataSource.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"]; dataSource.SelectCommand = "SELECT * FROM " + tableName; gridView.DataBind(); dataSource.UpdateCommand = "";%> <asp:SqlDataSource ID="dataSource" runat="server"></asp:SqlDataSource><asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" PageSize="20" DataKeyNames="ID" OnRowDataBound="tableGridView_RowDataBound"> <HeaderStyle BackColor="#C0C0FF" /> <AlternatingRowStyle BackColor="#C0FFC0" /></asp:GridView> The datasource take the "tableName" as argument to determine which table to display. My problem is I can't figure out a way to programmatically construct the UpdataCommand for the SqlDataSource. I try to get the field names from the HeaderRow, but all the cells are empty in this row. Does anyone know what causes the problem or how to construct the UpdateCommand properly. Thanks!
I am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues. The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key: ALTER PROCEDURE [dbo].[InsertAlbum](@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)ASDECLARE @err intINSERT INTO dbo.ALBUMS (ARTISTID, TITLE)VALUES (@ArtistID, @Title)SELECT @err = @@error IF @err <> 0 RETURN @errSET @NewID = SCOPE_IDENTITY() This works fine when run from Sql Server Management Studio and @NewID has the correct value. My data access code is roughly as follows: dsrc = New SqlDataSource()dsrc.ConnectionString = ConnectionStringdsrc.InsertCommand = "InsertAlbum"dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedureDim parms As ParameterCollection = dsrc.InsertParametersDim newid As IntegerAddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)AddParameter(parms, "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)Dim p As New Parameter("NewID", TypeCode.Int32)p.Direction = ParameterDirection.Outputparms.Add(p)Try Dim rv As Integer = dsrc.Insert() newid = parms("NewID") Return newidCatch ex As Exception Return -1End Try The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value. Has anyone tried to do this and can tell me what I am doing wrong? Jon
I need to provide defaults and sometimes overrides for items in SQLDataSource's UpdateParameters. I am attempting to do this in a FormView's ItemUpdating and ItemInserting events as follows: //======================================================================== // FormView1_ItemUpdating: //======================================================================== protected void FormView1_ItemUpdating(object sender, FormViewUpdateEventArgs e) { // not sure if this is the bets place to put this or not? dsDataSource.UpdateParameters["UpdatedTS"].DefaultValue = DateTime.Now.ToString(); dsDataSource.UpdateParameters["UpdatedUserID"].DefaultValue = ((csi.UserInfo)Session["UserInfo"]).QuotaUserID; } In the example above I am attempting to set new values for the parameters which will replace the existing values. I have found that using the DefaultValue property works ONLY if there is no current value for the parameter. Otherwise the values I specify are ingnored.The parameters of an ObjectDataSource provide a Value property but SQLDataSource parameters do not.How can I provide an override value without needing to place the value in the visible bound form element???If you can answer this you will be the FIRST person ever to answer one of my questions here!!!Thanks,Tony
I just want to insert a record into a table using a SqlDataSource control. But I'm having a hard time finding examples that don't use data bound controlsI have this so far (I deleted the parts not related to the insert):<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:UserPolls %>" InsertCommand="INSERT INTO [PollAnswers] ([PollId], [AnswerText], [AnswerCount]) VALUES (@PollId, @AnswerText, @AnswerCount)" <InsertParameters> <asp:Parameter Name="PollId" Type="Int32" /> <asp:Parameter Name="AnswerText" Type="String" /> <asp:Parameter Name="AnswerCount" Type="Int32" /> </InsertParameters> </asp:SqlDataSource> This is the data source for a gridview control I have on the page. I could set up an SqlDataSource for this alone if I need to, but i don't know if it would help. From what I could find, in the code behind I should haveSqlDataSource2.Insert()and SqlDataSource2 will grab the parameters and insert the record. The problem is I need to set the Pollid (from a session variable) and AnswerText (from a text box) at run time. Can I do this?Diane
Hey All for some reason I can not get this right and/or find what I am looking for. I have an SQLDataSource with a PartID set as the filtered value in the Datasource Query. I am trying to use code beside to set the value and I am failing...lol... Here is my attempt at it, SqlDataSource1.SelectParameters("PartID") = txtPartID.Text Any help would be great!
Hi I think I've programmatically created a SqlDataSource - which is what I want to do; but I can't seem to access details from the source - row 1, column 1, for example???? If Not Page.IsPostBack Then 'Start by determining the connection string valueDim connString As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 'Create a SqlConnection instanceUsing connString 'Specify the SQL query Const sql As String = "SELECT eventID FROM viewEvents WHERE eventID=17" 'Create a SqlCommand instanceDim myCommand As New Data.SqlClient.SqlCommand(sql, connString) 'Get back a DataSetDim myDataSet As New Data.DataSet 'Create a SqlDataAdapter instanceDim myAdapter As New Data.SqlClient.SqlDataAdapter(myCommand) myAdapter.Fill(myDataSet)Label1.Text = myAdapter.Tables.Rows(0).Item("eventID").ToString() -?????????????? 'Close the connection connString.Close() End Using End IfThanks for any helpRichard
Im ripping my hair out here.I need to access the field in a datasource control of use in non presentation layer code based actions.I know the I can use a code base connection and query but I dont see why i need to make two trips the the DB when the info is already available.The datasource is attached to a details view control and the details view control is nested in a loginview controlI've tried defining but all I can get in the header name of the field but not the dataitem, the dataitem causes an error help please jim
I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim installtype As Integer = row("InstallMethod") Dim install As String = row("Install").ToString Dim notes As String = row("Notes").ToString The above only works on a single row, of course. If I needed more, I know I can loop it.The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query. Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim count As Integer = row("rowcnt") The query here is "SELECT COUNT(*) as rowcnt FROM Software"The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value. Where am I going wrong here?
I am sending a GUID to a form via the query string. If it exists I use helper functions to load most of the form text boxes. However, if it does not then a blank form is presented and the GUID is stored in a hidden field. Regardless, I use this hidden field to populate a grid that is attached to a sqldatasource. If I then add new datarows to the backend database programmatically, I cannot 'requery' the datasource to include those row upon a postback. I cannot seem to find a simple way to force the sqldatasource to rerun the query. Can anyone help.
Hello, I want to loop through the first 10 records that are showing in a gridview with several pages that is populated by a sqldatasource. I can loop through the sqldatasource and get the list of values, but I'm doing something wrong because the 10 records it prints out are not the same 10 records the user sees in the gridview...They can click a search button which changes the sort, and they can click on the column headings to change the sort order. Where's the best place to put the looping code? I need the result to be the same as what the users sees. 1 Protected Sub GridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.Sorted2 Dim i As Integer = -13 Dim sTest As String = ""4 Dim vwExpensiveItems As Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), Data.DataView)5 6 'Loop through each record7 i = -18 For Each rowProduct As Data.DataRowView In vwExpensiveItems9 i = i + 110 'Output the name and price11 If i > 9 Then12 Exit For13 End If14 sTest = rowProduct("employeeid")15 Response.Write("RowSorting " & i.ToString & " [" & sTest & "]<br>")16 Next17 End Sub18
I've seen several post asking for that possibility, but all 've read, didn't help me.Some sing SQLDMO, other suggest to use SQLSMO, others only explaining to connect to a server and then do "CREATE DATABASE".I will do this within .Net. Connecting to a SQL Server 2005 and execute "CREATE DATABASE" I could realize.But how doing this with SQLExpress? Trying to do SqlConnection.Open with a non existing DB does not work., says "file not exists".Or do I only have the wrong connection string? Can someone post here an excample connection string, which works with a non existing database?Some hints I've read make me considering to use SQLSMO. But I don't have it on my computer. Where do I get it from? Any links would be nice.
I got a user who is requesting a weekly report to be exported in csv (comma delimited) format. But this process will run weekly using schedule job and he wants the file to save to a certain directory on the network. Two part questions...
1. Is there a way to create a .csv file programmatically after runing the query?
2. How would I save the .csv file to a specified directory on the network?
I am trying to write a script in VB.NET that will run a report that already exists in the system and export the results to my local machine. We are using MS Reporting Services to manage and manipulate the reports. So here's my question:
Is it possible to programmatically create a report in VB.NET based on an existing report? I noticed that crystal reports has a nice export method, but I have not been able to find anything similar for my situation. Basically I believe I would need some sort of reporting services object in .NET that would allow me to run the report and export the results. Does anyone know of such a structure, or if this is even possible? Thanks!!
I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception. Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal? I am giving my code here too. I will appreciate any kind of suggestion on this.
Code snippet:
public void CreatePackage() { string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName; Package pkg = new Package(); pkg.PackageType = DTSPackageType.DTSDesigner90; ConnectionManager oledbConnectionManager = CreateOLEDBConnection(pkg); ConnectionManager flatfileConnectionManager = CreateFileConnection(pkg); // creating the SQL Task for table creation Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask); ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask; execSqlTask.Connection = oledbConnectionManager.Name; execSqlTask.SqlStatementSource = "CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) GO"; // creating the Data flow task Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1"); TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable; MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject; // Put a precedence constraint between the tasks. PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable); pcTasks.Value = DTSExecResult.Success; pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint; // Now adding the data flow components IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New(); sourceDataFlowComponent.Name = "Source Data from Flat file"; // Here is the component class id for flat file source data sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}"; CManagedComponentWrapper managedInstance = sourceDataFlowComponent.Instantiate(); managedInstance.ProvideComponentProperties(); sourceDataFlowComponent. RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID; sourceDataFlowComponent. RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatfileConnectionManager);
managedInstance.AcquireConnections(null); managedInstance.ReinitializeMetaData(); managedInstance.ReleaseConnections(); // Get the destination's default input and virtual input. IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0]; // Here I dont find any columns at all..why?? // Now adding the data flow components IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New(); destinationDataFlowComponent.Name = "Destination Oledb compoenent"; // Here is the component class id for Oledvb data destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}"; CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.Instantiate(); managedOleInstance.ProvideComponentProperties(); destinationDataFlowComponent. RuntimeConnectionCollection[0].ConnectionManagerID = oledbConnectionManager.ID; destinationDataFlowComponent. RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oledbConnectionManager); // Set the custom properties. managedOleInstance.SetComponentProperty("AccessMode", 2); managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]"); managedOleInstance.AcquireConnections(null); managedOleInstance.ReinitializeMetaData(); // Throws exception managedOleInstance.ReleaseConnections(); // Create the path. IDTSPath90 path = dataFlowTask.PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0], destinationDataFlowComponent.InputCollection[0]); // Get the destination's default input and virtual input. IDTSInput90 input = destinationDataFlowComponent.InputCollection[0]; IDTSVirtualInput90 vInput = input.GetVirtualInput(); // Iterate through the virtual input column collection. foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection) { managedOleInstance.SetUsageType( input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY); } DTSExecResult res = pkg.Execute(); } public ConnectionManager CreateOLEDBConnection(Package p) { ConnectionManager ConMgr; ConMgr = p.Connections.Add("OLEDB"); ConMgr.ConnectionString = "Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"; ConMgr.Name = "SSIS Connection Manager for Oledb"; ConMgr.Description = "OLE DB connection to the Test database."; return ConMgr; } public ConnectionManager CreateFileConnection(Package p) { ConnectionManager connMgr; connMgr = p.Connections.Add("FLATFILE"); connMgr.ConnectionString = @"D:MyCSVFile.csv"; connMgr.Name = "SSIS Connection Manager for Files"; connMgr.Description = "Flat File connection"; connMgr.Properties["Format"].SetValue(connMgr, "Delimited"); connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine); return connMgr; }
I build my SQL statement with these values like so: SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2
The problem I am having is when there are multiple values of the same type in the list box. Say: lstCriteria.items(1).value = "COMPANY = 'foo'" lstCriteria.items(2).value = "DAY= 2" lstCriteria.items(1).value = "COMPANY = 'moo'"
My employer wants this to be valid, but I am having a tough time coming up with a solution.
I know that my SQL statement needs to now read: SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2 OR COMPANY = 'poo' AND DAY = 2
I have code set up to read the values of each list box item up to the "=". And I know that I need to compair this value with the others in the list box...but I am not running into any good solutions.
Hi all this is my code and i find it in microsoft's site if i run it with sql server connection it works but if i try to use it with sql express it give me this error: CREATE FILE encountered operating system error 5(access denied) while attempting to open or create the physical file 'c://mydatabase.mdf' it seems as a permission error but it isn't. I have to set something in sql express while in sql server it is already setted?
static void WriteDB()
{
String str;
//sql server connection
SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
//sql express connection SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
Does anyone have any examples of programmatically creating a Transformation Script Component (or Source/Destination) in the dataflow? I have been able to create other Transforms for the dataflow like Derived Column, Sort, etc. but for some reason the Script Component doesn't seem to work the same way.
I have done it as below trying many ways to get the componentClassId including the AssemblyQualifiedname & the GUID as well. No matter, what I do, when it hits the ProvideComponentProperties, it get Exception from HRESULT: 0xC0048021
Does anyone know how to create a Source Script Component programmatically. I can only seem to create a Transformation Script Component. I have this:
PipeLineWrapper.IDTSComponentMetaData90 sourceComponent = ((dataflowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
Below is C# code used to create a FuzzyLookup SSIS package programmatically. It does 95% of what I need it to. The only thing missing that I cannot figure out is how to take a Fuzzy Lookup Input column (OLE DB Output Column) and make it "pass through" the fuzzy lookup component to the OLE DB Destination. In the example below, that means I need the QuarantinedEmployeeId to make it into the destination.
Look in the "Test Dependencies" region below to get instructions and scripts used to set assembly references, create the sample tables used for this example, and insert test data.
Can anyone help me get past this last hurdle? You will see at the end of my Fuzzy Lookup region a bunch of commented out code that I've used to try to accomplish this last problem.
Code Block using Microsoft.SqlServer.Dts.Runtime; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; namespace CreateSsisPackage { public class TestFuzzyLookup { public static void Test() { #region Test Dependencies // Assembly references: // Microsoft.SqlServer.DTSPipelineWrap // Microsoft.SQLServer.DTSRuntimeWrap // Microsoft.SQLServer.ManagedDTS // First create a database called TestFuzzyLookup // Next, create tables: //SET ANSI_NULLS ON //GO //SET QUOTED_IDENTIFIER ON //GO //CREATE TABLE [dbo].[EmployeeMatch]( // [RecordId] [int] IDENTITY(1,1) NOT NULL, // [EmployeeId] [int] NOT NULL, // [QuarantinedEmployeeId] [int] NOT NULL, // [_Similarity] [real] NOT NULL, // [_Confidence] [real] NOT NULL, // CONSTRAINT [PK_EmployeeMatch] PRIMARY KEY CLUSTERED //( // [RecordId] ASC //)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] //) ON [PRIMARY] //GO //SET ANSI_NULLS ON //GO //SET QUOTED_IDENTIFIER ON //GO //CREATE TABLE [dbo].[QuarantinedEmployee]( // [QuarantinedEmployeeId] [int] IDENTITY(1,1) NOT NULL, // [QuarantinedEmployeeName] [varchar](50) NOT NULL, // CONSTRAINT [PK_QuarantinedEmployee] PRIMARY KEY CLUSTERED //( // [QuarantinedEmployeeId] ASC //)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] //) ON [PRIMARY] //GO //SET ANSI_NULLS ON //GO //SET QUOTED_IDENTIFIER ON //GO //CREATE TABLE [dbo].[Employee]( // [EmployeeId] [int] IDENTITY(1,1) NOT NULL, // [EmployeeName] [varchar](50) NOT NULL, // CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED //( // [EmployeeId] ASC //)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] //) ON [PRIMARY] // Next, insert test data //insert into employee values ('John Doe') //insert into employee values ('Jane Smith') //insert into employee values ('Ryan Johnson') //insert into quarantinedemployee values ('John Dole') #endregion Test Dependencies #region Create Package // Create a new package Package package = new Package(); package.Name = "FuzzyLookupTest"; // Add a Data Flow task TaskHost taskHost = package.Executables.Add("DTS.Pipeline") as TaskHost; taskHost.Name = "Fuzzy Lookup"; IDTSPipeline90 pipeline = taskHost.InnerObject as MainPipe; // Get the pipeline's component metadata collection IDTSComponentMetaDataCollection90 componentMetadataCollection = pipeline.ComponentMetaDataCollection; #endregion Create Package #region Source // Add a new component metadata object to the data flow IDTSComponentMetaData90 oledbSourceMetadata = componentMetadataCollection.New(); // Associate the component metadata object with the OLE DB Source Adapter oledbSourceMetadata.ComponentClassID = "DTSAdapter.OLEDBSource"; // Instantiate the OLE DB Source adapter IDTSDesigntimeComponent90 oledbSourceComponent = oledbSourceMetadata.Instantiate(); // Ask the component to set up its component metadata object oledbSourceComponent.ProvideComponentProperties(); // Add an OLE DB connection manager ConnectionManager connectionManagerSource = package.Connections.Add("OLEDB"); connectionManagerSource.Name = "OLEDBSource"; // Set the connection string connectionManagerSource.ConnectionString = "Data Source=localhost;Initial Catalog=TestFuzzyLookup;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"; // Set the connection manager as the OLE DB Source adapter's runtime connection IDTSRuntimeConnection90 runtimeConnectionSource = oledbSourceMetadata.RuntimeConnectionCollection["OleDbConnection"]; runtimeConnectionSource.ConnectionManagerID = connectionManagerSource.ID; // Tell the OLE DB Source adapter to use the source table oledbSourceComponent.SetComponentProperty("OpenRowset", "QuarantinedEmployee"); oledbSourceComponent.SetComponentProperty("AccessMode", 0); // Set up the connection manager object runtimeConnectionSource.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerSource); // Establish the database connection oledbSourceComponent.AcquireConnections(null); // Set up the column metadata oledbSourceComponent.ReinitializeMetaData(); // Release the database connection oledbSourceComponent.ReleaseConnections(); // Release the connection manager runtimeConnectionSource.ReleaseConnectionManager(); #endregion Source #region Fuzzy Lookup // Add a new component metadata object to the data flow IDTSComponentMetaData90 fuzzyLookupMetadata = componentMetadataCollection.New(); // Associate the component metadata object with the Fuzzy Lookup object fuzzyLookupMetadata.ComponentClassID = "DTSTransform.BestMatch.1"; // Instantiate IDTSDesigntimeComponent90 fuzzyLookupComponent = fuzzyLookupMetadata.Instantiate(); // Ask the component to set up its component metadata object fuzzyLookupComponent.ProvideComponentProperties(); // Add an OLE DB connection manager ConnectionManager connectionManagerFuzzy = package.Connections.Add("OLEDB"); connectionManagerFuzzy.Name = "OLEDBFuzzy"; // Set the connection string connectionManagerFuzzy.ConnectionString = "Data Source=localhost;Initial Catalog=TestFuzzyLookup;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"; // Set the connection manager as the fuzzy lookup component's runtime connection IDTSRuntimeConnection90 runtimeConnectionFuzzy = fuzzyLookupMetadata.RuntimeConnectionCollection["OleDbConnection"]; runtimeConnectionFuzzy.ConnectionManagerID = connectionManagerFuzzy.ID; // Set up the connection manager object runtimeConnectionFuzzy.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerFuzzy); // Establish the database connection fuzzyLookupComponent.AcquireConnections(null); // Set up the external metadata column fuzzyLookupComponent.ReinitializeMetaData(); // Release the database connection fuzzyLookupComponent.ReleaseConnections(); // Release the connection manager runtimeConnectionFuzzy.ReleaseConnectionManager(); // Get the standard output of the OLE DB Source adapter IDTSOutput90 oledbSourceOutput = oledbSourceMetadata.OutputCollection["OLE DB Source Output"]; // Get the input of the Fuzzy Lookup component IDTSInput90 fuzzyInput = fuzzyLookupMetadata.InputCollection["Fuzzy Lookup Input"]; // Create a new path object IDTSPath90 path = pipeline.PathCollection.New(); // Connect the source to Fuzzy Lookup path.AttachPathAndPropagateNotifications(oledbSourceOutput, fuzzyInput); // Get the output column collection for the OLE DB Source adapter IDTSOutputColumnCollection90 oledbSourceOutputColumns = oledbSourceOutput.OutputColumnCollection; // Get the external metadata column collection for the fuzzy lookup component IDTSExternalMetadataColumnCollection90 externalMetadataColumns = fuzzyInput.ExternalMetadataColumnCollection; // Get the virtual input for the fuzzy lookup component IDTSVirtualInput90 virtualInput = fuzzyInput.GetVirtualInput(); // Loop through output columns and relate columns that will be fuzzy matched on foreach (IDTSOutputColumn90 outputColumn in oledbSourceOutputColumns) { IDTSInputColumn90 col = fuzzyLookupComponent.SetUsageType(fuzzyInput.ID, virtualInput, outputColumn.LineageID, DTSUsageType.UT_READONLY); if (outputColumn.Name == "QuarantinedEmployeeName") { // column name is one of the columns we'll match with fuzzyLookupComponent.SetInputColumnProperty(fuzzyInput.ID, col.ID, "JoinToReferenceColumn", "EmployeeName"); fuzzyLookupComponent.SetInputColumnProperty(fuzzyInput.ID, col.ID, "MinSimilarity", 0.6m); // set to be fuzzy match (not exact match) fuzzyLookupComponent.SetInputColumnProperty(fuzzyInput.ID, col.ID, "JoinType", 2); } } fuzzyLookupComponent.SetComponentProperty("MatchIndexOptions", 1); fuzzyLookupComponent.SetComponentProperty("MaxOutputMatchesPerInput", 100); fuzzyLookupComponent.SetComponentProperty("ReferenceTableName", "Employee"); fuzzyLookupComponent.SetComponentProperty("WarmCaches", true); fuzzyLookupComponent.SetComponentProperty("MinSimilarity", 0.6); IDTSOutput90 fuzzyLookupOutput = fuzzyLookupMetadata.OutputCollection["Fuzzy Lookup Output"]; // add output columns that will simply pass through from the reference table (Employee) IDTSOutputColumn90 outCol = fuzzyLookupComponent.InsertOutputColumnAt(fuzzyLookupOutput.ID, 0, "EmployeeId", ""); outCol.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0); fuzzyLookupComponent.SetOutputColumnProperty(fuzzyLookupOutput.ID, outCol.ID, "CopyFromReferenceColumn", "EmployeeId");
// add output columns that will simply pass through from the oledb source (QuarantinedEmployeeId) //IDTSOutput90 sourceOutputCollection = oledbSourceMetadata.OutputCollection["OLE DB Source Output"]; //IDTSOutputColumnCollection90 sourceOutputCols = sourceOutputCollection.OutputColumnCollection; //foreach (IDTSOutputColumn90 outputColumn in sourceOutputCols) //{ // if (outputColumn.Name == "QuarantinedEmployeeId") // { // IDTSOutputColumn90 col = fuzzyLookupComponent.InsertOutputColumnAt(fuzzyLookupOutput.ID, 0, outputColumn.Name, ""); // col.SetDataTypeProperties( // outputColumn.DataType, outputColumn.Length, outputColumn.Precision, outputColumn.Scale, outputColumn.CodePage); // //fuzzyLookupComponent.SetOutputColumnProperty( // // fuzzyLookupOutput.ID, col.ID, "SourceInputColumnLineageId", outputColumn.LineageID); // } //}
// add output columns that will simply pass through from the oledb source (QuarantinedEmployeeId) //IDTSInput90 fuzzyInputCollection = fuzzyLookupMetadata.InputCollection["Fuzzy Lookup Input"]; //IDTSInputColumnCollection90 fuzzyInputCols = fuzzyInputCollection.InputColumnCollection; //foreach (IDTSInputColumn90 inputColumn in fuzzyInputCols) //{ // if (inputColumn.Name == "QuarantinedEmployeeId") // { // IDTSOutputColumn90 col = fuzzyLookupComponent.InsertOutputColumnAt(fuzzyLookupOutput.ID, 0, inputColumn.Name, ""); // col.SetDataTypeProperties( // inputColumn.DataType, inputColumn.Length, inputColumn.Precision, inputColumn.Scale, inputColumn.CodePage); // fuzzyLookupComponent.SetOutputColumnProperty( // fuzzyLookupOutput.ID, col.ID, "SourceInputColumnLineageId", inputColumn.LineageID); // } //} #endregion Fuzzy Lookup #region Destination // Add a new component metadata object to the data flow IDTSComponentMetaData90 oledbDestinationMetadata = componentMetadataCollection.New(); // Associate the component metadata object with the OLE DB Destination Adapter oledbDestinationMetadata.ComponentClassID = "DTSAdapter.OLEDBDestination"; // Instantiate the OLE DB Destination adapter IDTSDesigntimeComponent90 oledbDestinationComponent = oledbDestinationMetadata.Instantiate(); // Ask the component to set up its component metadata object oledbDestinationComponent.ProvideComponentProperties(); // Add an OLE DB connection manager ConnectionManager connectionManagerDestination = package.Connections.Add("OLEDB"); connectionManagerDestination.Name = "OLEDBDestination"; // Set the connection string connectionManagerDestination.ConnectionString = "Data Source=localhost;Initial Catalog=TestFuzzyLookup;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"; // Set the connection manager as the OLE DBDestination adapter's runtime connection IDTSRuntimeConnection90 runtimeConnectionDestination = oledbDestinationMetadata.RuntimeConnectionCollection["OleDbConnection"]; runtimeConnectionDestination.ConnectionManagerID = connectionManagerDestination.ID; // Tell the OLE DB Destination adapter to use the destination table oledbDestinationComponent.SetComponentProperty("OpenRowset", "EmployeeMatch"); oledbDestinationComponent.SetComponentProperty("AccessMode", 0); // Set up the connection manager object runtimeConnectionDestination.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerDestination); // Establish the database connection oledbDestinationComponent.AcquireConnections(null); // Set up the external metadata column oledbDestinationComponent.ReinitializeMetaData(); // Release the database connection oledbDestinationComponent.ReleaseConnections(); // Release the connection manager runtimeConnectionDestination.ReleaseConnectionManager(); // Get the standard output of the fuzzy lookup componenet IDTSOutput90 fuzzyLookupOutputCollection = fuzzyLookupMetadata.OutputCollection["Fuzzy Lookup Output"]; // Get the input of the OLE DB Destination adapter IDTSInput90 oledbDestinationInput = oledbDestinationMetadata.InputCollection["OLE DB Destination Input"]; // Create a new path object IDTSPath90 ssisPath = pipeline.PathCollection.New(); // Connect the source and destination adapters ssisPath.AttachPathAndPropagateNotifications(fuzzyLookupOutputCollection, oledbDestinationInput); // Get the output column collection for the OLE DB Source adapter IDTSOutputColumnCollection90 fuzzyLookupOutputColumns = fuzzyLookupOutputCollection.OutputColumnCollection; // Get the external metadata column collection for the OLE DB Destination adapter IDTSExternalMetadataColumnCollection90 externalMetadataCols = oledbDestinationInput.ExternalMetadataColumnCollection; // Get the virtual input for the OLE DB Destination adapter. IDTSVirtualInput90 vInput = oledbDestinationInput.GetVirtualInput(); // Loop through our output columns foreach (IDTSOutputColumn90 outputColumn in fuzzyLookupOutputColumns) { // Add a new input column IDTSInputColumn90 inputColumn = oledbDestinationComponent.SetUsageType(oledbDestinationInput.ID, vInput, outputColumn.LineageID, DTSUsageType.UT_READONLY); // Get the external metadata column from the OLE DB Destination // using the output column's name IDTSExternalMetadataColumn90 externalMetadataColumn = externalMetadataCols[outputColumn.Name]; // Map the new input column to its corresponding external metadata column. oledbDestinationComponent.MapInputColumn(oledbDestinationInput.ID, inputColumn.ID, externalMetadataColumn.ID); } #endregion Destination // Save the package Application application = new Application(); application.SaveToXml(@"c:TempTestFuzzyLookup.dtsx", package, null); } } }
I have been stuck with this problem since few days, need help regarding the same. I am enclosing the problem description and possible solutions that I have found.
Can anyone please help me out here?
Thanks and regards, Virat
Problem Description:
I have a requirement for which I have created a data driven subscription in SQL Server 2005, the whole thing works like this:
I have a report on Report Server which executes a stored procedure to get its parameters; then it calls another stored procedure to get data for the report; then it creates the report and copies it to a file share. This is done using data driven subscription and the time set for repeating this process is 5 minutes.
You can assume that following are working fine:
1. I have deployed the report on the Report Manager (Uploaded the report, created a data source, linked the report to data source) - manually, the report works fine.
2. Created a data driven subscription.
3. The data driven subscription calls a stored procedure, say GetReportParameters which returns all the parameters required for the report to execute.
4. The Report Manager executes the report by calling a stored procedure, say GetReportData with the parameters provided by GetReportParameters stored procedure; after it has generated the report file (PDF) is copied to a file share.
For each row that GetReportParameters stored procedure returns a report (PDF file) will be created and copied to file share.
Now, my question is
1. How to I get a notification that this file was successfully created or an error occurred? 2. The only message that reporting service shows on 'Report Manager > My Subscriptions' is something like "Done: 5 processed of 10 total; 2 errors." How do I find out which record was processed successfully and which ones resulted in an error?
Based on above results (success or failure), I have to perform further operations.
Solutions or Work around that I have found:
1. Create a windows service which will monitor the file share folder and look for the file name (each record has a unique file name) for the reports that were picked up for PDF creation. If the file is not found, this service will report an error. Now, there's a glitch there; if a report takes very long time to execute it will also be reported as error (i.e. when this service checks for the PDF file, the report was currently being generated). So, I can't go with this solution.
2. I have also looked at following tables on ReportServer database:
a. Catalog - information regarding all the reports, folders, data source information, etc. b. Subscriptions - all the subscriptions information. c. ExecutionLog - information regarding execution of the subscriptions and the also manual execution of reports. d. Notifications - information regarding the errors that occurred during subscription execution.
For this solution, I was thinking of doing a windows service which will monitor these tables and do further operations as required.
This looks like most feasible solution so far.
3. Third option is to look at DeliveryExtensions but in that case I will have to manually call SSRS APIs and will have to manage report invocation and subscription information. What is your opinion on this?
My environment details:
Windows XP SP2
SQL Server 2005
Reporting Services 2005
Please let me know if I am missing something somewhere...
Hello All im developing web application in VS2005(C#) while Creating SQLDATASource for Gridview im getting Following error cannot get web application service Please help ME Regards Balagangadharan.R
I am able to connect but when I try to use the advanced sql generation options the two check boxes are non enabled (generate insert, update, and delete statementsuse optimistic concurrencywhat is happening the user id has permissions to update/delete/select from the selected table
What is the C# code I use to do this? I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
i am using visual web developer 2005 with SQL Express 2005 with VB as the code behindi have one database and three tables in itfor manipulating each table i am using separate SqlDataSource() is it sufficient to use one SqlDataSource() for manipulating all the three tables ? i am manipulating all the tables in the same page only please help me
I need to programmatically backup a database in SQL Server Express. I actually also need to programmatically restore it from a backup file. How can I do this programmatically? I know how to do simple ADO commands for simple db operations, but backup and restore sound like "meta" commands to me, and I don't know where to begin from.
I'm building SSIS packages through code and I would like to set the properties of some custom tasks (not data flow tasks) to expressions. I've done some searches but turned up nothing. This is the only thing I'm hitting a brick wall on at the moment; Books Online has been excellent in detailing how to create packages via code up to this point.
For the sake of argument, let's say I want to set the SqlStatementSource property of an Execute SQL task to this value:
"INSERT INTO [SomeTable] VALUES (NEWID(), '" + @[User:omeStringVariable] + "')"
Ok. So I have this ASP.NET page and I've programmatically taken a report from the report server and rendered it in PDF. Now I would like to take this a step further and save the report as a pdf document on the local machine.
So at this point I have a byte array representing the document, now how would I save this as a pdf on the local machine? I'm unaware of an ASP Response method to allow this and I'm unaware of a SSRS ReportingService method, but as I said I'm unaware...
