This Component Has No Available Input Columns.
May 28, 2007I 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
ADVERTISEMENT
SSIS Script Component - Iterating Through Input Columns
Nov 26, 2007
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.
How To Run A Loop For All Columns In Script Component Input Column Collection For Each Row
Jan 22, 2008Hi 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.
Create Output Columns Based On Input In Custom Component
Aug 28, 2007
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?
Custom Component: How Can I Have Two Input Datasources?
Mar 27, 2008Hi 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.
How To Use Input Column Inside Script Component
Jul 22, 2007
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
Why Is There No Warning If An Input To An Aggregate Component Is Not Sorted
Nov 16, 2005All in the subject.
View 11 Replies View RelatedEditing Input Data In Script Component
Aug 22, 2007This 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.
SSIS Script Component Accessing Input ?? Please Help!
Feb 6, 2008Hi
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
Accessing Input On SSIS Script Component Tasks
Feb 6, 2008Hi
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")
No Available Input Columns
Sep 21, 2006Hi ,
Im trying to build a package that will copy data from excel to SQL
in a program
unfortunately , when I open the package xml file and I drill into the
oledb destination I see that I have no available input columns
what could be the problem ?
thanks ahead
Eran
p.s
the script:
Dim p As Package = New Package()
Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")
Dim thMainPipe As TaskHost = CType(e, TaskHost)
thMainPipe.Properties("Name").SetValue(thMainPipe, "Data Flow")
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' Create excel connection MANAGER
Dim excelCon As ConnectionManager = p.Connections.Add("EXCEL")
excelCon.Name = "ExcelSourceConn"
excelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e: ry ry ry.XLS;Extended Properties=""Excel 8.0;HDR=YES"""
' Create sqldev connection Manager
Dim sqlCon As ConnectionManager = p.Connections.Add("OLEDB")
sqlCon.Name = "sqldevConn"
sqlCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=InsFocus_Admin_Eran;Data Source=SQLDEVSQLDEV"
''create source component
Dim excelSource As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()
excelSource.Name = "ExcelSource"
excelSource.ComponentClassID = "DTSAdapter.ExcelSource.1"
Dim excelInstance As CManagedComponentWrapper = excelSource.Instantiate()
excelInstance.ProvideComponentProperties()
excelSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(0))
excelInstance.SetComponentProperty("AccessMode", 0)
excelInstance.SetComponentProperty("OpenRowset", "business_codes$")
excelCon.AcquireConnection(Nothing)
'excelInstance.ReinitializeMetaData()
excelInstance.ReleaseConnections()
Dim sqldev As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()
sqldev.Name = "sqldev"
sqldev.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
Dim sqldevInstance As CManagedComponentWrapper = sqldev.Instantiate()
sqldevInstance.ProvideComponentProperties()
sqldev.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(1))
sqldevInstance.SetComponentProperty("AccessMode", 0)
sqldevInstance.SetComponentProperty("OpenRowset", "business_codes")
sqldevInstance.AcquireConnections(Nothing)
sqldevInstance.ReinitializeMetaData()
sqldevInstance.ReleaseConnections()
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
path.AttachPathAndPropagateNotifications(excelSource.OutputCollection(0), sqldev.InputCollection(0))
MsgBox(excelSource.OutputCollection.Count)
'For Each input As IDTSInput90 In sqldev.InputCollection
' Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput
' For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
' ' Call the SetUsageType method of the design time instance of the component.
' sqldevInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
' Next
'Next
Dim app As Application = New Application()
app.SaveToXml("c:myXMLPackage.dtsx", p, Nothing)
Preselect Input Columns
Dec 22, 2006Is there a way to preselect the input columns a custom component wanna use. Lets say by DataType (select all of DT_CY for example).
I tried to use the
OnInputPathAttached()
to iterate over the inputColumnCollection and set the UsageType. Didn't worked.
Thanks in advance
Concatenate Input Columns
Jul 5, 2006Hi,
In my data flow taks, The Source data is coming from AS400 has 4 columns,
I need to achieve the followings and require your help.
1. Generate a new column which will be combination of concating these 4 columns.
2. Need to add an extra row for Header & Footer.
Please Help.
Pick One Of Three Input Columns
May 29, 2007I have three input columns from my flat file, defined as follows:
Col_A
Col_B
Col_C
Now, what I want to do is pick the one that is populated, and put it into my table, in the column called
ABC_INPUT
I assume that I want to use a derived column, but how do I filter out columns that contain only NULL values?
Thanks!!
jim work
Variable Number Of Input Columns
May 2, 2007We are trying to use the Import/export wizard to load a text file to a SQL Server 2005 database. The input file has a variable number of columns per row. For example, the first row has 3 columns, the second has 7, the third has 3, etc. The number of columns varies from 2 to 9 in the input file. The columns are separated by an uptick (`) and the rows are terminated by {CR}{LF}. We are using code page 1252. On processing, the wizard reads the first row (with 3 columns) ok, but then assumes all the other rows have 3 columns and parses the rows accordingly, ignoring the field and row terminators.
The process worked fine with SQL Server 2000. Is there some setting that we are missing, or some configuration on the database that we should be checking?
Thank You
Row Count Task, What Is Input Columns Tab For?
May 15, 2008I'm using a Row Count Task to count the number of records passing thru a particular path in my data flow. I created a package variable and referenced this variable in the Components Properties tab in the Row Count Task. I believe this is the minimum I need to do to get the row count.
However, as I explore the other tabs in the editor, I see there is something called Input Columns tab. What is this for? I didn't select anything in there and things are working fine. At first I thought that I had to choose the columns that I want to have available to me for further processing after the Row Count Task, but this isn't the case. I am able to see all my columns coming out of the Row Count Task even tho I didn't do anything in the Input Columns tab.
So, what is this setting for?
Ole DB Destination (available Input Columns Blank)
Oct 17, 2007Can anyone please help
I am trying to get a table from production to development created and populated with data in Prod. When I create a package to set the data flow on the available input columns in mappings I do not see any columns there. The source has been defined as the production table.
Thanks a bunch
Not Able To Check For Nulls While Looping Through Input Columns
Mar 10, 2008Hi,
I have an SSIS Package. I am using script component to loop through input columns and their values. I am not able to do Null checking. The code is as below. In place of dashes , I want to do null checking but am not able to do. I tried vbNull, IsNull, TypeOf, System.dbNull but nothing is working. I guess am missing something here. Can anyone help me with this.
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
columnValue = rowType.GetProperty(column.Name)
If Not --------------- Then
RowData += ((FormatElement(column.Name) + ReplaceSpecialChars(columnValue.GetValue(Row, Nothing).ToString()) + FormatElement(column.Name, True)))
End If
Next
Thanks in Advance.
Retain Input Columns Through An Asynchronous Transformation?
Jan 23, 2008
Is there by chance a cunning way to make the input columns automatically populate the output of an asynchronous script transformation?
My transformation writes several rows for each input row read. I'm creating some new columns along the way but I'd like all of the input columns to get output each time also. However I can't see any obvious way to achieve this, short of manually defining each column to the output and populating it in the script.
Odd Problem Programatically Mapping Input Columns
Dec 14, 2007I have set up a script task in one of my packages that I have set up to modify another package right before running it. This package is nothing more than a data flow task that transfers rows via an sql command from one table into another. The strange thing is I have gotten it to work with some tables but not with others. T
he script bombs out in the loop where i map all of the columns found below, where i use MapInputColumn with the error HRESULT: 0xC0010009 On Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSExternalMetadataColumnCollection90.get_Item(Object Index)
The thing is this happens after looping roughly 55 times but there are still about 100 columns that it needs to loop through still.
Code Block
Dim input As IDTSInput90 = data_destination.InputCollection(0)
Dim virtual_input As IDTSVirtualInput90 = input.GetVirtualInput
Dim input_column As IDTSInputColumn90
Dim virtual_column As IDTSVirtualInputColumn90
' Iterate through the virtual input column collection and map field names
For Each virtual_column In virtual_input.VirtualInputColumnCollection
input_column = inst_data_destination.SetUsageType(input.ID, virtual_input, virtual_column.LineageID, DTSUsageType.UT_READONLY)
inst_data_destination.MapInputColumn(input.ID, input_column.ID, input.ExternalMetadataColumnCollection.FindObjectByID(virtual_column.Name).ID)
Next
Just for kicks i removed the mapping portion of the code and left in the SetUsageType to see if it would update the available input columns in the destination. The script will then finish successfully but still only the 55 or so fields out of 155 are available in the input. So i then stepped through the script with the mapping portion still disabled and after it loops successfully, i call reinitialize meta data and it produces an error in the input_column variable: HRESULT: 0xC0047041.
I find it odd that this still reports to me that the script finished successfully and I also find it odd that this works fine on two other tables I've tested but not this one. Any insight would be greatly appreciated.
Problems With Input Columns For Mining Models
Nov 20, 2006Hi, all experts here,
Thank you very much for your kind attention.
I got a strange problem with SQL Server 2005 data mining models though. I have selected the input columns for my mining model (which are different from the input columns for its mining structure, since I ignored some of the columns for the selected model). But the mining model still used all input columns from the mining structure rather than those I chose for the mining model.
Would please any one here give me any guidance and advices for that. Really need help for that.
Thanks a lot in advance for any help.
With best regards,
Yours sincerely,
How To Make Input Columns Unavailable For Downstream Components?
Dec 22, 2006Hi,
In a SSIS Data flow task, whatever you are doing with the input columns that come from a Data source like a Flat File Source, these columns are always visible and available as input columns for all Transformation and Destination components in the Data flow.
Our Custom Component is a column mapping component that transforms many input columns into many oputput columns and we would like that the used input columns are not available anymore to the downstream components...
Is that possible?
I saw that with the Unpivot component it is possible to make some input columns unavailable to downstream components...So I think there is a way to do the same in a custom component...
Thanks for any help,
David
Dynamic OLE DB Table Source From Variable Not Seeing Input Columns
Dec 12, 2007I am building an SSIS package that loops through a table in SQL Server and dynamically builds a select statement that i would like to use as an ole db source. I have been having a difficult time with this as the select statement that i am generating is over 200,000 characters long so using an sql variable is out of the question.
I ended up placing the select statement into a table where each row of the table represents a piece of the select. I then use an execute_sql task that selects the entire rowset from this table into a variable object. I then use a for each loop to shred the variable and concatenate it into on big string variable called user:: sql_statement that is my select.
After setting up the loop and testing to see if the user:: sql_statement variable populates correctly i then added a data flow transfer with an ole db source and destination. I then go into the advanced editor for the source and set it to accept an sql statement from a variable and use my user:: sql_statement variable. I was forced to set validate external metadata option to false to avoid an error since there is no way to validate the columns until the for each loop runs during run time.
Now thats all fine and good but what is causing my problem is that during run time, when the package gets to the data flow task, the select statement doesn't seem to be populating the input columns of the data source. I have been searching to no avail on a way to tell the data source to update the input columns but every time it gets there, the package bombs out telling me the ole db source has no available output columns.
Specifically the error i get is :
[DTS.Pipeline] Error: "output "OLE DB Source Output" (6616)" contains no output columns. An asynchronous output must contain output columns.
Any help with this would be much appreciated.
Selecting Only The List Of Columns From A Table Based On Input
Apr 17, 2008Hi,
i have a table like below
table:-
id col2 col3 col4 col5 col6
1 2 3 4 5 6
2 5 8 4 7 6
3 4 8 2 6 9
4 2 5 8 6 3
5 6 9 5 5 9
i want to write a stored procedure where i pass column names a parameters and i want to get result based on that
For ex:-
if i pass the parameters as
col3 and col5 where id =1 then i should the result as
id col3 col4 col5
1 3 4 5
and if i pass input as col2and col6 where id =3, the result should be
id col2 col3 col4 col5 col6
3 4 8 2 6 9
can anyone help on this??
Integration Services :: DQS Cleansing Transformation - Can't Deselect Input Columns
Apr 14, 2015In SSIS I use the DQS Cleansing transformation component. I've got a knowledge base (KB) in place and this KB holds various domains and my data source has more input columns than would like to use for a particular clean up operation. I want to use some of the input columns to map against some domains in the KB. It is my understanding that it should be possible to select only the required input columns, but all i can do is select all input columns.
View 3 Replies View RelatedAdd Extra Error Columns - From The Component?
Aug 17, 2007I've read a MSDN article on enhancing error outputs with a script component. This solution added the script component astride the error output, and it was able to add a column with the error description text.
I would like to do something a little different. I'd like to add information from my custom component. When the component causes an error, it generally "knows why" - it has available to it the information that made it decide there was an error. I'd like to include that information in error outputs, but I haven't seen a way to do that.
Is this even possible using the error output directly? I could abandon error outputs and use Conditional Split, along with an "iserror" column plus my extra columns, but I'd prefer not to do that.
The Component Metadata For Component DataReader Source (1113) Could Not Be Upgraded To The Newer Version Of The Component.
Oct 26, 2007Hello,
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.
The Component Metadata For Component DataReader Source Could Not Be Upgraded To The Newer Version Of The Component.
Jan 23, 2007Hi,
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
Data Mining In 2005 - Same Input Columns Resulting In Different Decision Trees
Dec 12, 2007While recently working with several mining models, I came across something that struck me as pretty odd - and I'm hoping to find an explanation for the behavior.
Consider the following setup:
A single table in the relational database represents the only case table
A single, continuous column is the predictable
A mining structure has been created
The mining structure contains a single model, based on the MS Decision Trees algorithm
Input columns were selected for the model via the BI Studio wizard (i.e., those provided via the "Suggest" button)
The structure has been fully processed
Now, the interesting parts:
I view the scatterplot for the mining model, under the Mining Accuracy Chart tab
Back on the Mining Structure tab, I delete one of the input columns
I add the same column back into the structure
The structure is fully processed again
When I view the scatterplot for the mining model, under the Mining Accuracy Chart tab, a different set of data points are presented for the model predictions
A different set of decision trees under the Mining Model Viewer tab confirms thisHow could different patterns have been found this second time around, even though all of the input columns were the same (as well as the training cases)?
(Note: I encountered this situation while creating a new mining model that was identical to an existing one. Even though the models received the exact same inputs and training cases, they yielded different results. I was able to reproduce the behavior by using steps 1-6 above, though.)
Can someone provide some insight on this behavior, or some kind of explanation of what may be happening?
Thanks,
Joe Miller
How Do I Order Columns In My Custom Destination Component?
Nov 17, 2006Here is the situation. I have created a package that takes 50 columns from a comma delimited flat file. I then validate and clean the data. Next I add two columns that were not in the original source file. These two columns need to be in the 5th and 9th column position when the file is then re-written to a text file. How do i get those two columns to write out in the desired order? Any ideas?
K. Lyles
Script Component. Outputting To Columns Without Knowing Their Name
Feb 22, 2008
Hi Folks,
Always sorry to have to ask what is most likely such a simple question. However I'm in no way a programmer, I'm just patching something together using scripts I've found all over the shop.
I'm reading in an excel source using a dataflow script component. I build up a SQL String in Vb.Net using the output column names and query the spreadsheet via microsoft.Jet.OLEDB.4.0 then processing it in code. I don't want to use the Excel Source task btw, more for the fact I want to learn from this as well as other less important reasons.
This all works fine. Adding new columns to the output means the query string dynamically changes without the script ever having those columns defined in code. Easy stuff so far.
The issue I have is writing back into the outputbuffer the results without explicitly mapping the result to the output column names.
An easy example to see is..
Excel Spreadsheet Looks like..
ServerName
Server1
Server2
Server3
Output Column Names..
ServerName
Code Snippet..
While Reader.Read
With OutputBuffer
.AddRow()
.ServerName = Reader.GetString(0)
End With
End While
Instead of defining what the column name (servername) is, I want to map back by matching the source column header to the output column name. By doing this I won't ever need to change any code when I add or remove output columns.
Could someone lend a hand with this, and I'll buy you a virtual beer or two. I've already spent more time searching for an answer than I have writing the code thus far, and I think my heads getting more and more muddled by it.
Many thanks,
Jode
Map Resultset From Executing A Stored Proc Into Input Columns Of A Data Flow Task
Jul 30, 2007
I need to loop the recordset returned from a ExecuteSQL task and transform each row using a Data Conversion task (or a Script Task).
I know how to loop the recordset returned by an ExecuteSQL task:
http://www.sqlis.com/59.aspx
I loop the returned recordset (which is mapped to a User variable of type System.Object) and assign the Variable Mappings in the ForEach Loop to different user variables which map to the Exec proc resultset (with names and data types).
I assume to now use these as the Available Input columns for the Data Conversion task, I drag a Data Flow task inside the For Each Loop container and double-click it, then add a Data Conversion task.
But the Input columns (which I entered in the Variable Mappings in the ForEach Loop containers) dont show up in the Available Input columns of the Data Conversion task.
How do I link the Variable Mappings in the ForEach Loop containers from the recordset returned by the Execute SQL Task to the Available Input columns of the Data Conversion task?
.......................
If this is not possible, and the advice is to use the OLEDB data flow as the input for the Data Conversion task (which is something I tried too), then the results from an OLEDB Command (using EXEC sp_myproc) are not mapped to the Available Input columns of the Data Conversion task either (as its not an explicit SQL Statement and the runtime results from a stored proc exection)
I would like to use the ExecuteSQL task to do this as the Package is clean and comprehensible. Which is the easiest best way to map the returned results from a Stored proc execution to the Available Input columns of any Data Flow transformation task for the transform operations I need to execute on each row of data?
[ Could not find any useful advice on this anywhere ]
thanks in advance!
Scd Type 2 Historical Component Not Working When Using Not Null In Columns
Aug 23, 2007
I have a timestamp column and a username column with default values of getdate and system user in my table and they are defined as not null.
even though i donot use these columns in scd type 2 in wizard
if the columns are not null the scd deosnot work and If the columns are defined null they work
Can anyone please explain or help me with what might be the problem associated with this