Hi All,
I have built a custom flat file destination adapter but it appears that the code is not working. When I debug the process I notice that the ProcessInput section is called multiple times. The first time it looks like everything is working, then it call it again and there is no inpout from the DTSInput90.
Why would it do this?
Thanks
Mike
I am writing a custom task to import data from delimited files into SQL tables. I use the standard Flat File Source adapter, a custom transformation to add a URN column and a filename column to the data and the standard OLEDB Destination Adapter.
Most of my test data files work fine except for ones with a lot of columns (around 350 columns). I get an error when I call the ReinitializeMetaData() method for the destination adapter.
Q1) Is there a restriction on the number of columns (or data row size) that can be imported into an OLEDB Destination Adapter?
Q2) The reason I use this adapter rather than the SQL Server Destination Adapter is that I need to set the destination table name using a variable. I don't believe I can do this with the SQL Server Destination Adapter. Is this the case?
Q3) Anyone know of a better/alternative way of acheiving the above? One way I have thought of is to create a custom destination adapter using the SQL Server Destination adapter as the base but I'm not sure whether this is a) possible and b) worth the hassle.
Hope someone can help...
Cheers
Nick
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
Quick question...
I have a conditional split transform in a dataflow...
One of the outputs of the conditional split will take records that are just not needed...
Do I have to send that output into the Trash Destination adapter? Or can I just let those records hang?
Will there be a memory leak if I don't use a trash adapter?
I'm just thinking about if I move the package I'd have to go reinstalling the trash adapter every time...
Thanks
I would Like to know whether anyone tried to SSIS from SQL to AS400.
I am getting Exception when I set destination adapter ( for As400).
Whats the acceptable value .
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
Hi,
I have conditional split on a boolean variable, when the value is true, case1 output goes to Oledb Destination, defaukt Output goes to Sql Server Destination.
When I run the package with value "true" the package runs, but when i run the package with bool value "false", the data is inserted through oledb but Sql Server destination fails.
Can any one tell me Why.
[SQL Server Destination [292]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
I don't have any linked server.
thanks
Dharmbir
I am currently writing a custom source adapter that extracts data from a JD Edwards OneWorld system. In the custom user interface of the source component I need to allow the user to set a query (a custom property), and then refresh a list of output columns that will be extracted based upon that query (similar to the list shown in the advanced editor).
My question is, can I apply the custom property change to the component and build my output column list without closing and restarting my custom UI form? I understand that the IDTSComponentUI interface being implemented allows for transactional editing of the component, in that the changes to the component are not applied until I have returned a result in the implemented Edit() method. However is there a way to apply changes without returning this result (and closing my UI)?
Essentially I am looking to have similar behaviour to that of the 'Refresh' button in the advanced component editor form.
Thanks
Hi,
I'm getting some unexpected behaviour from my SSIS packages when targeting tables that are being referenced by Indexed Views. There's two separate issues:
1. When writing into a pair of tables with a SuperType / SubType relationship concurrently with a pair of SS destinations I'm getting deadlocks between the two. Removing the index on the view that references both of these fixes the problem.
2. Much odder, I'm getting some extremely long waits (10 times longer than the whole package should take to run!) from an SS Destination adapter even when there's no data in the flow for it to bulk insert. Again, removing the indexed views that reference the destination table fixes the problem.
The views aren't mine, and (apparently) are required by the reporting app (BO), so removing them isn't really an option. I realise that there's quite a lot of overhead to maintaining indexed views, but unfortunately, the project is on a very tight timeline, so I can't look into it in as much detail as I'd like.
I was wondering if anyone's experienced any similar issues, or would have any ideas as to where to start investigating?
Thanks a lot
Mark
I didn't want to deal with any truncation issues so I edited the SQL created by the Import Wizard. I made the fields VARCHAR instead of NVARCHAR and changed the size from the default 50 to 250.
Now I have a triangle warning on the OLE DB Destination control. So I am wondering, is it limited to only handling 50 characters?
IanO
Hi,
Will someone please tell me what is the difference between Destination adapter and bcp in Sql server 2005 ?
Thanks and Regards
Altaf Nizamuddin
It€™s always said that using the SQL Server Destination adapter is much faster when loading data to a SQL Server database. My question is, if I have a SQL 2000 database, can I still use the SQL Server Destination adapter, or is it only for SQL 2005 db? Is OLE DB Destination adapter my best bet? My data files(flat files), SSIS package and destination SQL 2000 db are all in the same physical server.
View 1 Replies View RelatedI'm having alot of trouble figuring out the proper way to log from inside my custom source adapter.
I couldn't find my useful information in MSDN, it mostly applies to logging from inside a script task.
I'd like to log my messages along with all the other SSIS package log entries, which I have going to the dts log table.
I'm assuming I should be using some functionality from Microsoft.SqlServer.Dts.RunTime, probably the LogProvider.
Can anyone advise?
I am writing a custom dataflow destination that writes data to a named pipe server running in an external process. At runtime, in PreExecute, I check to see if the pipe exists. If it does not exist I want to be able to fail the component and gracefully complete execution of my package.
Question is, how do I do this? If I make a call to ComponentMetaData.FireError() that will only create an OnError event and not actually stop the execution. So, how to I halt the execution of my component and return the error message?
Thanks,
~ Gary
Here 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
Hi there,
I am writing a Custom Destination component with a custom UI. The UI contains a combo box which contains the connection names of type €œFLATFILE€?. I also have provided a button which would create a new connection of type €œFLATFILE€? by making a call to CreateConnection method of IDtsConnectionService.
The combo box gets properly updated showing the connections of type €œFLATFILE€? but on clicking on the new Connection button the application hangs up.
Am I missing something or is there some other way to do it?
The function are the events handlers which are called by the UI.
void form_GetConnections(object sender, AvailableColumnsArgs args)
{
Debug.Assert(this.ServiceProvider != null, "Service Provider not valid.");
this.ClearErrors();
try
{
IDtsConnectionService connectionService = (IDtsConnectionService)this.ServiceProvider.GetService(typeof(IDtsConnectionService));
if (connectionService != null)
{
ArrayList temp_Connections =
connectionService.GetConnectionsOfType("FLATFILE");
args.AvailableColumns = new AvailableColumnElement[temp_Connections.Count];
for (int i = 0; i < temp_Connections.Count; i++)
{
ConnectionManager runtimeConnection = (ConnectionManager)temp_Connections;
args.AvailableColumns.AvailableColumn = new DataFlowElement(runtimeConnection.Name, runtimeConnection);
}
}
}
catch (Exception ex)
{
this.ReportErrors(ex);
}
}
void form_GetNewConnection(object sender, AvailableColumnsArgs args)
{
Debug.Assert(this.ServiceProvider != null, "Service Provider not valid.");
this.ClearErrors();
try
{
IDtsConnectionService connectionService = (IDtsConnectionService)this.ServiceProvider.GetService(typeof(IDtsConnectionService));
if (connectionService != null)
{
connectionService.CreateConnection("FLATFILE");
ArrayList temp_Connections =
connectionService.GetConnectionsOfType("FLATFILE");
args.AvailableColumns = new AvailableColumnElement[temp_Connections.Count];
for (int i = 0; i < temp_Connections.Count; i++)
{
ConnectionManager runtimeConnection = (ConnectionManager)temp_Connections;
args.AvailableColumns.AvailableColumn = new DataFlowElement(runtimeConnection.Name, runtimeConnection);
}
}
}
catch (Exception ex)
{
this.ReportErrors(ex);
}
}
Has anyone else run into this?
I am running
SQL 2005 9.0.1399 and VS 2005 8.0.50727.42 (RTM.50727.4200) on Windows Server 2003 Enterprise Edition SP1.
Any suggestions would be welcome.
The code sample is an extension to the RemoveDuplicates sample (Dec 2005) which comes along with the SQL Server.
TIA,
Robinson
I have created a custom script destination using an article found on TechNet. The script executes fine and completes, but with one small problem. The last package execution log entry states "wrote 0 rows". This is a problem because I use this number to verify that all of the database records have been properly transferred.
I have monitored that database when the script is executing and have verified that the data is being transferred property.
Is there some function that I should be calling with each row prcessed in the custom script destination to "tally" the row count? This way the script component will display the proper row count.
Any suggestions would be appreciated.
Hi,
All the examples I've seen for creating custom data destinations (scripts or components) show that in the ProcessInput method you need to iterate through each record and execute a commit for each row. Here's a sample from http://forums.microsoft.com/msdn/showpost.aspx?siteid=1&postid=70469
Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)
With sqlCmd
.Parameters("@addressid").Value = Row.AddressID
.Parameters("@city").Value = Row.City
.ExecuteNonQuery()
End With
End Sub
This works for me but is extremely slow when processing lots of rows. Has anyone come across a better/faster example for committing rows to a database when creating a custom data destination component? I was thinking about using the ADO.NET batch update. Any thoughts on this or other approaches?
Thanks,
Mike Fulkerson
I wrote a custom destination component. Everything works fine, except there is a logging message that is displayed that I cannot get rid of or correct. Here is the end of the output of a package containing my component:
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x0 at Data Flow Task, MyDestination: Inserted 40315 rows into C: empfile.txt
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "MyDestination" (9)" wrote 0 rows.
SSIS package "Package.dtsx" finished: Success.
I inserted a custom information message that contains the correct number of rows written by the component. I would like to either get rid of the last message "... wrote 0 rows", or figure out what to set to put the correct number of rows into that message.
This message seems to happen in the Cleanup phase. It appears whether I override the Cleanup method of the Pipeline component and do nothing, or not. Any ideas?
public override void Cleanup()
{
ComponentMetaData.FireInformation(0, ComponentMetaData.Name,
"Inserted " + m_rowCount.ToString() + " rows into " + m_fileName,
"", 0, ref m_cancel);
base.Cleanup(); // or not
}
Hi,
How do I retrieve the connections (connection managers) collections from Custom Data Flow destination? ComponentMetadata.RuntimeConnectionCollection is empty. I would like to be able to access all the connections defined in the package from the custom data flow task.
I came across code in which it was possible to access the Connections collection using the IDtsConnectionService for custom task (destination). The custom task has access to serviceProvider, whcih can be used to get access to the IDtsConnectionService interface but not the custom data flow task.
Any help appreciated.
Thanks
Naveen
Hi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
hi I have this asp sql statement which works fine but I want to use an spl adapter instead as I am enabling paging in details view. I am stuck in one place. How do I declare @listing as I have done in the select parameter in the asp sql statement? Where in ths sql adapter do i declare it? listing value is derived from a dropdown box when the user makes a selection.
This is the sql statement i want to declare in the adapter.<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" SelectCommand="SELECT UserDetails.UserId, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, aspnet_Membership.Email FROM UserDetails INNER JOIN
aspnet_Membership ON UserDetails.UserId = aspnet_Membership.UserId WHERE Category = @Listing">
<selectparameters>
<asp:controlparameter name="Listing" controlid="SearchListingDropDownList" propertyname="SelectedValue"/></selectparameters>
This is the adapter so far which gives me an error to declare scalable value @listing. How do i incoporate the select parameters below?
Function getTheData() As DataTable Dim DS As New DataSet()
Dim strConnect As New SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ConnectionString)Dim objSQLAdapter As New SqlDataAdapter("SELECT aspnet_Membership.UserId,UserDetails.FirstName + ' ' + UserDetails.LastName AS 'Customer',UserDetails.City, aspnet_Membership.Email FROM aspnet_Membership INNER JOIN UserDetails ON aspnet_Membership.UserId = UserDetails.UserId WHERE Category = @Listing", strConnect)objSQLAdapter.Fill(DS, "aspnet_Membership,UserDetails")
Return DS.Tables("aspnet_Membership,UserDetails").Copy
End Function
www.mylookmeup.co.uk
I am running Windows XP and have SQL Server 2005 Enterprise Edition that I use for testing on my notebook. I want to install Oralce 11g on it as well and use VS 2005 as a front end programming. For me to install Oracle I need to install a loopback adapter. If I do this will it mess up SQL Server 2005 or will it not affect SQL Server 2005? Please advise
View 1 Replies View RelatedCan someone tell me why I an getting the following error message for my table adapter: "incorrect syntax near '?'"
The selecte statement is below.
SELECT Questions.QuestionID, Questions.QuestionNumber, Questions.QuestionText, Questions.SampleAnswer, SAFSection.SectionNumber + ' - ' + SAFSection.SectionName as [Section], SAFSubSection.SubSectionNumber + ' - ' + SAFSubSection.SubSectionName as [SubSection], SAFSubSection.SubSectionDefinition, SAFSubSection.SubSectionQuestion, SAFSubSection.SubSectionInstruction, Answers.AnswerFROM (SAFSubSection INNER JOIN (SAFSection INNER JOIN Questions ON SAFSection.[SectionID] = Questions.[QuestionSectionID]) ON SAFSubSection.[SubSectionID] = Questions.[QuestionSubSectionID]) LEFT JOIN Answers ON Questions.QuestionID = Answers.QuestionIDWHERE Answers.SystemID = ?Order BY SAFSection.SectionID, SAFSubSection.SubSectionID,Questions.QuestionID
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
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 ?
In VS2003 I have a user defaults table that I would call in the OnLoad - No Postback section to get the user defaults and then used some of the fields from that recordset as Parameters for other recordsets that would also load in that same section and might even issue a different Sql statement based on the field. In VS2005 (VB.NET), I can no longer figure out how to accomplish this. I can use the SQLDatasource Control which seems much improved but I cannot even find the generated code which I would use as a quick way to create my own datasources in the OnLoad event. Where is this code now stored and how can I accomplish my objectives in VB.NET 2005 with the current framework or programatically create data sources as necessary as I did before?
View 3 Replies View RelatedHello,
I have a table adapter which uses a Query string to get the select view and a stored procedure for its delete portion. Not all of the columns in the select statement are required for the stored procedure. When I try to do the delete, it attempts at passing in more variables than it needs and ends up resulting in an error saying it can't find a procedure that is valid. Is there any way to restrict which columns are being passed?
Thanks,
Chris
I have been struggling with the Adapter Fill issue for days.
I got an eeror "Invalid object name 'Weather'." Why? How to modify it?
Thanks
Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using My.Name.Space;
public partial class GetStrings : System.Web.UI.Page
{
getStrings g = new getStrings();
protected void Page_Load(object sender, EventArgs e)
{
string strConnectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
SqlConnection myConnection = new SqlConnection(strConnectionString);
DataSet myDataSet = new DataSet();
try
{
myConnection.Open();
// create a new DataSet
// create the data
GenerateDataSet(myDataSet, myConnection);
}
finally
{
myConnection.Close();
// bind each to table to a grid
GridView1.DataSource = myDataSet.Tables["Weather"];
}
// databind the page
GridView1.DataBind();}
void GenerateDataSet(DataSet dset, SqlConnection conn)
{
AddTable(dset);
FillTable(dset, conn);
}
void AddTable(DataSet dset)
{
// create the table
DataTable WeatherTable = new DataTable("Weather");
DataColumn WeatherID = WeatherTable.Columns.Add("State", typeof(string));
WeatherTable.Columns.Add("Division", typeof(String));
WeatherTable.Columns.Add("TEMP", typeof(string));
WeatherTable.Columns.Add("PCPN", typeof(string));
WeatherTable.Columns.Add("UL", typeof(string));
WeatherTable.Columns.Add("LL", typeof(string));
WeatherTable.Columns.Add("PCT", typeof(string));
WeatherTable.Columns.Add("POT", typeof(string));
WeatherTable.Columns.Add("RO", typeof(string));
WeatherTable.Columns.Add("MOIST_INDEX", typeof(string));
WeatherTable.Columns.Add("CF", typeof(string));
WeatherTable.Columns.Add("MNA", typeof(string));
WeatherTable.Columns.Add("DROUGHT_INDEX", typeof(string));
WeatherTable.Columns.Add("DROUGHT", typeof(string));
// set the column properties
WeatherTable.Columns["State"].MaxLength = 50;
WeatherTable.Columns["Division"].MaxLength = 50;
WeatherTable.Columns["TEMP"].MaxLength = 50;
WeatherTable.Columns["PCPN"].MaxLength = 50;
WeatherTable.Columns["UL"].MaxLength = 50;
WeatherTable.Columns["LL"].MaxLength = 50;
WeatherTable.Columns["PCT"].MaxLength = 50;
WeatherTable.Columns["POT"].MaxLength = 50;
WeatherTable.Columns["RO"].MaxLength = 50;
WeatherTable.Columns["MOIST_INDEX"].MaxLength = 50;
WeatherTable.Columns["CF"].MaxLength = 50;
WeatherTable.Columns["MNA"].MaxLength = 50;
WeatherTable.Columns["DROUGHT_INDEX"].MaxLength = 50;
WeatherTable.Columns["DROUGHT"].MaxLength = 50;
GetDataRow(WeatherTable, g.gets());
// add the table
dset.Tables.Add(WeatherTable);
}
void FillTable(DataSet dset, SqlConnection conn)
{
// create the Command and DataAdapter
SqlDataAdapter WeatherAdapter = new SqlDataAdapter();
SqlCommand WeatherCommand = new SqlCommand("SELECT State, Division,MOIST_INDEX,DROUGHT_INDEX FROM Weather", conn);
WeatherAdapter.SelectCommand = WeatherCommand;
// fill the DataTable
WeatherAdapter.Fill(dset,2,0,"Weather");//wrong here,
//WeatherAdapter.Fill(dset.Tables["Weather"]);
}
//assuming the first dimention is collumn and the second is row
public void GetDataRow(DataTable datatable, string[][] source)
{
DataRow dr;
int rows = source.GetLength(0);
int collumns = source[0].Length;
for (int i = 0; i < rows; i++)
{
dr = datatable.NewRow();
if (source[i] != null)
{
dr.ItemArray = (object[])source[i];
datatable.Rows.Add(dr);
}
}
datatable.AcceptChanges();
}
}
Hi All,
Using a data access mode of SQL Command, I have a stmt that returns one date ( e.g select max(LastChangedDate) from TblA ).
I want to store the result in a variable. How do I do this?
Cheers,
Tamim.
I downloaded and installed the Informix Client SDK 3.0 and was able to extract data from an Informix 10 database. All of a sudden, it stopped working. The error message I received is "... error in initializing provider. (-25580) System error occurred in network function.". Has anyone encountered this error before and know of a solution?
Thanks.
Now, I don't know if what I want to do is possible, but here goes. In the table I want to query, there is an "approval status" column, of type Int32. There are four approval levels, 1, 2, 3 and 4. What I want to set up is a query in the table adapter that can return all entries of one or more approval levels. In "raw" sql, I would do something like: SELECT * FROM facility_table WHERE (approved IN (1,2,3));What I want to do though, is to have the list of approval codes to be a parameter that I can pass to the table adapter query, so the where clause becomes "WHERE (approved IN (@approval))", and I pass a string with the list of approval codes. But the query designer doesn't want to cooperate with me, as it insists that "@approval" should be an int32.Any suggestions?
View 1 Replies View Related