Identity Field Settings Not Copied By Import Wizard (2005)
It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column
[ref] [int] IDENTITY ( 1 , 1 ) NOT NULL,
When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be
[ref] [int] NOT NULL,
instead. Is there a way to change this behavior somewhere in the gui? When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column.
-Eric
View Complete Forum Thread with Replies
Related Forum Messages:
Importing Tables With Identity Properties (uisng Import Wizard)
I am using the Import wizard to import a SQL2000 database to SQL2005 and noticed 2 problems: 1. all tables and views were selected; the tables were imported correctly but the views were created as tables, ignoring the "Create view" syntax. The SQL generated contains "Create table" syntax instead of "Create View". 2. when a table contained a column with an "identity" property, the data was successfully imported, but the values for the "identity" column were not preserved, instead they were resquenced from 1 with an increment of 1 (the default values for an identity). When I opened the "Edit" (under "Mapping"), "enable identity insert" was not checked. A further note: I created all tables in the SQL2005 database before running the Import.
View Replies !
Importing Data From Oracle 8i/9i To SQL Server 2005 Using SQL Server Import And Export Wizard (AKA DTS Wizard)
Hi All, I have become frustrated and I am not finding the answers I expect. Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough. So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good. I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise. When I get to the end it progresses to about 20% and then throws this error about 300 or so times: Could not connect source component. Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used. So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property! Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so. This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!! This generates 2 questions: 1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection 2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times. Help!!!
View Replies !
DTS Wizard SQL 2005 - Enable Identity Insert
I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.
View Replies !
SQL 2005 SP 2 Import Wizard
I select a database then right click and select import data. The import wizard screen shows then I click Next and get the following error. The wizard will close because it encountered the following error: Additional Information> No description found. =================================== This wizard will close because it encountered the following error: (Microsoft SQL Server) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476 =================================== No description found I am at a loss to fix this error. Is there another way to import data without using the wizard? Robert
View Replies !
SQL 2005 Import Wizard
I just installed SQL 2005 and am having trouble with the import wizard. After I select import data I get the following error. I am running SQL on XP. Does anyone have a lead on this error? =================================== This wizard will close because it encountered the following error: (Microsoft SQL Server) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476 =================================== No description found ------------------------------ Program Location: at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_DBProviderInfos() at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetDtsDbProviderInfos(WizardInputs wizardInputs) at Microsoft.SqlServer.Dts.DtsWizard.DataSourceCombo.PopulateProviders(Boolean fSources, WizardInputs wizardInputs) at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnInitializePage(EventArgs e) at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage() at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage nextPage) at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender, EventArgs e)
View Replies !
SQL Server 2005 Import And Export Wizard
Hi Everyone, I currently create packages using the Import and Export Wizard in SQL Server 2005 Express Edition. How can I log error messages, if any, to a file with the packages I created using the wizard? I do not have SSIS. Any way I can accomplish without SSIS? Thank You! -Sam
View Replies !
Help Import Export Wizard SQL Server 2005
Hi! I have XP sp2, with SQL server 2005 Developer Edition 9.00.3054.00 and also have Microsoft SQL Server Express Edition 9.00.3042.00, well my problem alway I want import data into any server from any data source I recive this message: TITLE: SQL Server Import and Export Wizard ------------------------------ The operation could not be completed. ------------------------------ ADDITIONAL INFORMATION: Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the file specified. (System.EnterpriseServices) ------------------------------ Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the file specified. ------------------------------ The system cannot find the file specified. (Exception from HRESULT: 0x80070002) ------------------------------ BUTTONS: OK ------------------------------ I have this name space but, I try for many ways and I can't import data in my server, What can I do??? Thanks a lot.
View Replies !
MS SQL 2005 Import Export Wizard - SAP R/3 Connection
Hi I am trying to import SAP R/3 Data to MS SQL 2005 with SQL Server Import and Export Wizard (.net framework Data Provider for mySAP Business Suite. ) for ie i have to import data from mara table. System Number of the R3 environemnt 00 but i am getting error at the time i am making connect from MS SQL 2005 to SAP R/3 ... Following is the error The operation could not be completed Additional information --> Missing SYSNR = ... in connect_param in RFcOpenEx(Microsoft.Adapter.SAP.SAPProvider) |--> Missing SYSNR = ... in connect_param in RFCOpenEx ( GInvoker) Can any one explain me where i am going wrong?
View Replies !
Import Wizard SQL Server 2000 Vs. 2005
I'm importing a fixed or tab delimited text file into a SQL Server database. When I use SQL 2000 import wizard about 10,000 rows are missed or skipped, but when I use SQL 2005 all the rows are imported successfully. If anyone has any ideas, I would greatly appreciate it :-) Thanks!
View Replies !
Help - Convert/import Access Data Into Sql 2005 Express... W/dts Wizard....?
i have a website project making an online catalog, maybe 20 categories, a few hundred items. the small business starter kit looked perfect to jump start this. the client has provided an access database with the two table for categories and products. i've got the dts wizard installed and have been trying to import the access data into the sbsk database but i'm stuck. i'm almost positive that ONE time when i was banging around with the dts wizard, the Edit Mapping screen would let me drop down a list of fields in the destination table so I could actually tell it which field data from the source i wanted to go into which field of the destination. Of course it didn't succeed that time and now i can't seem to figure a way to get back to having the option to choose the destination fields, all it gives me in the drop down is "<ignore>" or the field name of the source table, which of course doesn't match any of the ones that are in the sbsk. i've been back through it for a couple days, trying different ways, migrating the data first to sql, letting it try a transfer to a new table then trying to import that, moving both out to excel, aligning the data manually and trying to import that, etc. i'm bout out of ideas and would sure like to get back to just trying to work out the data type alignment issues with whatever it was i did when i could choose the destination table's fields. i'd be most appreciative if anyone has a clue what i'm talking about and can put me back on track. matthew
View Replies !
Data Migration Using Import/export Wizard SQL 2000 To SQL 2005
I am trying to import data from SQL 2000 to SQL 2005 The Data schemas are the same in both (tables, relationships, key and triggers). Using the wizard I am trying to import data from SQL 2000 to SQL 2005. The issue I face is to manage the Idenity columns. I do not want the identity values from the SQL 2000 DB which may be at say 5000, instead I want to use the identity values from the SQL 2005 database which will problably start at 1 Is there a way to do this, should I use a custom query instead of a direct table dump. If using a custom query do I need to just specify a Select query or does it have to be a specific "INSERT INTO Table ..." type query If I do import the data directly with Identity "ON" how can I advance the identity column value to current +1 on the SQL 2005 table. Thanks javahar
View Replies !
Doing A Data Import Using DTS Wizard In SQL Server 2005 - Being Efficient With 5 Flat Files
Hi, I'm a new user of SQL Server 2005. I have the full version installed. I also have SQL Server Business Integration Dev Studio installed. My OS is Windows XP. I'm importing a series of 5 flat files into a database on one of the SQL Servers we have. My goal is to get 5 different tables (though perhaps I should do one and add an extra field to distinguish each import) into the database for further analysis. I tried doing an import via DTS Wizard. There are no column names in the flat file so I defined them during the import process (all 58 of them). When I got to the end, I had an option to save the import process as a SSIS (SQL Server Integration Service) Package on: SQL SERVER (I don't have permission for this) or FILE SYSTEM (did this one) I saved the Package locally in hopes of being able to go back in, change the source file and destination table of the package and quickly get the other 4 flat files imported. My problems are: 1) I couldn't find how to run the *.DTSX Package file to run in SQL Server Studio (basically reuse the Package with minor changes and saving me having to redefine the same 58 columns on each flat file import) 2) Tried but didn't understand how to run it in SQL Server Bus Intel Dev Studio (i.e. understanding the mapping and getting the data types right so it wouldn't error out) 3) Don't know how to make the necessary changes so that the Package handles the next source file and puts in a new destination table (do I need to do 5 CREATE TABLES so this Package has a place to run to?) 4) Does the Package need to be part of a Project to run (I haven't found how to take an existing Package and make it part of a Project/Solution)? 5) Is there a good book or online resource for just getting the basics of using SQL Server 2005 and SQL Server Business Intelligence Development Studio? I'm really at a loss after spending a day fruitlessly on it scouring the help files, forums and experimenting around. Hope somebody can point me in the right direction. Regards, Patrick Briggs, Pasadena, CA
View Replies !
SQL Server Managment Studio Express 2005 Import/Export Wizard
I've created a new database and restored the database from a backup. I'm wanting to import raw data from an excel spreadsheet into the tables of the new database. When I right click on the database, and hover over tasks the import/ export data menu options are not in the menu.The menu items ends at 'Generate Scripts'. I'm connected on a local instance to a registered server. Can anyone advise?
View Replies !
Doing A Data Import Using DTS Wizard In SQL Server 2005 - Being Efficient With 5 Flat Files
I just spent some time working out how to do a seemingly simple task. I€™m sharing the steps I took to do this in hopes it saves other SQL Server 2005 users (especially newbies like myself) time. My original question posed on several SQL newsgroups was based on this goal: I'm importing a series of 5 flat files (all with same file layout) into a database on one of the SQL Servers we have using SQL Server 2005 (SQL Server Management Studio) . My goal is to get 5 different tables. I want to do this without having to redo all the layout criteria 4 additional times. Below are the steps I followed to get a solution (all done in Microsoft SQL Server Management Studio): Create the Package (data import) 1) Use the SQL Server Import Export Wizard (equivalent to SQL Server 2000 Data Transfer Wizard) to import your first flat file. At the CHOOSE DATA SOURCE window browse for your file. 2) Under the Advanced tab, you can set your Column attributes (€śoutput column width€? or €śdata type€? to name a few). I highlighted all the columns and selected €śstring [DT_STR]€? for data type. To avoid truncation errors, I selected 255 for output column width. You can name the columns whose data you are most concerned with (I did import all the available fields). 3) After choosing a server destination you will have a €śSELECT SOURCE TABLES AND VIEWS€? window pop up. Under the €śMapping€? column you can choose to tweak your mapping further editing in SQL (see Edit SQL button). I didn€™t. 4) The €śSAVE AND EXECUTE PACKAGE€? will pop up. The €śExecute Immediately€? box should be checked and you should check the €śSave SSIS Package€? (SQL Server Integration Services). When you do, select €śFile System€? for where to save this import-file-package to. 5) Click OKAY for the Package Protection Level and the €śSAVE SSIS PACKAGE€? window will appear. Browse for a path on your local computer to save to. Modify Package (data import) for Next Use 6) In SQL Server Management Studio, browse for the Package and open it. Preparation for SQL Task €“ box 7) You should see a screen that shows two boxes (€śPreparation for SQL Task€?) and (€śData Flow Task€?). 8) Right click on the former and select €śEdit€?. 9) On the €śSQL Statement€? row, click into the right column and select the €ś€¦€? box 10) Change the destination table (the table you will create with this package) to a meaningful name and click OK. 11) Click OK for the €śSQL Task Editor€? Data Flow Task - box 12) Right click on the €śData Flow Task€? box and select €śEdit€?. 13) Three boxes will appear €śSourceConnectionFlatFile€?, €śData Conversion 1€?, and €śDestination - <whatever table name your original data import went to>€?. Below them is a section that displays €śConnection Managers€? SourceConnectionFlatFile - editing 14) The first thing you will want to do is change the import source to a new flat file. You do this by going below the boxes under the €śConnection Managers€? window and right clicking on €śSourceConnectionFlatFile€? and then selecting €śEdit€? 15) Browse for the new €śFile Name€? and select it. 16) A €śMicrosoft SQL Server Management Studio€? window will pop up asking you if you want to €śkeep or reset the existing metadata€?. The metadata is just your column definitions and choosing €śYES€? to keep this makes sense if you are doing data imports on files with the same file layout. 17) Still in the €śFlat File Connection Manager Editor€? window, change the €śConnection Manager Name€? to something meaningful (I add <_> at the end and then the name of the table the flat file is going to) and click OK. SourceConnectionFlatFile €“ box (editing) 18) Right click on the €śSourceConnectionFlatFile€? box and select €śEdit€?. 19) Your newly named €śFlat File Connection Manager€? should appear in select box. 20) Click OK, right click again on the €śSourceConnectionFlatFile€? box and select €śShow Advanced Editor€?. 21) Under the €śConnections Manager€? tab, your newly named €śFlat File Connection€? should appear (the prior step is necessary for the advanced editor to recognize your change). 22) Under the €śComponent Properties€? tab, on the €śName€? row, click into the right column and rename to something meaningful (notice the €śIdentification String€? row description changes too once you click out of the €śName€? row) 23) Under the €śColumn Mappings€? tab, just confirm you are mapping your flat file fields (€śAvailable External Columns€?) to a destination table€™s fields (€śAvailable Output Columns€?). 24) Under the €śInput and Output Properties€? tab you can check in €śFlat File Source Output€? to make modifications to either your €śExternal Columns€? or your €śOutput Columns€? €“ you shouldn€™t need to for a simple import. ((NOTE: any changes you make here would likely need to be consistent with the column properties found under the €śConnection Manager Window€? for the €śSourceConnectionFlatFile€? as well as the €śData Conversion 1€? box under the €śData Flow Tasks€? window, so exercise caution 25) NOTE: This process has worked for me by making my source columns all €śstring [DT_STR]€? data type and the output columns all €śUnicode String [DT_WSTR]€? data type. Data Conversion 1 €“ box (editing) 26) There is nothing you need to do here. By right clicking on the €śData Conversion 1€? box and selecting €śEdit€?, you can see and change the data type of the output columns (the ones in the table your importing the flat file to). There are probably more edits one can do but they€™re beyond what I€™ve learned. Destination - <whatever table name your original data import went to> €“ box (editing) 27) Right click on the €śDestination - <whatever table name your original data import went to>€? box and select €śShow Advanced Editor€?. 28) Select the €śComponent Properties€? tab. 29) Select the right column at the €śName€? row and change the name to something meaningful (ie. related to the source file name or the table name you€™re importing to). 30) Select the right column at the €śIdentification String€? row and it will update to this change. 31) Select the right column at the €śOpenRowSet€? and change it to the name of the table you are importing your flat file to (this should be consistent with table name under step 10). 32) Click OK 33) Select FILE and select €śSave As€¦€? and then give your package a new name that€™s meaningful (this will be helpful if you have to rerun the import of the flat file later). Run (execute) the Revised Package (data import) 34) Go back to SQL Server Management Studio and open the Object Explorer 35) Connect to an €śIntegration Services€? component. This should essentially be a local instance (not sure where it is on the local computer or in SQL Server Management Studio on the local computer). 36) In €śObject Explorer€? go down to your €śIntegration Services€? object and expand it. 37) Expand €śStored Packages€? 38) Right click on €śFile System€? and select €śImport Package€? and an €śIMPORT PACKAGE€? window will appear 39) For €śPackage Location€? choose €śFile System€? and then browse for the €śPackage Path€? 40) Click into the €śPackage Name€? and it defaults to your Package€™s file name. 41) Click OK and the Package is imported. 42) Right click on the newly imported Package and select €śRun Package€? 43) An €śExecute Package Utility€? window appears 44) Select €śExecute€? and the package runs.
View Replies !
Import And Export Data Wizard Missing In SQL Server 2005 Express Edition
After some weeks evaluating tools and platforms for developing an application, I decided to move to SQL Server 2005 Express Edition. Everything was fine till last night, when after creating my tables, I needed to populate them. I tried to find the Import and Export Data Wizard that SQL Server 7.0 and 2000 used to have, but great was my surprise when I found - in this forum - a post that said that it's not available in the Express Edition. I'll have to move back in time (what I hate) to remember the way BCP worked. Can somebody post some examples to not start from zero ? Does anybody know a third party visual tool that can import/export data from text files to a SQL Server DB via ODBC ? What's the reason it was not included in the EE ? THanks !
View Replies !
SQL Server Import And Export Wizard Fails To Import Data From A View To A Table
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server. I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard. However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection Operation stopped... - Initializing Data Flow Task (Success) - Initializing Connections (Success) - Setting SQL Command (Success) - Setting Source Connection (Error) Messages Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (SQL Server Import and Export Wizard) Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap) - Setting Destination Connection (Stopped) - Validating (Stopped) - Prepare for Execute (Stopped) - Pre-execute (Stopped) - Executing (Stopped) - Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped) - Post-execute (Stopped) Does anyone encounter this problem before and know what is happening? Thanks for kindly reply. Best regards, Calvin Lam
View Replies !
Error Trying To Import MS Access 2003 Database Via SQL Server Import And Export Wizard - Too Many Sessions Already Active
I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition. I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work. Trouble is that it gets most of the way through the import until it spews forth the following error messages: - Prepare for Execute (Error) Messages Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.". (SQL Server Import and Export Wizard) Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. (SQL Server Import and Export Wizard) Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C. (SQL Server Import and Export Wizard). There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem. Does anyone know how I can get the import to work?
View Replies !
Error Regarding File Import Through Import Wizard
Hi all, when trying to Ămport files to our database server from a client, I keep getting an error: - Validating (Error) Messages Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source_txt" (1). (SQL Server Import and Export Wizard) Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (175). (SQL Server Import and Export Wizard) ... doing the same import when logged on the server, hasn't been giving me any errors, how come. I can from my client without trouble import tables from other DB servers but when ever it is files it won't do it. I tried as mentioned in other threads rerun setup to re-install SSIS, but as it was already installed it wouldn't re-install. My next move would be to make a clean install, but not sure it would help, as I think this is a buck. best regards Musa Rusid
View Replies !
Import And Export Wizard: Transferring Multiple Tables From SQL Server 2005 To SQL Server 2000
Hi! I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K. I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.) Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy. Basically the wizard creates the following Control Flow tab entries (in processing sequence order): an Execute SQL Task: NonTransactableSql an Execute SQL Task: START TRANSACTION a Sequence Container: Transaction Scoping Sequence, which contains an Execute SQL Task: AllowedToFailPrologueSql an Execute SQL Task: PrologueSql a Foreach Loop Container, which contains a Transfer Task with an icon I did not notice in the Toolbox an Execute Package Task: Execute Inner Package an Execute SQL Task: EpilogueSql an "on success" arrow to an Execute SQL Task: COMMIT TRANSACTION an Execute SQL Task: PostTransaction Sql an "on failure" arrow to an Execute SQL Task: ROLLBACK TRANSACTION an Execute SQL Task: CompensatingSql Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information? This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table. So now I'm trying to understand why the Wizard created this more-complex package. Any help will be appreciated, including references to non-Microsoft books/websites/etc. Thanks in advance. Dan
View Replies !
Insert Row In Table With Identity Field, And Get New Identity Back
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table). What is the most direct way to do this in SSIS? TIA, barkingdog P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
View Replies !
The Wizard Stole My IDENTITY
Being new to SQL, I started with the basics... using VS2005, it seemed easy enough. Not a single Sql statement was coded by me, but there I was addding, deleting, updating records like a pro. Relations here, bound objects there, The Wizard was my friend. Then I asked him for what I thought was a very simple request: Can I have my @@IDENTITY please? The Wizard grew angry, and hurled error after error at me, null objects were exploding all around me, I tried to reason with him, offering CType's and Try/Catch's but to no avail. I had to seek help for this monster - one after another telling me to SELECT @@IDENTITY, SELECT SCOPE_IDENTITY, but I pleaded with them "I have for the love of god" everywhere possible (if you find yourself wanting to reply now - my answer is YES I tried that), but he would not reviel my identity... Until I took matters into my own hands.... Wizard Says: Private Sub MarketLogFilesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MarketLogFilesBindingNavigatorSaveItem.Click Me.Validate() Me.MarketLogFilesBindingSource.EndEdit() Me.MarketLogFilesTableAdapter.Update(Me.EveMarketDBDataSet.MarketLogFiles) End Sub I say: Private Sub MarketLogFilesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MarketLogFilesBindingNavigatorSaveItem.Click Me.Validate() Me.MarketLogFilesBindingSource.EndEdit() Dim command As New SqlClient.SqlCommand("SELECT @@IDENTITY", Me.MarketLogFilesTableAdapter.Connection) Me.MarketLogFilesTableAdapter.Connection.Open() Me.MarketLogFilesTableAdapter.Update(Me.EveMarketDBDataSet.MarketLogFiles) Dim i As Integer = CType(command.ExecuteScalar(), Integer) Me.MarketLogFilesTableAdapter.Connection.Close() End Sub Though this code may be totally wrong, it works. I spent many hours trying to find a way that didn't involve me writing my own Update commands, or more to the point, I was sure that such a simple task as retriving the last PK of record had to be in the wizards head somewhere. I am sure there is a simple way to do it, that I have so clearly missed, but for those of you who rely on the wizard, the above at least got me one step closer. It seem's in the auto-gen code, the connection is closed after the update command IF the connection was closed BEFORE the update command was issued. IF ANYONE CAN FIND ME DOCUMENTATION ON THAT FACT, I WILL GIVE YOU ONE MILLION.... Thanks guys, keep up the great work, I love these forums - inspiration and knowledge -
View Replies !
Copying Personalization Settings To Another Field
Im trying to store default page layouts for the different Roles created in my membership provider. Depending on the job code of my employee, they get different default formats of the website. What I thought at first would be easy, is proving to be a bit of a pain. I guess what I need to know are two things: 1) How can I create an image field in my aspnet_Roles table, and set the length = 6000 to match the 'PageSettings' field in the aspnet_PersonalizationPerUser table? By default the image is set to 16, and wont allow me to change it. (this might not make a difference, Im not sure) 2) How do I copy this binary data back and forth from one table to another? I realize its actually a pointer, but why cant I copy the pointer to another field? I appreciate any help available.
View Replies !
Import DTS Wizard
Hi... Anybody out there who can help me on how to view the whole import process (DTS package created using Import DTS wizard) in SQL codes? Is there a possibility to view the process using Query Analyzer? please help. MyPast
View Replies !
Import Wizard
Hi, I'm trying to import data from Microsoft Access. I already have all the tables in SQL Server and also the relationships, but I can't seem to import any data without deleting the relationships. Is there a way to do this without deleting them?
View Replies !
How To Change Integer Field To Identity Field.
I have an integer column(not null) in a table. how can i change that to identity column through query analyzer, through a script, b'cos i have to apply this to number of databases. I tried this, but got an error: ALTER TABLE table_name alter column company_id int IDENTITY ERROR: Incorrect syntax near the keyword 'IDENTITY'. any help is welcome.
View Replies !
DTS Import Wizard Causes Crash
Importing DBase files I get to the screen titled "Select Source Tableand Views" And none appear in the list. (I can get the list displayedabout once in ten tries) From that screen anything causes a crash ofthe wizard and Enterprise Manager. This happens on my deveopmentinstallation of Enterprise Manager Version 8.0.Any Ideas/ PleaseJohn Cooper
View Replies !
DTS Import Wizard Fails
I am trying to copy updated tables from a DB on a remote SQL server tomy pc using import wizard. I choose "copy objects and data between SQLServer databases". On the screen "Select objects to copy", all of theoptions are unchecked/blank including the script location. Clicking"select objects" causes the wizard (and enterprise manager) to crash.An error message refers to mmc.exe. Any ideas?
View Replies !
DTS-import/export Wizard
Can anyone tell me how I can use the DTS Wizard to import over 100 excel files from my hard drive, over to my server called(MARKETING_DB)and loop back through to grab the next excel file until all the files have been imported? Thanks, Mark
View Replies !
DTS Import/Export Wizard
Hi guys I'm trying to export a table from one ms sql database to another ms sql database. Unfortunately the following error occurs: Error at Destination for Row number 2400. Errors encountered so far in this task: 1 Can somebody help me to solve this problem? Would be very nice!! Best regards, Lorenz
View Replies !
Import Wizard, Where Saved?
I created my first package using the import wizard and saved it to the server. And now I can't find it. In SQL Server Mgt Studio, I've browsed through every imaginable folder in the target database, in MSDB database, and in the global folders. I read something that suggested there should be an Integration Services folder, but there isn't. Once I locate the package, I understand that it's not editable. Can it be transferred to BI Studio for editing? TIA.
View Replies !
Can I Get The Source To The Import Wizard?
Or is someone reading this a Wizard of Wizards. I need a 2nd flavor of import wizard. The one that I would clone out of the existing one would: Use ASCII not UnicodeUse the copy column control not the Data ConversionThe size of all fields will be 255 not 50.The data type will be varchar.In my cloned wizard, the goal will be to just get the data loaded. All other goals will be addressed by the user after the data is in the database. Do I hear a volunteer? Thanks, IanO
View Replies !
Import Export Wizard
Hello, I am having alot of problems with the import export wizard. I am trying to copy over around 315 tables to a new database. When the tables are copied over to the new database they are copied without any of the original constraints. Does anyone know why this could be happening? What I am trying to accomplish is a simple database transfer with the exception of about 10 to 15 tables from the original database. I do not understand why this is such a problem. I did not create a package, as this is going to be a one time shot. Any help would be appreciated. Thanks, David
View Replies !
Import Wizard And Null Value
I am trying to import some csv files into a table. Some of the values are left blank and are supposed to be interpret as null. However, when I try to use the import wizard (via SQL Server Management Studio), the import process errors out. The fields on the table allows null values. How do I specify the import wizard to interpret blank fields in flat files to be null? It was mentioned somewhere that there might be a checkbox indicating that I can preserve the null values, but I can't find the option anywhere.
View Replies !
Import Data Using Wizard
hi! i always use the import data wizard in SQL 2005 to import data from excel to an existing table in my database. but lately i am getting errors at the validating part... it seems to say that the table is already existing thats why i cant import into it. But heres the weird thing.. if i use a previous excel (the one that works before) it will be succesful! Am I doing something wrong here? Any comments / info / answer will be greatly appreciated! Thanks!
View Replies !
Import Export Wizard
In sql2k i can select bunch of tables in EM and export to a different db also I can schedule this activity. How to do the same in SQL2K5?
View Replies !
Import Export Wizard
Import Export Wizard creates a new SSIS package to the file system. With the help of that wizard can I add the data flow task to an existing package. Thanks Sai
View Replies !
DTS Import/Export Wizard
What happens if my DTS fails half way through? (For example due to a network error). I guess what I'm really asking is: does DTS Wizard operate within a transaction which upon failure is rolled back, and on success is committed? Thanks, Richard.
View Replies !
DTS Import/Export Wizard
Hi, I'm using the DTS Import/Export Wizard for the first time. Does anyone have a "canned" script I can run instead of going through the step by step wizard process. I have a number of tables I have to copy from a prod to test environment. Thanks, Jeff
View Replies !
Last GASP On &&"Insert Row In Table With Identity Field, And Get New Identity Back &&" ?
While I have learned a lot from this thread I am still basically confused about the issues involved. .I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple. To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package. 1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful. 2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key! 3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed? TIA, Barkingdog
View Replies !
SSIS Import/Export Wizard
I have Sql Server 2005 Developer Edition and Sql Server Management Studio.I'm trying to import data but there are no options available under "Projects" in SSMS.Do I need to download an update or anything to use this functionality? Thanks
View Replies !
DTS Import Wizard Not Copying Data.
I am trying to copy my database from one Sql Server to Another. I am using the DTS Import / Export Wizard, and selecting Copy Objects and Data. I can't seem to get it copy the data. It copies everything else fine ( table schemas, stored procedures, etc. ). If I try to copy All Data, it gives an error stating that a UDF already exists, so I Select just the tables to transfer. It says that it has completed successfully, but no data shows in the destination tables. Any ideas as to why this is happening?
View Replies !
DTS Import Wizard Does Not Fire Triggers
Hi to all Gurus,I am working with two tables, where in if a record is inserted in onetable an insert trigger fires and inserts relevant information for thatrecord in the second table. This works well within my applications.But it failed (or the trigger never fired)when we used the DTS Importwizard to insert new data into the table from an excel file.How can I overcome this situation? Any help is greatly appreciated.--Part-time BE
View Replies !
DTS Import/Export Wizard In MSDE
I used to have the DTS Import/Export Wizard in my Office-installed MSDE, along with a few other administration tools. When I upgraded to SP3a, these went away. I removed MSDE completely and did a fresh install, but still no Admin tools. Are these just gone? I can't find them as available on the MSDN site, either without an MSDN license. I had upgraded in order to use MSDE with Web Matrix for ASP.Net development. Thanks for your help
View Replies !
MS SQL Server Import/export Wizard
Hi everyone, Plese help me out. I import one database from one server to another. This process able to copy all the tables and functions but it copied only few stored procedures. so tell me how to copy or import remaining stored procedures?
View Replies !
|
TRACKER
