.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Simple Data Import Help

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server

I know I am new to SSIS but first impressions mean a lot and what it is telling me is that SSIS is NOT user friendly and is extremely complicated to do simple tasks that can be done using windows services in 5 minutes.  With that opinion being said, all I am trying to do is the following:

1. I have two databases A and B

2. I need to run about 5 different SELECT queries to gather data from A

3. I need to run an insert statement using the 5 result sets from A to move data into a table in B

In a service places the data into datatables which are then merged into a single datatable and then bulk copied to the new database.  I can also do it by hand exporting the results of the queries to a csv file and then importing the data to the new table.  Anyone know how to do this in SSIS because it does not appear straight forward.  I have tried Execute SQL Task but it doesn't support DECLARE statements and everytime I do it per query I get the following error:

"The type of the value being assigned to variable "User::DATA" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object."

View Complete Post

More Related Resource Links

XML in .NET: .NET Framework XML Classes and C# Offer Simple, Scalable Data Manipulation


Microsoft .NET introduces a new suite of XML APIs built on industry standards such as DOM, XPath, XSD, and XSLT. The .NET Framework XML classes also include innovations that offer convenience, better performance, and a more familiar programming model, tightly coupled with the new .NET data access APIs-ADO.NET. XmlWriter, XmlReader, and XmlNavigator classes and classes that derive from them, including XMLTextReader and XMLTextWriter, encapsulate a number of functionalities that previously had to be accomplished manually. A discussion of the XMLDOM-Document is also included.

Aaron Skonnard

MSDN Magazine January 2001

A simple Data Modeling Question


I have a data modeling question that hopefully this fictious example will help explain.  My application is data driven and the classes reflect the tables to a large degree.   In this example my database has two tables:

  • VendorMaster with an int Primary Key of VendorID
  • VendorContact with an int Primary Key of ContactID

The VendorContact table also has a Foreign Key of VendorID in order to associate the contact with a specific Vendor.  In this example a Vendor can only have one contact.

My question is how to represent these two tables in terms of classes.  In my current design I have two classes:

  • VendorMaster
  • VendorContact

In order to represent the relationship between a VendorMaster and VendorContact, my VendorMaster class contains a property called VendorContact.

My questions are about the VendorContact class and how it represents the relationship between a VendorMaster and itself.

Option #1

The VendorContact class should contain an int property of VendorID

Option #2

The VendorContact class should contain a VendorMaster Property

I am not sure which option is the 'best' option to take. My concern is that if I go with option #2 that I would create a cyclical relationship between the VendorMaster and Vendo

Data Import issues: Access 2003 .mdb to sqlce 3.5 on mobile device

I am trying to download wirelessly or cradled, data from 3 Access 2003 tables into my SQLCE 3.5 datatables. I already have a successful method + web method that uploads data from my sqlce datatables into the Access 2003 tables. I just need feedback to know if I'm headed in the right direction. Web method: Public Function UpdateSqlCeData(ByVal downloadSet As DataSet) As Boolean ' Connect to Access DB here, execute command and populate data set with returned data. Me.Server.ScriptTimeout = 600000 Dim strSql2 As SqlCeCommand Dim oleDbDataset As System.Data.DataSet = New DataSet Dim sqlParam As New SqlCeParameter Dim fhAccDt, fhCeDt As New DataTable strSql2 = Nothing sqlParam = Nothing 'Connection string to connect to access DB Dim FHLiveDB As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\testpre\testservertest.mdb") Dim Pbrd As New OleDb.OleDbDataAdapter("SELECT * FROM tblProductBrand", FHLiveDB) FHLiveDB.Open() Pbrd.Fill(oleDbDataset) With strSql2 .CommandTimeout = 600000 .CommandText = "INSERT INTO Product_Brand ([BrandID],[Brand_Name]) VALUES (@Brand_ID,@Brand_Name)" sqlParam = .Parameters.Add("@Brand_ID", SqlDbType.NVarChar, 50, "Brand_ID&

how to import the data of excel into gridview1 using asp.net/c#

