Hi
I have a script component task that uses a column (which contains filenames) as input,
my problem is i cant access this in the script ? the column name is "Document"
and i have tried .
Test_var = Me.ProcessInput(186, "fulldoc").Value
Hi
I have a script component task that uses a column (which contains filenames) as input,
my problem is i cant access this in the script ? the column name is "Document"
and i've tried Me.ProcessInput("Document")
I'm trying write a reusable script component that takes data from rows that were rejected from a SQL Destination operation and put them into a common SQL error table.
This script would basically function to take the input columns selected in the script, and build a delimited string, (similar to the 'Flat File Source Error Output' that is contains redirected rows from reading a flat file) and insert this string into a SQL table called 'SourceData' to store errors.
I'm trying to script the component to iterate through all input columns (as selected in the input columns screen) and build a simple string.
Code Block
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Use the incoming error number as a parameter to GetErrorDescription
Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)
Try
Row.ErrorColumnName = ComponentMetaData.InputCollection(0).InputColumnCollection(Row.ErrorColumn).Name
Catch ex As Exception
Row.ErrorColumnName = String.Concat("Column Name retrieval failure. Details", ex.Message)
End Try
'
'Build input data
'
Dim inData As String
For Each inputCol As IDTSInputColumn90 In ComponentMetaData.InputCollection(0).InputColumnCollection
inData = String.Concat(inData, "~", inputCol.Name) 'I don't want the name, but the value.
Next
Row.SourceData = inData
'
End Sub
I've only got as far as iterating the names of columns in the input buffer, but how do i get the values?
The result i'm trying to achieve is :
Selected columns in 'Input Column' screen : Name, Address, Phone
OutPut column 'SourceData' value : Harry~Melbourne~None
I don't want to write the code as:
Code Block
inData = Row.Name
indata = String.Concat(inData,"~",Row.Address)
indata = string.concat(inData,"~",Row.Phone)
as this make my code not very reusable. I've got some tables which are 100+ columns long and I don't wish modify the code too much
I have also tried overriding the ProcessInput() function of the script component to iterate through the buffer columns :
Code Block
Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
MyBase.ProcessInput(InputID, Buffer)
Dim iCnt As Integer = 0
Dim inData As String
If Buffer.ColumnCount > 0 Then
For iCnt = 0 To Buffer.ColumnCount - 1
inData = String.Concat(Buffer.Item(iCnt)) 'Error thrown here: PipelineBuffer has encountered an invalid row index value.
Next
End If
End Sub
but i get an error when i run it.
Please help.
I did make the SQLServer 2005 tutorial, but the LokUp point I see an error "This component has no available input columns". How I can to solve this?
View 6 Replies View RelatedHi,
I have two input felds recieved date from and to fields. I would like to put one checkbox (not one for from field and one for to field) and when you tick the checkbox, I would like to disable both input fields indicating null values for both input fields.
Is it possible?? If yes, how to archieve that?
Thanks a lot in advance,
Leon
Hi all. Can you help me? I'm trying to build a custom component that recieves two datasources (like for instance the union all) . I first started by adding a new IDTSInput90 in the ProvideComponentProperties, but when I tried to use the component I got an error that has very helpful :
===================================
The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)
===================================
Error at Data Flow Task [Replica Transformation [1289]]: System.Runtime.InteropServices.COMException (0xC0048004): Exception from HRESULT: 0xC0048004
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputCollection90.get_Item(Object Index)
at MyCustomSSISComponent.SampleComponentComponent.ProvideComponentProperties()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper)
===================================
Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper)
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)
This is my ProvideComponentProperties:
Code Snippet
public override void ProvideComponentProperties()
{
RemoveAllInputsOutputsAndCustomProperties();
ComponentMetaData.UsesDispositions = true;
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Staging Data";
input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSInput90 input2 = ComponentMetaData.InputCollection.New();
input2.Name = "Replica Data";
input2.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
// Add the output
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "Replica Output";
output.SynchronousInputID = input.ID;
output.ExclusionGroup = 1;
// Add the error output
AddErrorOutput("StagingErrorOutput", input.ID, output.ExclusionGroup);
// Adds columns
AddXmlColumn();
IDTSOutputColumn90 column0 = ComponentMetaData.OutputCollection[1].OutputColumnCollection.New();
column0.Name = m_SyncStatusColumnName;
column0.SetDataTypeProperties(DataType.DT_STR, 1, 0, 0, 1252);
IDTSOutputColumn90 column1 = ComponentMetaData.OutputCollection[2].OutputColumnCollection.New();
column1.Name = m_AS400ImportedDateColumnName;
column1.SetDataTypeProperties(DataType.DT_DATE, 0, 0, 0, 0);
}
Since I'm new to SSIS I'm following Josh's SSIS Xmlify Data Flow Task sample.
Thanks.
Hi,
I have a script component and under input columns, I checked one of the fields I want to use inside the script. It's under the Input 0 in the hierarchical tree inside the inputs and outputs.
How do i use this inside the script?
cherriesh
All in the subject.
View 11 Replies View RelatedThis is very similar to the post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1901076&SiteID=1
I am trying to do this. Basically, if the field I am checking contains a space - not an empty string - I want to assign it an empty string so it will get converted to NULL when it hits the SQL destination. The space character from the source is causing the rows to fail I want to force a NULLin the database as a "workaround" from something I am working on.
I thought I had this set up correctly, but it is not working. I have the field in question as an input field set to ReadWrite
If Row.[COLNAME].ToString = " " Then
Row.[COLNAME] = ""
End If
I am getting the following error:
[Assign District Code [12469]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
I can provide more details if needed.
Thanks for your help.
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!
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)
I have a COM object that is written using Visual Basic 6. This is referenced in a .NET Stored Procedure. But when I execute the stored procedure I get an error:
Msg 6522, Level 16, State 1, Procedure prCalculator_ExecCalc, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'prCalculator_ExecCalc':
System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {844E8165-ABC1-432B-9490-51B1A6D91E71} failed due to the following error: 80040154.
System.Runtime.InteropServices.COMException:
Here is what I do:
1. Compile the VB DLL.
2. Convert into a .NET assembly by importing to a Visual Studio 2005 project. Sign the interop assembly.
3. Register it as an assembly in SQL 2005 server.
4. Create a .NET stored procedure and reference the above assembly. Compile this assembly again in SQL 2005 and create a stored procedure using the assembly. This assembly is also signed.
Please Note:
1. All the assemblies are registered with UNSAFE permissions.
2. They are compiled with COM Visible flag in Visual Studio 2005.
3. This works perfectly on my local SQL Express where I have Visual Basic/VisualStudio 2005 installed.
4. I get this error, when trying on Test Server. I tried to install the VB Runtime on this machine and still does not work.
So, what am I missing? Thanks for your help.
.
I have a series of records that are timestamped and ordered by time ascending. i.e.
Time
TimeToNext
02/07/2007 11:38:22
0:00:21
<--- Need to calculate these
02/07/2007 11:38:43
0:00:22
<--- Need to calculate these
02/07/2007 11:39:06
0:00:00
<--- Need to calculate these
02/07/2007 11:39:06
0:00:36
<--- Need to calculate these
02/07/2007 11:39:42
0:00:28
<--- Need to calculate these
02/07/2007 11:40:10
0:00:26
<--- Need to calculate these
02/07/2007 11:40:36
Last Record
<--- Need to calculate these
I need to calculate the difference in time between each of these records. The flow should go like this (psuedo):
1. Read FirstRecord
2. Read SecondRecord
3. Set FirstRecord.TimeToNext = SecondRecord.Time - FirstRecord.Time
4. Output FirstRecord
Repeat for all rows in buffer (i.e. SecondRecord.TimeToNext = ThirdRecord.Time - SecondRecord.Time)
I am sure there is an easy way to do this with a script component, but I haven't been able to figure it out, and my head hurts from smashing it against my desk.
Any suggestions would be greatly appreciated.
Hi All,
I want to run a loop for all the input columns in the script component. My requirement is, I have nearly 50 columns in the input columns list. For each row and for each column I need to do some operation. How Can I run a loop for each column. Please note in the script component I need to get the column names in the middle for some operations. Please see below.
Process Each Input Row
For each column in Input column list
....
....
If column.Name Starts with "Test" then set NULL to the column value
....
....
End Loop
End Loop for each input row
Can anybody tell me how can I do that.
Regards
Venkat.
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?
I have a requirement to access a lookup table from within an SSIS Transform Script Component
The aim is to eliminate error characters from within the firstname, lastname, address etc. fields by doing a lookup of an ASCII code reference table and making an InStr() type comparison.
I cannot find a way of opening the reference data set from withing the transform.
Regards
Tim
Hi all,
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?
Please help and advise.
Thanks in advance,
Scott Chang
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.
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
Hello everybody,
my name is Christof and I#m currently trying to write into our Terdata Warehouse by SSIS. In the Management Studio we can perfectly access Terdata - reading from it - but we actually see no way writing back Data into that system. Is there any hidden feature, any configuration menu that we had´nt seen so far?.
In the software documentation we found a tip "ADO.NET" as Data Destination is´nt imlemented for now - please wait until ....
I´m asking me if someone has had the same problem before and could help me out with a little tip.
thx in advance!
Christof
Hello everybody,
my name is Christof and I#m currently trying to write into our Terdata Warehouse by SSIS. In the Management Studio we can perfectly access Terdata - reading from it - but we actually see no way writing back Data into that system. Is there any hidden feature, any configuration menu that we had´nt seen so far?.
In the software documentation we found a tip "ADO.NET" as Data Destination is´nt imlemented for now - please wait until ....
I´m asking me if someone has had the same problem before and could help me out with a little tip.
thx in advance!
Christof
Hi
I am not able to access SSIS variables which are defined at Data Flow Task in a custom component. This custom component is developed by me in C#. How can i access these variables?
Please let me know if theres a way to access SSIS variables.
Thanks,
Vipul
I'm running windows 8.1 with SQL.Server.2012.
I've admin permissions on the PC but can't access SSIS because Access is denied like below.
However the solution proposed below is not appropriate for me as I can't find DCOM User Group Membership on my Windows 8.1 machine.
[URL]
After reading some threads on this forum about accessing OLAP data from SSIS via an OLE DB Source component (and trying it myself and getting that "pcrsstore.cpp line 325" error), I have worked around the problem by using a Script Task component.
This calls a .NET assembly I wrote that uses ADOMD.NET to get at the data using an MDX query, and then actually posts an event to a Notification Services instance via a stored proc.
Writing, deploying, and maintaining a .NET assembly is more than I wanted to do, but it was worth the effort since now I know a little about extending SSIS when I need to.
But what I really wanted to do was the following, all from within the core components of SSIS:
Import my OLTP data into my relational warehouse (already done using SSIS)
Process the cube (already done using SSIS)
Access the cube via MDX and post an event to Notification Services
It's this last step that needed some special work because of SSIS's apparent limitation accessing cube data (although posting the event to NS would be easy because SSIS can call a stored proc on the NS application database).
So I'm hoping that a future release or service pack of SSIS will give us some components to run MDX and get the OLAP data into the pipeline as if it were from any other source.
Thanks for reading, and if anyone has any suggestions on a better way to achieve what I need please let me know!
-Larry
hi
I would like to create a SSIS package that is going to be called by store procedures.
What i have done so far.
1) I created a Execute SQL task that come with this statement e.g. Seleect * from tblA where BD >= ? and BD =< ?
2) I save this package as a DTSX file and will called it from a proc.
My intention is to pass 2 values when i call the proc. What should do next? any guided tutorial or steps i would be happy. thanks
Hi *,
I'm trying to import a flat file with ~3500 rows into a SQL-DB. SSIS extracts only around half the rows. It leaves out every 2nd row. Anyone had this problem before?
Thanks!
Hi All,
I want to know is it possible to have source as Flat File and destination as XML
Thanks in advance,
Shagun
There is not a way to pass parameters to input of Web Service tasks. I heard this problem is fixed with SQL2K5 SP1 and even the online doc says that one can choose either "value" or "variable" when specifying input for web service tasks, but after I installed what-I-think-is SP1, there is still no way to do this.
If one can only specify values (hard-coded) as input to web service tasks, then this would be a very severe limitation. I hope I'm wrong, so could someone please give a pointer. Thanks
Kevin Le
Hi all.
we have SSIS package which runs under SQL Job.
Now we need to modify this package in order to have input parameter
which we want to setup in the "Set Value" Tab of SQL Server 2005 SQL Job |Edit Step |Option window.
Could anybody give me in details how-to setup Global Variable (or something else) inside SSIS Package in order to create input parameter of the package?
I also appreciate an advice how exactly we should assign value to input parameter of SSIS Package inside SQL Job Set Value Tab (example?).
Thank you,
Vitaliy
Hi all,
i am not very experienced with the SSIS. I am just wondering if there is something like a "if-then-else"-componente like the foreach-component in SSIS.
I want to delete the values of all tables in one database. So I took a foreach-component and selected the smo-enumeration with all tables. I store the tablename in a variable and execute a sql-task with "delete table.." with the variable tablename as parameter. Now I want to delete all except one certain table. I would like to add a selection where the variable tablename is checked. If the tablename is this certain table, I don't want to execute the sql-command, else I want to excecute the delete-command.
Are there any suggestions?
Thank you very much
Joachim
Hi,
In SSIS web service task - when you specify the Service and Method in the input tab for a WSDL file being used,
it seems to prompt for the parameters to be supplied only in the body of the WSDL file, and not the header.
I need to be able to provide security information (present in the SOAP header of the WSDL) like username, password etc..which is necessary to post any response to the web server, and I cannot see where I can give this in the input tab of the web service task.
Any help highly appreciated.
Thanks
I have just migrated a DTS 2000 package
as an SSIS package.
one of the features that failed to migrate, was a transformation
that , selected 2 colums of data with a stored procedure,
file name, and full path of filename,
Then the file name only was written to a txt file,
Then there was an ActiveX transformation task that used the other
column (full file path) to copy said file to another location (specified as a global str variable eg \127.0.0.1directory..etc)
Now my question is this, with SSIS script task
can i save the path name (2nd column) to a variable and then
using this variable copy the file to another location (global str variable) ?
Is there a CopyFile function like there is in ActiveX ?
And can i add this script task along with the DATA FLOW ?
because if i add it outside the DF , it will only (im assuming) copy the last line (path) into the variable...
Dear all,
Such a pain, I know. Yesterday I did a couple of questions regarding transformation rows and very kindly I obtained answer (Thanks Jamie and Michael)
But now I face another stupid issue and is how to map source with destination columns inside Script Component Task.
On my Flat File Source I€™ve got defined thirteen columms (from Column0 till Column13); that€™s fine. And then, I€™ve got a sql table as destination with another names, of course€¦
Inputs and Outputs option from Script Component Editor leaf has been commited both (Input 0 and Output 0) but I wonder how the hell I€™m refer to them here:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
** snippet of old code sql2 to migrate€¦
'If DTSSource("Col010") = "N" Then
' DTSDestination("ImpBase") = -1 * CDbl(DTSSource("Col011") / 100)
'Else
' DTSDestination("ImpBase") = CDbl(DTSSource("Col011") / 100)
'End I
.Net Script
If Row.Column10 = "N" Then
¿??????????????
End If
End Sub
Thanks a lot for your comments and thoughts,
Enric