Script Component With Multiple Outputs
Sep 25, 2006
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
View 4 Replies
ADVERTISEMENT
Aug 21, 2007
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.
View 7 Replies
View Related
Mar 14, 2006
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?
View 2 Replies
View Related
Oct 26, 2007
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
View 7 Replies
View Related
Jan 23, 2007
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
View 4 Replies
View Related
Jan 14, 2008
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 Related
Apr 10, 2007
Hello!
I'ave got a problem of setting more than one Variable in ReadOnlyVariables Property of ScriptComponent...I provide comma separated list of names ( As described in the help ) byt VS Studio Editor can not be opoened claiming that there is no a variablle with such a name...Looks like it doesn't treat the list as a collection of names...
Please help.
Vladimir
View 4 Replies
View Related
Apr 13, 2000
I 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 Related
Nov 2, 2004
Considering email is setup, how would you send emails by scanning errorlog for any issues. Also, how to send program outputs using email.
Thanks
View 4 Replies
View Related
Jan 3, 2008
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
View 9 Replies
View Related
Sep 24, 2007
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..... :-/
View 8 Replies
View Related
Mar 19, 2004
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
View 2 Replies
View Related
Oct 12, 2004
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.
View 3 Replies
View Related
Nov 30, 2014
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.
View 9 Replies
View Related
Jul 22, 2015
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.
View 8 Replies
View Related
Apr 11, 2007
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.
View 14 Replies
View Related
Mar 13, 2007
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
View 3 Replies
View Related
Oct 22, 2007
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."
View 1 Replies
View Related
Mar 16, 2007
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
View 3 Replies
View Related
Aug 13, 2007
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
View 4 Replies
View Related
Mar 30, 2006
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
Does anyone have any suggestions?
TIA . . . Ed
View 7 Replies
View Related
Apr 27, 2005
Rather than the real code, here's a sample we came up with.
Here's the C# Code:
public class sptest : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
private DataSet dtsData;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
string strSP = "sp_testOutput";
SqlParameter[] Params = new SqlParameter[2];
Params[0] = new SqlParameter("@Input", "Pudding");
Params[1] = new SqlParameter("@Error_Text", "");
Params[1].Direction = ParameterDirection.Output;
try
{
this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);
Label1.Text = Params[0].Value.ToString() + "--Returned Val is" + Params[1].Value.ToString();
}
//catch (System.Data.SqlClient.SqlException ex)
catch (Exception ex)
{
Label1.Text = ex.ToString();
}
}
Here is the stored procedure:
CREATE PROCEDURE [user1122500].[sp_testOutput](@Input nvarchar(76),@Error_Text nvarchar(10) OUTPUT)AS
SET @Error_Text = 'Test'GO
When I run this, it prints up the input variable, but not the output variable.
View 2 Replies
View Related
Nov 27, 2007
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain
Public Sub Main()
Try
Dim xmlDoc As New XmlDocument
xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up"
Catch ex As Exception
MsgBox(ex.Message)
UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
Try
Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
Dim rd As StreamReader = New StreamReader(fileName)
Dim xml As String = rd.ReadToEnd()
Xml = Xml.Replace(invalidChar, String.Empty)
xml = xml.Replace("", String.Empty)
xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
xml = xml.Replace("]]>]]>", "]]>")
MsgBox("replaced")
rd.Close()
Dim wr As StreamWriter = New StreamWriter(fileName)
wr.Write(xml)
wr.Close()
Dim xdoc As XmlDocument = New XmlDocument()
xdoc.Load(fileName)
Catch ex As Exception
UpdateXML(fileName, ex.Message)
End Try
End Sub
End Class
View 4 Replies
View Related
Apr 21, 2006
1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,
2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.
Thanks in advance.
View 1 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
IDÂ key1Â key2Â key3Â key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Mar 30, 2007
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View 11 Replies
View Related
Apr 21, 2015
I have a table with single row like below
 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0Â Â Â | Value1Â Â Â | Value2Â Â Â Â | Value3Â Â Â |Â Value4Â |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _
View 6 Replies
View Related
Jun 16, 2015
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
View 4 Replies
View Related
Aug 17, 2015
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies
View Related
Aug 25, 2015
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.Â
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
View 6 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',Â
@profile_name =
'SQL SMTP',
  Â
[code]....
View 3 Replies
View Related