i m having excel file a.xlsi m having gridview1,fileupload1how to import excel file from fileupload1 get the destination of a.xls and show the values in gridview1if any boby knows full code answer/mail me at ponmanivannangj@mobiusservices.in

Simple data binding question

Newbie binding question here: is it possible to bind a control in XAML to a StaticResource by passing in the key via a data structure? Basically something like this: <TextBlock Text="{Binding ImageKey}" /> <Image Source="{StaticResource ResourceKey={Binding ImageKey}}" /> <-- doesn't work   The TextBlock binding on the first line works correctly, it extracts the key of the StaticResource from my data structure and displays it correctly. What I'm trying to do is get the Image control to bind to the StaticResource with that name (which happens to be a BitmapImage).      

User Profiles Service Application and Import of SharePoint 2007 SSP data

I have setup a test SharePoint 2010 Farm. I will be using this as a test upgrade of a current live SharePoint 2007 Farm. The database attach method will be used. I have replicated the web application and AAM settings of the SharePoint 2007 Farm to the SharePoint 2010 Farm and have made the 2010 Farm a DC in a new Forest. I don't want to join this to the current domain at the moment. It also has SQL server 2005 with SP3 and cumulative update 3 installed. I have just setup the User Profiles Serice Application and when I go to Manage it, I get this. Error An unexpected error has occurred.   Troubleshoot issues with Microsoft SharePoint Foundation. Correlation ID: a1760e87-372f-4711-afac-3ceba34bc599 Date and Time: 8/31/2010 4:22:56 PM I have verified and configured the following. Created the Managed Metadata Service. The status is started via Service Applications and Services on Server. Created the User Profiles Service Application and ensured status is started via Service Applications. I started the User Profile Service and User Profile Synchronisation Service via Manage Services on Server.      

Import Excel data into SQL Server express 2008

I have recently installed SQL express 08 and created a table to import excel data into.  Im running on a windows 7 64 bit OS i5 processor.  I've disabled the firewall (temporarily) and will set the necessary ports once this is working. I run the following query:  (which i found from multiple sources on the net) Select   * From OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Users\Public\lenovoBkup\contacts\outlookFiles\noib2a.xls', 'Select * From [sheet1$]' )  and receive the following message: Msg 7308, Level 16, State 1, Line 1                                                                                  OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.  (how did they know i was single and living in an apartment? lol) so after research of the following message i restarted the system and relaunched man

Import data without duplicating

Gooday How can i import names into a data column using sql managment studio but without duplicating the names . thanks robrh

import data to sql

HI all,Im a really noob in asp.net.I created a small application with Web developer express for a while.I have a sql server running for the database and a webserver for the frontend.So, now i would improt some data from an athore databes to the my database which allreday have data inside.Can i do that with web developer express, can any one help me to do this?which type should the iport data should be xls or txt or so on?best regards

Import and Match data

Gooday , how can i import data to fill in null values , using the first row as a target to match the other rows.  i tried this , but ,,,(you know)                                              thanks   rob INSERT INTO Personel (FullName, company , position) SELECT FullName , company, position FROM Name  AS B WHERE  EXISTS (SELECT FullName ) but company or position = null                                                                                                      From Personel  AS A WHERE  A.FullName = B.FullName)rh

Import a flat file with combined data into separate SQL tables using SSIS

I have a flat text file (comma delimited) that is essentially multiple files, each with its own format, combined into one file. The file is coming from an external software vendor so unfortunately we don't have much choice but to work with what we are receiving. Here is an example of what the file could look like: Customer Data CustID,FName,LName,PhNum,Email 12345,John,Smith,,jsmith@gmail.com 12346,Jane,Doe,8001111111,jdoe@hotmail.com Customer Plan CustID,PlanType,PlanName,PlanStart 12345,0,Plan1,01/01/2010 12345,2,PlanVis,01/01/2010 12346,3,PlanLf,04/01/2010 12346,0,Plan1,01/01/2010 Customer Payment CustID,LastPayment,Amount 12345,09/01/2010,100.00 12346,05/01/2010,50.00 There is an empty line between each 'section' of data. I adapted a VB script I found online that can take the incoming file and save off each section as its own file so that each one can be separately imported, but this seems inefficient. I'm really new to SSIS in general, but it seems like it shouldn't be that difficult to take the data, split it where there is an empty line, and then import each section into the appropriate SQL table. Any ideas would be most welcome. Thanks!  

