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
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.
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!
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
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 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'm experiencing a problem I think I should not in my COM+ application. I will describe the setup first and then will expose the problem.
It's a simple COM+ application (dll). Inside it, there's a method to save an object A. Object A is persisted in a table in SQL Server 2000 that uses an identity field for the primary key. What this method does is the following:
1) Insert the record for Object A via ADO
2) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object
3) Execute an UPDATE statement based on a certain condition via ADO (this UPDATE statement will fire a trigger, however the trigger will not do anything since the record does not answer the criteria for the trigger)
4) Insert a record for another Object A via ADO
5) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object
When I get to step (5), an error is raised because SCOPE_IDENTITY returns NULL. This is as if it was returning the Identity value for the trigger that did not cause any INSERT on the UPDATE statement in (3). All the steps are performed using a single connection.
The trigger will duplicate the updated record in another table if a certain flag is set, so in my case, it was not set yet.
It's just weird that this would happen. If I delete the trigger, everything works fine. @@IDENTITY gives me the same problem. It's really as if the trigger was taking over or something and unless I put something between the two steps I get this error. There's one thing though. In step (3), I was using the adCmdText flag for the ADO statement. If I use adExecuteNoRecords it works fine. However I don't understand why it would be this way, I'm trying to understand why it's not working to begin with, even though the sequence of the steps performed should.
Any idea why this would happen?
Thanks,
Greg
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.
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
how the hell you allocate a null value for a smalldatetime sql field?
Now, I'm putting a false date because of I'm stuck with this f.. and then I do an update:
.Parameters("@FecEnajenacion").Value = "1999-01-01"
error:
.Parameters("@FecEnajenacion").Value = vbNull
.Parameters("@FecEnajenacion").Value = Null
.Parameters("@FecEnajenacion").Value = SqlDbType.???????
Hi all,
I have a class that utilizes the ThreadPool together with a ManualResetEvent to queue up work items (in this case, FTP downloads) and then waits until all work items have completed before proceeding.
I then use this class inside a Script Component. However, I am getting a "remote procedure call failed" every single time the FTP component starts a download. This same class works perfectly in a WinForm app that I created as a test harness. Also, the synchronized version of the FTP download component works perfectly well from the Script Task -- it's only when I "wrap" the synchronous version in a threaded version that it fails.
Also, when the "remote procedure call failed" exception occurs, sqldumper.exe runs for a split second and creates a minidump. I, unfortunately, cannot inspect the dump because VS2k5 throws a native error when I try to "debug" the dump.
Is it possible to utilize multithreaded code inside SSIS? I noticed that SQL Server prohibits the use of threading as part of its hosting requirements (http://msdn2.microsoft.com/en-us/library/ms172338.aspx) but I'm not sure how that would impact (if at all) the SSIS host.
Thanks,
Vitaly
Within a data flow I have a script component that I want to perform some data calls from. I want to execute SQL commands to connections already defined in the package's connection manager.
Is there a way to set my script's connection variable to the connection manager connection? Or, can I use the connection manager connections directly from within the script?
Hi
I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?
Thanks
Mani
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 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
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?
Hi!
I have a "little" problem with nested case model:
-- "normal" database:
DROP TABLE [unitInfo] ;
GO
CREATE TABLE unitInfo (
unitID INT PRIMARY KEY
, beginDate SMALLDATETIME
, area VARCHAR(10)
, partSize INT
, y2predict MONEY
) ;
go
INSERT INTO unitInfo
VALUES (1, '2007-02-01', 'home', 42, 10.0) ;
INSERT INTO unitInfo
VALUES (2, '2007-03-05', 'home', 43, 11.0) ;
INSERT INTO unitInfo
VALUES (3, '2007-02-02', 'office', 11, 11.4) ;
INSERT INTO unitInfo
VALUES (4, '2007-02-01', 'office', 10, 33.6) ;
INSERT INTO unitInfo
VALUES (5, '2007-02-01', 'office', 42, 44.1) ;
CREATE TABLE unitLog (
id INT IDENTITY(1, 1)
PRIMARY KEY
, logtime SMALLDATETIME
, -- combination of logtime/unitID is unique
unitID INT
, -- "FK" on unitInfo
m1 FLOAT
, m2 FLOAT
)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 2, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 3, 63.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-02', 4, 432.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-02', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-03', 1, 423.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-04', 1, 432.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-05', 2, 43.0, 441.0)
INSERT INTO [unitLog]
VALUES ('2007-01-06', 2, 43.0, 4.0)
INSERT INTO [unitLog]
VALUES ('2007-01-06', 3, 43.0, 4.0)
INSERT INTO [unitLog]
VALUES ('2007-01-07', 1, 4.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 3.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-09', 2, 143.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-10', 3, 143.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-11', 4, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-11', 5, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-12', 2, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-13', 4, 413.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-14', 4, 43.0, 414.0)
INSERT INTO [unitLog]
VALUES ('2007-01-14', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-20', 1, 43.0, 414.0)
INSERT INTO [unitLog]
VALUES ('2007-01-22', 1, 43.0, 414.0)
-- SSAS:
CREATE MINING STRUCTURE NestedStructure
( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE
, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS
, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS
, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS
)
)
ALTER MINING STRUCTURE NestedStructure
ADD MINING MODEL nestedModel ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR
,y2predict REGRESSOR PREDICT_ONLY
, logdata ([id] , unitID
, m1, m2
)
) USING Microsoft_Decision_Trees
/* version 1*/
insert into NestedStructure ( unitID, beginDate, area, partSize, y2predict
, logdata(skip,unitID, m1, m2))
openrowset('sqloledb', Server=myserver;Trusted_Connection=yes;,
'Shape {select * FROM mydb.dbo.unitInfo }
Append ( { select id, unitID, m1, m2 from mydb.dbo.unitLog }
Relate unitID to unitID ) as logdata ')
Parsing the query ...
OLE DB error: OLE DB or ODBC error: Syntax error or access violation; 42000.
Parsing complete
Where is the error?
/*version 2*/
CREATE MINING STRUCTURE NestedStructure1
( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE
, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS
, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS
, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS
)
)
ALTER MINING STRUCTURE NestedStructure1
ADD MINING MODEL nestedModel1 ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR
,y2predict REGRESSOR PREDICT_ONLY
, logdata ([id] , unitID
, m1, m2
)
) USING Microsoft_Decision_Trees
insert into mining structure NestedStructure1 ( unitID, beginDate, area, partSize, y2predict
, logdata(skip,unitID, m1, m2))
Shape {openquery(dsnDB,'select * FROM mydb.dbo.unitInfo') }
Append ( { openquery(dsnDB,'select id, unitID, m1, m2 from mydb.dbo.unitLog') }
Relate unitID to unitID ) as logdata
Parsing the query ...
Error (Data mining):
INSERT INTO error: The '[logdata].[id]' nested table key column is not bound to an input rowset column.
Parsing complete
Remark that combination logtime/unitID is the natural key in unitLog.
"ID" is the surrugate key.
What is wrong here...?
Hi, I was wondering how I can complete a column (which doesnt have an input one) with data.
For example:
I have a sql query which bring data of 3 columns
ID | FISRT NAME | LAST NAME
1 MIKE MORGAN
2 SARA JOHANES
So, I will insert that data in a FLAT FILE CONNECTION MANAGER, which I configured with 3 columns and I did the corresponding mapping in the FLAT FILE DESTINTATION.
Now, If I add one more column in the FLAT FILE CONNECTION MANAGER, I will not have it mapped to a input one, obviously. So, what I need is to add one more column to the flat file destination and complete it with zeros values in it.
Probably I can solve this part by introducing a DERIVED COLUMN and there I can configure the zeros that I want to add to the column. But I'm not sure if I can do that without having a input column.
So, the question will be, how can I add one column to a flat file which doesnt have a input and introduce any value that I want to it?
Hope I was clear
Thanks for your help.
Beli
I have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".
I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.
But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.
So how do I go about doing this the smart way?
thanks in advance!
Hi,
I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:
a) Why doesn't my Catch block catch the exception?
b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?
Hope someone could help.
Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.
View 4 Replies View RelatedHello,
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
I am trying to get the value of a column in the Input0_ProcessInputRow function and I have the column name.
There has to be an object in the pipeline that will allow me to do this right?
Something like "ComponentMetaData.InputCollection(0).InputColumnCollection([COLUMN NAME])"
Can someone recommend how I would do this. I have tried a few things, but can't seem to get to the Value or ToString of the column I want in this row.
Thanks
I need to find a way to query a value stored inside a table column. I have a piece of sample script that looks like this:
select convert(xml,[VALUE]).value('(/ROOT/app/@S_YEAR)[1]','numeric(4,0)') as S_Year from myTble
where [KEY] = 'StringA'
The myTble has 2 columns, Key, Value, and the Value is of data type text. The data stored in there is a large xml node or doc. The table has only three rows for StringA, StringB, and StringC.
I feel bad that I cannot post the xml here because it contains a lot of sensitive data. It will be very hard for me to replace those sensitive data without breaking the syntax integrity.I used to know XPath, but couldn't make out above Select.
Hi there,
I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.
For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -
update tableA
set columnA = ?,
column B = ?
where columnC = ?
AND (columnA != ? OR columnB != ?)
I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.
I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -
declare @ValueA varchar(50),
@ValueB varchar(50),
@ValueC varchar(50)
select @ValueA = ?,
@ValueB = ?,
@ValueC = ?,
update tableA
set columnA = @ValueA ,
column B = @ValueB
where columnC = @ValueC
AND (columnA != @ValueA OR columnB != @ValueB)
Any suggestions?
Hi Experts,
I am new to this forum and SQL usage.
I have a database with following table fields as
Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Amount - Integer
Now i want to create a View & split the Amount column into 2 basd on
split this Amount into 2 columns as below:
Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Debit Amount - Integer (where D_C is D)
Credit Amount - Integer (where D_C is C)
Regards
SEkar
Is it possible to assign to a column a value passed as a parameter?
When I run the proc I get the following error :
Msg 245, Level 16, State 1, Procedure Transfer, Line 17
Conversion failed when converting the varchar value '@ID' to data type int.
----####################################################
USE [tbldata]
GO
/****** Object: StoredProcedure [dbo].[Transfer] Script Date: 05/19/2014 11:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Transfer](@ID int)
[code].....
Hi,
I have a table with column size char(100), But As soon as I enter 60 charcters I get an error 'Maximum limit violation'
Any help please
Thanks
Raja Jayaseelan
Do I have to use condition split?, then union all?
if in script, I can use update from <tablename> Set column = isnull(column, 'NA'). It's so simple.
I'm also wondering can we run SQL Script against input dataset in a SSIS component?
I'm bothered by an issue of updating a column in input dataset from a update query. It looks like SSIS has a very poor function on this.
Example, I have an input dataset of name, salary, dept_no, and I have another table called departments which has fields of dept_no, Dept_name, basic_salary
now I want to update salary column in input dataset with basic_salary if it's salary is smaller than the basic_salary.
update #tmp set salary = basic_salary where #tmp.salary <departments.basic_salary and #tmp.dept_no = departments.dept_no
how could I impletement this in SSIS package?
I tried with lookup, modify scripts by enabling memory restriction. It doesn't say any error when I save the package, but I never get pass debug.
HELP!!