Script Component - DataType Property Inaccessible
Dec 19, 2007
Hi,
I have a trouble with script component. From time to time, I cannot see available data types of the column I´m just editing, in the Script component editor. The cell in the section Data Type properties, where normally combobox with data types is, is completelly black. This happens both with "old" script components I'm only viewing and new script component I'm creating.
This problem occured for the first time immediately after I migrated from Windows XP to Vista Bussiness 64bit and then several times without any obvious similarity among terms under which this appeared.
I have SP2 for SQL Server and SP1 for VisualStudio 2005 Professional edition installed.
Please, can you help me?
Thanks
Jana
View 11 Replies
ADVERTISEMENT
Apr 9, 2008
i need some help modifying a custom control flow task...
I am currently working on an export solution, and since some of the consumers of the export files can not handle unicode, i need to change the Codepage of the output based on config file parameters.
I downloaded a custom dataflow component published by MS (please find the link below - 1). It works fine for changing codepages, but it cannot be configured via expressions.
So i decided to modify the component the way MS describes it (link below again - 2):
Thus, added one row to the method "ProvideComponentProperties()":
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
But this changes nothing, i am still not able to use an expression for setting this property. I could need some help on this...
Thanks a lot,
Regards
Jan
Links:1)SQL Server SSIS Sample Component: CodePageConvert:https://www.microsoft.com/downloads/details.aspx?familyid=9e56417e-23d1-4fd3-8d6d-61314faa2de3&displaylang=en2) Design-time Methods of a Data Flow Component:http://technet.microsoft.com/en-us/library/ms135969.aspxComplete Method:
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
IDTSCustomPropertyCollection90 customProperties =
this.ComponentMetaData.CustomPropertyCollection;
IDTSCustomProperty90 customProperty = customProperties.New();
customProperty.Name = "CodePage"; // do not localize
customProperty.Value = 850;
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY; //My Code
}
View 1 Replies
View Related
May 29, 2008
Can you fire a warning / error validation immediately after a value is entered (user clicks away / clicks different property) in a component property without having to press ok button or switch tabs.
Have tried to call validate in setcomponentproperty but only takes effect when press ok button / switch tabs.
Thanks
View 1 Replies
View Related
May 28, 2008
I have set a validation error when changing a property in the advanced editor. Unfortunately this validation error is only displayed when you change tabs or press the ok button in the editor.
I have tried calling the validate method within the setcomponentproperty method but the error only show up when ok button is pressed or tab in editor.
I don't want to throw an exception, is there any way you can show a validation error immediately once the user has clicked away / entered / clicked to another property field after entering a property value in the advanced editor that is incorrect.
Thanks
View 1 Replies
View Related
Mar 24, 2007
I am creating of may of dataflow component.
How make a property with list of predefined values?
Thanks in advance.
View 3 Replies
View Related
May 8, 2008
Hi,
I have a script component that contains a script similar to that shown below;
Public Sub Main()
Dim sqlConn As New SqlConnection("Server=myserver;Database=mydb;Trusted_Connection=True;")
Dim SQLStmt As String
SQLStmt = "select top 10 CountID from mytable"
Dim sqlComm As New SqlCommand(SQLStmt, sqlConn)
sqlConn.Open()
Dim r As SqlDataReader = sqlComm.ExecuteReader()
While r.Read()
Dim MyValue As Integer
If r("CountID") IsNot DBNull.Value Then
MyValue = CInt(r("CountID "))
' HERE I WANT TO WRITE MyValue TO A PACKAGE VARIABLE AS A ROW
End If
End While
r.Close()
sqlConn.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
Once i have a value assigned to MyValue, I want to write this as a row to a variable (DATATYPE Object Value VALUE System.Object) in the package which will be an ADO Recordset Destination that i can then loop through later.
Many thanks for any help
Phil Harbour
View 1 Replies
View Related
Aug 17, 2015
I'm writing a custom source component that reads data from a SharePoint list with dynamic mapping to output columns. It's my first custom component and it's based on several samples and tutorials from Internet
Output columns are not created by the component itself, they must be added by user at design time. The component makes dynamically an association between SharePoint fields and available output columns at run-time (based on an mapping table).
I made a very basic skeleton and I encounter a problem when I add a column to output: it has no datatype and when I try to set one I have an the error Property value is not valid, The component xxxxxx does not allow setting output column datatype properties.
Imports System
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
<DtsPipelineComponent(ComponentType:=ComponentType.SourceAdapter,
DisplayName:="SharePoint Dynamic Assoc List Source",
[Code] ....
View 4 Replies
View Related
Apr 2, 2007
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
View 5 Replies
View Related
Aug 17, 2005
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
View 10 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
Nov 21, 2007
Has anyone seen this error related to SSIS? I get this everytime I try to perform any action on (import, export, or run) a file system package. I am racking my brain and I would prefer not to perform a full reinstall, but I am not seeing any posts that have helped with this error.
Unable to cast object of type 'Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction' to type 'Microsoft.SqlServer.Dts.ObjectExplorerUI.ISimpleAction'. (Microsoft.SqlServer.DtsObjectExplorerUI)
View 7 Replies
View Related
Jun 16, 2006
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
Can someone please help me resolve this.
Thanks much.
View 5 Replies
View Related
Jan 8, 2008
Something strange just happened to my database.Windows Vista UltimateSQL Server 2005 SP2IIS 7.0I just did an insert into a few tables from my ASP.NET application that I'm building and I suddenly cannot query the database tables. The query window just spins and finally times out.So I decided to try a SELECT TOP and tried 100, nothing, then 10, nothing, then 1 and it returned the first row. Then I tried 2 and got that, and I remember this table had 8 rows prior to my last Insert, and once I tried to select TOP 9, it times out again.Does anyone knows why this happened and how to fix it? It's like every row past 8 either doesn't exist or got corrupted somehow. I can't even drop the table and rebuild it. SQL Server won't let me. If someone doesn't have a solution, I'll have to rebuild the entire database and I'd rather not have to do that.Any help would be appreciated.Thanks,Kahanu
View 2 Replies
View Related
Jun 17, 2008
Msg 945, Level 14, State 2, Line 1
Database 'Northwind' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
I keep getting that error for just trying to USE Northwind. I don't understand why this problem is occurring. It was working fine for the past few weeks. Yesterday something happened to the DB and it said something about recovery to which I accidentally clicked cancel. From then on I can't access some of the DB that I have on my computer.
I'm fairly new to the SQL Server Environment so I would appreciate any and all help that can be provided. I've google searched the details but I can't seem to get any of those to work. Thanks in advance for your reply.
View 2 Replies
View Related
Dec 21, 2007
Hi All,
Does anyone knows what is the reason behind following error ? And resolution for that ?
Server: Msg 945, Level 14, State 2, Line 1Database 'Blaster' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Thanks in advance,
--kneel
View 1 Replies
View Related
Apr 17, 2008
Hi,
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
Thanks for any hints.
View 3 Replies
View Related
May 7, 2008
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing
"OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere"
" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager.
The Expression is as follows
"@[User:trTextFileImpDirectory] +"SomeTextStringHere."+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ +"SomeTextStringHere"
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text
files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch
value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While
"Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures.
2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ?
3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
View 10 Replies
View Related
Jul 2, 2007
Please help.
I created a linked server with Oracle Provider for OLE DB to Oracle Database from SQL Server 2000 SP 4. Afterwards I created Views from this linked server and granted SQL Server users "select" access to the Views.
Unfortunately when they try to access the views the following error message was generated (see the attached image - error.gif).
Can someone please explain to me what is wrong?
Thanks
Victor
View 7 Replies
View Related
May 24, 2001
Hi,
I have a problem with a database on our SQL 7 system . I had set away a restore from tape, but cancelled it after users complained that a different database was performing slowly . However, this has resulted in the database being marked as inaccessible when trying to access it through SQL Enterprise Manager and trying to access it through Query Analyser produces message 927 informing me that the database is in the middle of a restore . How do I remove this error and make the database accessible again??
Many thanks in advance
Peter Burton
(IT Support)
Durham Aged Mineworkers Homes Association
View 1 Replies
View Related
Jun 30, 2015
I am not MS SQL Developer, and I have no experience with it. I have had to use it as a part VAMT database of keys and activation.
There is 8.1 box with MS SQL (express I think).
After change host name I cannot connect to database with error "The specified database is not a valid VAMT database."
Looking into log gives me: Could not obtain information about Windows NT group/user 'litkjonca', error code 0x534."
Some googling suggest to use:
sp_dropserver "..." and sp_addserver '...', local
but with no effect .
View 17 Replies
View Related
Aug 12, 2015
Been practicing DR strategies with a test SQL instance by following the scenarios listed here: [URL] ....
> Took a backup of the Model database
> Stopped SQL Server
> Deleted model database data & log file
> Started SQL Server and it obviously wouldn't start because TempDB needs a model database present.
> Started SQL instance with trace flags 3608 & 3609
> Connected to SQL instance using command prompt.
> Issued restore command but was met with this error:
Shared Memory Provider: The pipe has been ended.
Communication link failure
And found this in the SQL log..
2015-08-12 16:21:32.83 spid51 Starting up database 'tempdb'.
2015-08-12 16:21:36.88 spid51 Error: 3456, Severity: 21, State: 1.
2015-08-12 16:21:36.88 spid51 Could not redo log record (59:136:21), for transaction ID (0:0), on page (1:20), allocation unit 458752, database 'tempdb' (database ID 2). Page: LSN = (30:165:3), allocation unit = 458752, type = 1.
[Code] .....
View 9 Replies
View Related
Jul 28, 2006
I made a merge replication and sucessfully connected with mobile device. Everything works fine. Because I wanna try asynchronous synchronization (which won't stop my application executing when subscribing) I read How to example from MSDN :
http://msdn2.microsoft.com/en-us/library/ms172391.aspx
But there is a problem with creating SyncStatus class object. I can't get into it and I can't create. maybe some reference will help? (I use reference to SQLServerCE). This error stoped my work for now, so I am waiting for some answers.
And if it's simple resolution for this problem - sorry. I just started programming applications for Mobile Devices.
Best regards. Maciek Wysocki
View 3 Replies
View Related
Aug 4, 2015
How to fix this SQL error event logg 17204 and 17207 ?
View 8 Replies
View Related
Jan 28, 2015
Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
View 4 Replies
View Related
Apr 24, 2008
Good afternoon,
I have an issue with an ssis variable datatype.
The scenario is as follows:
I have a stored procedure:
PROCEDURE [dbo].[sp_newTransaction]
@sourceSystem varchar(50),
@txOut NUMERIC(18,0) OUTPUT
AS
insert into scn_transaction (sourceSystemName) values(@sourceSystem);
SELECT @txOut = @@identity
Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).
I execute the stored proc with the following sql with an OLE DB connection manager:
exec sp_newTransaction ?, ?
The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:
User:ystxId output numeric 1 -1
User:ourceSys input varchar 0 -1
The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.
At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.
I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.
Thanks much,
B
View 6 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
Sep 17, 2003
Database is SQL Server 2000
I have a field in a table that stores date of birth. The field's datatype is char(6) and looks like this: 091703 (mmddyy). I want to convert this value to a datetime datatype.
What is the syntax to convert char(6) to datetime?
Thank you in advance.
View 1 Replies
View Related
Mar 14, 2008
Hi,
I imported a table from Accees to SQL 7 with data in it.
I need to modify one of the datatype columns to "datetime" from nvarchar.
I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error.
I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one.
UPDATE users SET DATE2 = DATE.. But it also faild,..
How can I modify the column?
Thank you.
View 10 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
Feb 15, 2007
Hello,
I have a group I'll call G4.
The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group.
When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time.
At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown.
When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point...
=IIF(reportitems!textbox66.
When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value.
If I complete the IIF statement manually so that it spells out .....
=IIF(ReportItems!Textbox66.Hidden = False, True, False)
...the report chokes on it.
So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group?
Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.
View 1 Replies
View Related