Help - simple data binding

As a newbie I am struggling with the data binding syntax. In the code below I have used a class to create a simple data structure. (I need to be able to fill this from an array which I can do.) For this example I have filled it in directly. However, despite exhaustive searching in the web I cannot find a Visual Basic example showing me how to bind this data source to a Datagrid. I have included in the code the xaml for a basic data grid. Can anynbe help me with the xaml to bind the fields to textboxes? Class MainWindow Public test As String ' Public mage() As Integer = {24, 35, 46, 57} Private Sub Window_Loaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles MyBase.Loaded Dim P1 As New myPerson(111, "Bob", 23) Dim P2 As New myPerson(222, "Fred", 34) Dim P3 As New myPerson(333, "Bill", 17) Dim P4 As New myPerson(121, "Alf", 44) Dim st As New SortedList st.Add(P1.name, P1) st.Add(P2.name, P2) st.Add(P3.name, P3) st.Add(P4.name, P4) Dim P As myPerson Dim i As Integer For i = 0 To st.Count - 1 P = CType(st.GetByIndex(i), myPerson) Console.WriteLine("The age of " & P.name & " is " & P.age) Next End Sub End Class Public Class myPerson Public id As Integer Public name As String Public age As In

Simple Data Extension

Hi I have a requirement to apply security filtering (column and row level) above and beyond the capabilities of SSRS Model security. in 2005 and 2008 I achieved this by creating a data Extension and tweaking the Command text and execute reader methods, and all was Well with the world. Now all I wanted was a bit of customisation of the SQL data Extension  (SQL Connection Wrapper)... of course its Sealed so couldn't inherit, so I composite / wrapped it... and all was well with the world......  Now along comes SQL2008 R2 .... and someone has discovered the internal Keyword !  now everything is locked down.  the Constructor of SQL Connection Wrapper  is internal. Luckily my code is not yet producion code, and I know Microsoft are completely within their rights ... but why!!! ... all is not good with the world, I doubt I am alone in this ! Any suggestions how to implement a minor customisation to SQL data Extension, without reimplementing the whole of the dataExtensions DLL ! Any chance the decison might be reversed ?  regards Iain   

Import excel sheet data to datagrid without using oledb

In my application(C#) i want to import excel sheet to show its data in DatatGridView i dont want to use oledb Need sample code can any any Help

Import Excel data into SQL db

 First off sorry if this is in the wrong place but there are over a dozen forum sections here and this seems to be the only one that comes close to what I need.. anyway ... I'm trying to use Visual Web Developer 2008 express to create a form that will allow a user to select an excel file and upload it's values to SQL. I found the following code in another post but as I'm a newbie to C# I'm confused about what 'using' items it needs to run. I keep getting errors like 'System.Web.UI.Page.Application' is a 'property' but is used like a 'type' and "The type or namespace name 'Workbook' could not be found (are you missing a using directive or an assembly reference?)" So what kind of obvious stuff am I overlooking? Or is this just an impossible task for a web app?  1 public DataSet GetExcel(string fileName)2 {3 Application oXL;4 Workbook oWB;5 Worksheet oSheet;6 Range oRng;7 try8 {9 // creat a Application object10 oXL = new ApplicationClass();11 // get WorkBook object12 oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,13 Missing.Value,

How to import Data from Excel to SQL Server Express

 Hi,  please any one is expert in Data Base help me, i would like to import two Data columns from Excel file 2003 instead of two columns in a table in Sql Server Express 2005 , How Can i do that? and what is the way if I have Sql Developer 2005, thank you .  

Simple Excel Import Question

I have an excel file I am importing into a table. The excel file is named with a date (02102010_Excel_file.xls). I created a very simple import package. This simple two object package - Excel Source to OLE DB destination works just fine. But..   My problem is that the Excel file name will change every day to todays date (09162010_ExcelFile.xls) So,  I need to change my Excel Import  to something like *.xls but that doesn't work. How can I Import a *.xls file? qeqw
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend