Hello, I am using a multicast with 5 outputs, I attempting to pass about four thousand rows to different destinations. However, upon execution only two of the outputs send the rows to the destination. After deleting the multicast and reinserting it, different destinations received the data. The number of destinations is also not consistant, sometimes 3 work and sometimes only 1 works.
Thank you.
I split a source into two. I get an Id from one after doing couple of lookups and maxId from other after doing a lookup and and an Aggregate. I want to compare Id and max Id in the same data flow. How can I do that please?
View 10 Replies View RelatedHello,
I have a data-flow containing 150000 rows that is taking a long-g-g-g- time to run.
The bottleneck seems to be a SORT component. The SORT is immediately preceded by a MULTICAST.
Now, all 150000 rows have gone down the other output from my MULTICAST all the way to the destinaton, admittedly very slowly. My SORT is still caching the 150000 rows.
I'm wondering if the MULTICAST is causing a problem. Does each output fro the MULTICAST have its own buffer?
Can an asynch component that caches all the data (e.g. SORT) cause backpressure on the MULTICAST that might cause problems.
As I write, the package has been running 20 mins and the SORT component still has not cached all the rows.
Any clues would be welcome.
cheers
Jamie
I have a dataflow where each row passes through about 20 validations. This data flow could contain 200,000 to 20 million rows. Initially I set it up with a multicast component sending data each of the validations.
This got to be pretty messy.
Then I thought about using several multicast to subdivide the validations and make the data flow easier to follow. I tested using two multicasts to see if it would affect the performance of the dataflow. Results were inconclusive.
Before I continue adding and testing different multicast configurations, I thought I would check here to see if anyone else has tried and measured this?
Thanks for any insight you can provide.
I have to do various controls on a dataset - I created a multicast. After performing controls (one control per copy), I merge my (7) multicasted datasets using a Union All transformation. The problem I'm having are the duplicate rows created by merging the multicast copies.
How do I get rid of the duplicates? Is the Sort Transformation the solution by setting the option Remove rows with duplicate sort values to True? I have a unique key by which I'm able to discard the duplicates correctly. Are there any other ways (at a Union All level)? Is there sth like Union and Union All like in SQL?
I'm working on my 1st integration serv. project and it seems that more I work more questions I have. Shoudn't be the opposite? Thank you for the help.
Wondering how this is handled by Multicast component. If multiple copies are created in the memory and the size of the dataset is large, this could cause some performance problems. Any thoughts? TIA
View 8 Replies View RelatedI need to send the output of a report generated, automatically through email as an attachment to some customer.Is it possible?Can anyone help?
View 1 Replies View RelatedConsidering email is setup, how would you send emails by scanning errorlog for any issues. Also, how to send program outputs using email.
Thanks
Can someone please clarify:
If you have a data file, and you only want CERTAIN rows to pass to the destination, ie) a table
and you are using a script task to accomplish this,
is this a synchronous or asynchronous transformation?
Q. And how do you assign the values to the output? Do you have to create output columns, or not?
I am very very confused right now. I can't seem to find a decent answer to what is a very basic question either in my SSIS book or in the documenation. Perhaps it is so basic, that the question doesn't seem valid? I don't know. But I just don't understand this at all.
Thank you
Hi There,
I have been struggling day and night with the creation of a store procedure due to not being able to retieve the rows I need for SUM and AVG functions.
I have two tables ('actions' & 'incident_types') which both have a score value for each record. In my database, I have reports that contain both action codes and incident_type codes based on a personnel_code.
In simple terms I'm trying to do the following:-
For all reports, find each personnel member and thier attached incident_types and action codes and then, for each score from the actions and incident_types tables, create the SUM of the 'combined' scores.
I have successfully retrieved the output for the scores individually but I need the TOP(10) of the combined output.....
I'm currently using to seperate queries as follows :--
--This gives me the incident type output...... Creating the 'Volume' column which is the total Score for incidetn_types
SELECT Top (@Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(incident_types.type_score) as 'Volume', avg(incident_types.type_score) as 'Average' INTO 'Incident_Scores' from Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE (report_header.report_date >= @fromDate and report_header.report_date <= @toDate)
AND (report_header.report_time >= @fromTime and report_header.report_time <= @toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@recordStatus
group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname
-- I then use the following to get the total of all action scores, again in the 'Volume' column
SELECT Top (@Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(actions.action_score) as 'Volume', AVG(actions.action_score) as 'Average' from profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE (report_header.report_date >= @fromDate and report_header.report_date <= @toDate)
AND (report_header.report_time >= @fromTime and report_header.report_time <= @toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@recordStatus
AND profile.record_status=@recordStatus
group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname
So my question is - How can I get the sum(incident_types.type_score) + sum(actions.action_score) and then get the TOP(10) rows?
Is it possible to output these 2 result to a new table and then join the new tables?
Thanks for any assistance, this is really draining me at the moment..... :-/
Hi Dear All!
I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.
I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.
Please help me if I can look similar packages performing?
Thank you!!
I have a stored procedure that I just need to return the output to my program.It is a Select All type statement.I will post my vb code that works when I use both inputs and outputs but not for all output procedure...I dont get it.
Here is the Stored Procedure.....
CREATE procedure dbo.IDXAppt_Settings_NET
(
@SQLADD nvarchar(15)Output,
@SQLDatabase nvarchar(20)Output,
@SQLLogin nvarchar(20)Output,
@SQLPass nvarchar(20)Output
)
as
select
@SQLADD=SQLAddress,
@SQLDatabase=SQLDatabase,
@SQLLogin=SQLLogin,
@SQLPass=SQLPassword
from
Clinic_Settings
GO
Here is the Vb.Net Code........
To retrieve the elements that does not give me an error just gives me no data....
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim consql As New SqlConnection("server=myserver,database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand
Dim parmSQLAddress As SqlParameter
Dim parmDatabase As SqlParameter
Dim parmLogin As SqlParameter
Dim parmSqlPass As SqlParameter
Dim strtest As String
Dim db As String
Dim login As String
Dim pass As String
cmdsql = New SqlCommand("Appt_Settings_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure
parmDatabase = cmdsql.Parameters.Add("@SQLData", SqlDbType.NVarChar)
parmDatabase.Size = 20
parmDatabase.Direction = ParameterDirection.Output
db = cmdsql.Parameters("@SQLData").Value
parmLogin = cmdsql.Parameters.Add("@SQLLogin", SqlDbType.NVarChar)
parmLogin.Size = 20
parmLogin.Direction = ParameterDirection.Output
login = cmdsql.Parameters("@SQLLogin").Value
parmSqlPass = cmdsql.Parameters.Add("@SQLPass", SqlDbType.NVarChar)
parmSqlPass.Size = 20
parmSqlPass.Direction = ParameterDirection.Output
pass = cmdsql.Parameters("@SQLPass").Value
parmSQLAddress = cmdsql.Parameters.Add("@SQLADD", SqlDbType.NVarChar)
parmSQLAddress.Size = 15
parmSQLAddress.Direction = ParameterDirection.Output
strtest = cmdsql.Parameters("@SQLADD").Value
consql.Open()
cmdsql.ExecuteNonQuery()
Label1.Text = strtest
End Sub
Hi everyone!
I have an stordprocedure that returns a resultset , an output value and an return value.
I invoked this procedure using an sqlcommand object ( by ExecudeReader method ) and I filled a SqlDataReader object by the resultset.
dr = cmd.ExecudeReader();
but I cant reach its output variable and its return value (cmd.parameters["@ret"].value).
an exception raises : object refrence error!
what is wrong in this approach ?
please help me.
Obviously Excel is the tool of choice for most people but with it's limited ability to leverage RAM (32 bit) and it's limitation with rows at just over 1 million what other choices do we have for viewing data?
My bosses boss created several OLAP universes and they seems to fly a lot fast than regular relational database. This still doesn't work with the fact the data can't be worked with unless you have a strong front end that can handle processing all those rows.
I have a table with email addresses and CC_Flag.
Email    | CC_Flag
xxxx          0
yyyy          1
zzzz          1
Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag. Is it possible to fill two variables from a single SQl Task.
Hi,
SQL Server 2005, sp2
What I am trying to do is very simple. I just want to run 2 stored procedures, then have the output from both sp's written to a single file.
I created a Data Flow task, then put 2 OLE DB Source adapters in the Data Flow task, one for each stored procedure.
Next, I dropped a Flat File destination object onto the page. However, I can only connect ONE arrow from one of the OLE DB Source adapters to the Flat File destination. It won't let me connect both.
What am I doing wrong here? How can I accomplish what I am trying to do here?
Thanks much
Hi,
I wonder if someone might be able to help me with scripting a script component. I'd like to include error redirection of rows within my script.
If the conversion of any of my inputs fail i'd like to catch the error and then just output all values to another output path. The output path will just take the input values without converting them from string data types and output them to an error table.
In the script i imagine i would use try catch statements and if it fails then set the output. I am not entirely sue as to how to go about switching between outputs though.
Any help on this matter would be greatfully recieved.
Cheers,
Grant
If you have an output that is not synchronous with the input what is the best way of processing the data.
I am currently using a generic queue, and a custom class. I am creating an instance of the class in the ProcessINputRow and then adding it to the Queue.
The CreateNewOutputRows Dequeues the class instances and creates buffer rows.
Is there a better solution?
Can someone please point me to one or more examples of a Source component that has two or more outputs?
I wrote a source, which works with a single output, but after adding a second output, I see no rows there. I've single-stepped the code in the debugger, and it looks like it should be adding rows to the second output, but the downstream component never sees any.
Thanks.
On URL: http://technet.microsoft.com/en-us/library/ms135931.aspx among other things there is an VB example for "Creating and Configuring Output Columns" of Custom Transformation Component with Asynchronous Outputs:
Public Overrides Sub OnInputPathAttached(ByVal inputID As Integer)
Dim input As IDTSInput90 = ComponentMetaData.InputCollection.GetObjectByID(inputID)
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection(0)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()
For Each vCol As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
Dim outCol As IDTSOutputColumn90 = output.OutputColumnCollection.New()
outCol.Name = vCol.Name
outCol.SetDataTypeProperties(vCol.DataType, vCol.Length, vCol.Precision, vCol.Scale, vCol.CodePage)
Next
End Sub
When I copy-paste given code into ScriptMain of Ascynchronous Script Transformation Component I receive error message "sub 'OnInputPathAttached' cannot be declared 'Overrides' because it does not override a sub in a base class."
Hi,
I have a string almost 11006 length.. when i try to send back from SQLCLR procedure
it says cannot send ..
here is Exception Text "Message length 11060 exceeds maximum length supported of 4000."
Max limit to send a string using pipe is 4000
How I can send a string which is large in size than 4000.
Thanks
Hello All,
Hopefully someone out there will have an idea as this is driving me nuts.
I want to send a dynamic files in attachment files ny send mail task that file name has change follow datetime.
I try to use the expression but I can't use it.
please tell me for this problem.
Any suggestions appreciated,
Thanks.
This is my procedure which I need to send the Data into Database, But it does not generate any error and it does not send the information into Database, May I have done wrong, any one to look on that please.
Sub Add_To_Cart(ByVal Src As Object, ByVal Args As EventArgs)
Dim FVProductID As Label = FormView1.FindControl("ProductID")
Dim FVProductName As Label = FormView1.FindControl("ProductName")Dim FVProductPrice As Label = FormView1.FindControl("ProductPrice")
Dim DBConnection As SqlConnection
Dim DBCommand As SqlCommand
Dim sql As String
Dim SQLAddString As String
DBConnection = New SqlConnection("Data Source=MANDARISQLEXPRESS;Initial Catalog=SHOES;Integrated Security=True")
DBConnection.Open()
If Not Session("OrderID") Is Nothing Then
sql = "SELECT Count(*) FROM ShoppingCart " & _
"WHERE OrderID = '" & CType(Session("OrderID"), String) & "' " _
& "AND ProductID = '" & FVProductID.Text & "'"
DBCommand = New SqlCommand(sql, DBConnection)
If DBCommand.ExecuteScalar() = 0 Then
SQLAddString = "INSERT INTO ShoppingCart (OrderID, ProductID, OrderDate, ProductName, ProductQnty, ProductPrice) VALUES (" & _
"'" & Session("OrderID") & "', " & _"'" & FVProductID.Text & "', " & _
"'" & Today & "', " & _"'" & FVProductName.Text & "', " & _
"'" & FVProductPrice.Text & "', 1)"DBCommand = New SqlCommand(SQLAddString, DBConnection)
DBCommand.ExecuteNonQuery()
End If
End If
DBConnection.Close()
Src.Text = "Item Added"Src.ForeColor = Color.FromName("#990000")
Src.BackColor = Color.FromName("#E0E0E0")
Src.Font.Bold = True
End Sub
Sub Add_To_Cart(ByVal Src As Object, ByVal Args As EventArgs)
Dim FVProductID As Label = FormView1.FindControl("ProductID")
Dim FVProductName As Label = FormView1.FindControl("ProductName")
Dim FVProductPrice As Label = FormView1.FindControl("ProductPrice")
Dim DBConnection As SqlConnection
Dim DBCommand As SqlCommand
Dim sql As String
Dim SQLAddString As String
DBConnection = New SqlConnection("Data Source=MANDARISQLEXPRESS;Initial Catalog=SHOES;Integrated Security=True")
DBConnection.Open()
If Not Session("OrderID") Is Nothing Then
sql = "SELECT Count(*) FROM ShoppingCart " & _
"WHERE OrderID = '" & CType(Session("OrderID"), String) & "' " _
& "AND ProductID = '" & FVProductID.Text & "'"
DBCommand = New SqlCommand(sql, DBConnection)
If DBCommand.ExecuteScalar() = 0 Then
SQLAddString = "INSERT INTO ShoppingCart (OrderID, ProductID, OrderDate, ProductName, ProductQnty, ProductPrice) VALUES (" & _
"'" & Session("OrderID") & "', " & _
"'" & FVProductID.Text & "', " & _
"'" & Today & "', " & _
"'" & FVProductName.Text & "', " & _
"'" & FVProductPrice.Text & "', 1)"
DBCommand = New SqlCommand(SQLAddString, DBConnection)
DBCommand.ExecuteNonQuery()
End If
End If
DBConnection.Close()
Src.Text = "Item Added"
Src.ForeColor = Color.FromName("#990000")
Src.BackColor = Color.FromName("#E0E0E0")
Src.Font.Bold = True
End Sub
I need to send an email for each row in a dataset. The data set will contain the order number. I need to first look up the order detail based on the order number and put it into an e-mail. Should I use the OLE DB command object for this?
View 3 Replies View RelatedI have set up a multiple server merge replication publication using the following:
SQL server running SQL Server 2005 (SQL server ce server tools installed)
Web Server running IIS (Required replication elements installed)
All went smoothly with the installation and I was able to running the diagnostics report from my Pocket PC by browsing the web page url that contains the slqcesa30.dll
http://www.myserver.eu/repdata/sqlcesa30.dll
I then ran the configure web synchronization wizard and setup basic authentication (I am using this purely for test purposes and will reconfigure the security when testing is complete) I then created a user on both the SQL server and the Web Server (identical on both machines) and gave this user the required rights in all objects. I have set up a trial application using visual studio 2005 vb.net on a windows mobile 5 device. When form1 loads the following code is fired:
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
'' Configure the DataAdapter for use in UI binding
cnField = New SqlCeConnection("Data Source =documents and settingsMobileData.sdf;")
daField = New SqlCeDataAdapter("SELECT Id, LatLong, Dateandtimeoffix FROM FieldData", cnField)
'' Configure the update comand
Dim cb As New SqlCeCommandBuilder(daField)
Merge()
End Sub
Private Sub Merge()
Const URL As String =€? http://www.myserver.eu/repdata/sqlcesa30.dll€?
'' URL to Agent
Const InterNetUser As String = "Fielddata" '' InternetUser
Const InternetPassword As String = "Password" '' InternetPassword
Const PublisherServer As String = "MyServer" '' Publisher server
Const PublisherDatabase As String = "Table_Test" '' Publisher Database
Const PublicationName As String = "RepData" '' Publication name
Const SubscriberName As String = "Testing" '' Subscriber name
Const LocalDB As String = "Data Source=documents and settingsMobileData.sdf" ''Connection string to local database
Using rep As New SqlCeReplication(URL, InterNetUser, InternetPassword, PublisherServer, PublisherDatabase, PublicationName, SubscriberName, LocalDB)
Try
If Not System.IO.File.Exists("Documents and SettingsMobileData.sdf") Then
rep.AddSubscription(AddOption.CreateDatabase)
End If
rep.Synchronize()
Catch ex As SqlCeException
DisplaySQLCEErrors(ex)
End Try
End Using
SetupDataSetforUI()
End Sub
When the code executes I obtain the following error:
Index #0
Microsoft SQL Server 2005 Mobile Edition
Error: A request to send data to the computer running IIS has failed for more information see HRESULTS.
I have looked this up and it appears to be to do with certification? Is this correct I am using HTTP not HTTPS?
Any assistance appreciated
joebo
i currently have multiple textboxes which input data, i'm wanting the textbox details to be sent to a database, here's how one of the textboxes look: <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' Visible="False" Width="300px"></asp:TextBox>
the sqldatasource:<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommunityDirConnectionString %>" InsertCommand="INSERT INTO email(Name, Department, Email, Subject, Body) VALUES (@Name, @Department, @Email, @Subject, @Body)" SelectCommand="SELECT email.* FROM email"><InsertParameters><asp:Parameter Name="Name" /><asp:Parameter Name="Department" /><asp:Parameter Name="Email" /><asp:Parameter Name="Subject" /><asp:Parameter Name="Body" /></InsertParameters></asp:SqlDataSource>
i'm wanting it to fire once the user selects a button, but i'm not sure what code needs to be placed behind the button?
I want to post all the data in my company branch office in Lahore to the Head office in Peshawar. This will be done at the end of the day and the two SQL SERVERs synchronize data between both points over internet. what procedure should i use? and how the two computers will know the ip addresses of each other while connecting?
View 3 Replies View RelatedHow yould i loop trought all the records in a table and fetching a specic record that is flagged and sending for each record found a email with that records data to a mail recipient. this should be part of a step in a sql job. PLZ HELP
View 2 Replies View RelatedHi i have a SSIS package, that looks at the @@rowcount in a table and if the rowcount is >= 1 then send the information on to a file.
Now i want to only email my self to say if the file has data in it.
Is there anyway of doing this on the send email task. Can i use an expression ?. I've check and i can't see a away of checking the file size etc.
I have a report that may or may not have data. I have a subscription setup for this report. The subscription has a defined To list. I would like to NOT send the report if there is no data on the report. I did this in SQL 2000 by raising an error from the stored procedure if there was no data. However, that approach does not seem to work with 2005. Is there another way to accomplish this?
View 13 Replies View RelatedI have a job that emails out shipment notifications at the end of the day to our customers. The problem I have is I don't understand why the same email is sending out twice within a minute of each other when the job is only scheduled to run once. If I take the code out of the step and run it in management studio it only emails once. I attached the code for one customer for reference. We are running SQL 2008 on a VM sending to an exchange 2010 server.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>XYZ Company ASN For ' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' </H1>' +
N'<table border="1">' +
N'<tr><th>Vendor</th><th>Delivery Date</th>' +
N'<th>Purchase Order Number</th><th>Item Number</th><th>Item Description</th>' +
N'<th>Quantity Shipped</th><th>UOM</th><th>BOL Number</th>'
[code]....
hi alli have a web application on a web server.and another windows application on another computer.there are databases on each of them.how can i send and receive data between these two applications?please reply me as soon as possibleregards
View 5 Replies View RelatedI have a view which will have some data and mail id.
Each time when new data arrives, i need send the complete row to those mail ids.