I have the following Excel code that I need to convert to SQL. I have looked (a little) at using cursors to do the looping, but I'm not sure how to define the cursors
----------------------
Do While Worksheets("DATA").Cells(sCellRow, 1).Value <> ""
sPartNo = Worksheets("DATA").Cells(sCellRow, 1)
Do While Worksheets("DATA").Cells(sCellRow, 1) = sPartNo
sPartNo = Worksheets("DATA").Cells(sCellRow, 1)
'
' SO #
'
Select Case dCounter
Case 1 To 9
sNextSO = "E" & "0000" & dCounter
Case 10 To 99
sNextSO = "E" & "000" & dCounter
Case 100 To 999
sNextSO = "E" & "00" & dCounter
Case 1000 To 9999
sNextSO = "E" & dCounter
Case 10000 To 99999
sNextSO = dCounter
End Select
'
' PO Line Item
'
Select Case Worksheets("DATA").Cells(dCellRow, 25).Value
hi all, i am trying to import data from an excel sheet into sql server. here i am getting an error which states the "EXTERNAL DATABASE IS NOT IN THE EXPECTED FORMAT" what is the expected format? can anyone help me out with this.. thanks.. bel.
I'm having an issue with data from an OLE DB source being converted from numeric [DT_R8] to text when I export it to Excel. I had the column in the spreadsheet formatted as Numeric, but when I set it up as the data destination, it is showing in SSIS as [DT_WSTR].
The SSIS job runs OK, it just converts my numeric data to text in the spreadsheet. I'm surprised that the job actually runs, since I have to do explicit data conversions for my OLE DB string data in order to convert it to unicode. From what I have read elsewhere, the decision was made in SSIS not to use implicit data conversion, but to require explicit conversion. Yet this export is converting my numeric data to text. I would appreciate any information on how to set up the Excel Destination to properly handle numeric information.
I have a report that the user can print right from the RS site on three pages (each page one page wide). However, when I have the server email the report as an attachment in Excel; Excel tries to print it on four pages with one of the pages spilling over into the fourth page.
How can I get Excel to format the print version just as I have on the RS site?
I am painfully learning SSIS and just when I think I have a better understanding of how things work and go down the path that I should take, I hit a wall. Frankly, discouraging and demoralizing.
I am performing a Select of rows that are then passed on to a ForEachLoop with an ADO Enumerator with a FullResultSet. Variable mappings for the columns used are:
A ScriptTask in the foreach loop allows me to confirm that I am processing rows.
A DataFlow in the ForEachLoop has in it a DERIVED COLUMN item that allows me to assign the four variable fields above to their respective DERIVED COLUMN.
An attempt to use a Flat File Destination to process the derived columns is being made. As I monitor the execution of the package I notice the following message.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (382)" wrote 0 rows.
Can anyone PLEASE tell me why is it that I am able to see the data being processed through the script task? And yet not see any rows being written to the Flat file. Why?
Any information you may provide would be immensely appreciated.
I’ve created a script which will do the following.
Update fields in a database which contain instances of an order number. An order number is defined as a 10 digit numeric sting which beings with 998. The first 3 digits of the order number need to change from 998 to 999. There are two types of fields to update: document number fields which may contain 1 instance of the order number and free text fields which may contain multiple instances of an order number.
I created a function which accepts the text to be updated, the text to find and the text to replace it with. The function then loops through the sting for instances of 998. For each instance it finds it checks to ensure that it is at the start of a 10 digit string which contains only numeric numbers – if this is the case then it will update the 998 to be 999. If the field is free text then it will continue to loop through the string (it will skip forward 10 digits for every order number found) until the end. If the field is not free text then it will exit the script after the first instance found which matches the above criteria, if any.
Need achieving this via set processing and not having to loop through every line. I’ve included the function below and some test data.
--Create the function we will call in order to do the replace if object_id(N'OrderReplace',N'FN') is not null drop function dbo.OrderReplace; go create function dbo.OrderReplace ( @Textnvarchar(4000),
Hi, I need to import an SQL string from MS Excel 2003 to SQL SERVER 2000. The string I need to import is composed by 5 different several blocks and looks like:
The detail of the SQL string is at: http://forums.microsoft.com/msdn/showpost.aspx?postid=2093921&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1
I am trying to implement OJ's suggestion: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2117223&SiteID=1 to use multi - batch processing to import the string to SQL SERVER, something like:
Code Snippet Dim SqlCnt, cmd1, cmd2, cmd3 'set the properties and open a connection
cmd1="use my_db" cmd2="create table mytb" cmd3="insert into mytb"
I have installed the excel DM addin and am trying to work through the tutorials -
When I run the 'Analyze Key Influencers' tool against the sample data through a remote AS server I get: The task was not able to detect any key influencers for the 'Purchased Bike' column. The values of 'Purchased Bike' seem unrelated to values of other columns.
however when I run it against a local AS server I get the expected results.
I can see no differences in settings or setup between the AS instances I am trying to use - perhaps a permissions issue? Thank you
I'm attempting to use the Foreach loop container to loop through the excel files located on a shared network folder. I've set up the Excel file connection manager to include the user variable generated from the container, but I get the below error messages when applying that variable to the connection string in the data flow. I've tried everything but I can't seem to get SSIS to recognize the path of the Excel files. I've tried copying the files to my PC, I tried running the package on the server, etc. The connection works fine if I set it up to point to any of the excel files in the network directory, but not with the Foreach loop connection name.
Any help or suggestions would be greatly appreciated! I've looked everywhere and tried everything but to no avail...
Thanks,
Kevin
TITLE: Microsoft Visual Studio ------------------------------
Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
I'm having a problem getting the for loop container to process all excel files in a folder. I set the collection folder to where my .xls files are, and i set a variable in the for loop container to the FileName. I then changed my source connection and added expressions for
I have a package that loops over ~60 Excel files in a directory. Each file has three named ranges in it, which I import into different tables. Sometimes the package runs without a hitch, sometimes it chokes. But it is intermittent.
If I pull the control flow components out of the foreach loop and point the Excel connection manager to the specific Excel file that has caused the package to choke, I get a message in the dataflow component pointing to the named range that "the metadata of the following output columns does not match the metadata of the external columns......Do you want to replace the metadata of the output columns with the metadata of the external columns?" When I choose 'yes', then the file will be loaded. then I can put the control flow components back into the foreach loop and the file will run again, successfully, along with some more, until it chokes again....
So, first of all, does anyone have any insight into this? Sometimes, somedays, these files will load with no problems. These exact files; I am having to reload constantly... Other times, like today, it is a battle.
Otherwise, is there a way to get Integration Svcs to handle the metadata issue on the fly???
Any ideas, resources, references, war stories, or good clean jokes would be appreciated, Kathryn
Hi I am trying to load a bunch of excel files into a table and running into tables
I create a seq container--> add a foreach loop control task configure a variable thats ok as I am displaying the files names through script
I am stuck as how to configure the next data flow task to load into a destination I create a source excel and then map the expression to the variable but the destination SQL connection is not able to see this one
What I'm trying to achieve is a SSIS package that will pick up 1 or more excel files, process the data in them via the conditional splitter, pushing the good data into a table, and all other rows into an error table.
I'm having some issues using the ForEach container to process multiple excel spreadsheets into tables. The excel import into the tables is more or less working (imports data for good cases, but uses a null if the Excel Source when it gets an unexpected value - but that's a seperate problem).
I found something related to this when searching, but it related to CTPs (June and September) and trying to reuse the connection strings they built up there (using my own variable names, naturally) causes a 'Property Value failure':
The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
The excel importer works fine as a stand-alone component. Trying to use the process defined in 'Profession SQL Server Integration Services' pp140, I tried to use an expression to assign the variable value to the connection string. I get a validation error:
SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Excel Importer' has been hit
SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Foreach Loop Container' has been hit
SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit
Information: 0x4004300A at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Import TPNB Ranking Excel spreadsheets, ProductSalesRank Table [278]: Truncation may occur due to inserting data from data flow column "Rank" with a length of 1000 to database column "SalesRank" with a length of 50.
Error: 0xC0202009 at Excel Importer, Connection manager "Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unrecognized database format 'D:TestingTestRanking.xls'.".
Error: 0xC020801C at Import TPNB Ranking Excel spreadsheets, Excel Source [1]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Error: 0xC0047017 at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Import TPNB Ranking Excel spreadsheets: There were errors during task validation.
SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit
Warning: 0x80019002 at Foreach Loop Container: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit
SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit
SSIS breakpoint 'Break when the container receives the OnWarning event' at executable 'Excel Importer' has been hit
Warning: 0x80019002 at Excel Importer: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS breakpoint 'Break when the container receives the OnPostExecute event' at executable 'Excel Importer' has been hit
The program '[2460] Excel Importer.dtsx: DTS' has exited with code 0 (0x0).--------------------------------------------------------------------------------------------
well i am trying to follow the example of msdn help on how to loop through excel files and it doesn't work. the variables have the project scope. what can i do?
Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.
I'm trying to write a package that imports a bunch of Excel files into a sql server table. My import routine works fine when I do not use a foreach loop container. I then add a Foreach Loop Container with a Foreach File Enumerator and create a variable to store all of the excel file names to use with my excel connection manager in the connection string property.
When I run the debugger I get the following error:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Import New Data [My Data (Excel) [4234]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Error at Import New Data [DTS.Pipeline]: component "My Data (Excel)" (4234) failed validation and returned error code 0xC020801C.
Error at Import New Data [DTS.Pipeline]: One or more component failed validation.
Error at Import New Data: There were errors during task validation.
Error at ImportMyData [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80040E4D.
I've read the varous posts on this forum and others with people having this problem and nothing that was recommended to them works for me. I've also read the Msdn article 'How to:Loop through Excel files and Tables' and followed the directions there without success. I've tried the following with no success:
I've tried to create first a hard coded connection string in the excel conn. manager which works but as soon as I set the experessions: connection string property = my file enumerator variable it breaks.
Turned the Delay Validation to True for the package as well as the task
Set the Connection string equal to not just the variable coming from the file enumerator but to the full connection string : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::excel_filename] + ";Extended Properties="Excel 8.0;HDR=YES";"
I've also tried to set the excel filepath property instead of the connection string and this didn't work either.
I'm getting really frustrated. Any advice on why something so simple doesn't work for me would be appreciated.
I have to import a number of excel spreadsheets. I'm using the classic Foreach Loop inside another Foreach loop approach. The outside loop (Foreach File Enumerator) cycles through the Excel files, while the inside loop (Foreach ADO.NET Schema Rowset Enumerator - ExcelSchema - Tables) to cycle through the individual Excel sheets in each file.
Nothing special there; however, for some reason these excel files have some "phantom" tabs that should not be imported. I call them phanton because they show up as an importable tab in a SSSIS import wizard but actually are not listed in the excel file structure (no, they are not hidden tabs, I checked).
My idea is to use a constraint to NOT import those phanton tabs. The name convention should allow me to do that because the normal tabs have the name 'AAAAAAyyyymmdd$' and the phantom tabs show up as 'AAAAAAyyyymmdd$'_xlnm#_FilterDatabase (the line below was captured from the Local Variable window and show one of the phantom tabs name).
I tried using Len (@[User::WorksheetName]) == 17, which corresponds to the length of the normal tabs name ('AAAAAAyyyymmdd$'). However, it does not work. For some reason the portion of the phantom tab name after the ending single quote (_xlnm#_FilterDatabase)Â appears to be ignored.
I tested with a number of different expressions, including reversing the variable, to no avail. It seems that internally just the standard name between quotes is what the constraint sees.
How can I use a foreach loop container with an Excel connection manager. It is virtually impossible to configure the ConectionString property through expressions builder. Everything works fine with a Flat File connection manager but when it comes to Excel, I get the following error message: Error 1 Validation error. Extract Four: Extract Four [1]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. Tarification.dtsx 0 0
Please ! Heeeelp ! tel me how I can loop through Excel files !
I'd need an help because I'm stucked!! I have to import an Excel file into my DB. The Excel file is made by 2 worksheets but I need only one and inside this worksheet I have to loop through the columns and for each column I define a Data Flow that trasform the data as necessary and then insert into the table.
I started with a "Foreach ADO.NET Schema Rowset Enumerator" with connection=excel file and the schema was set to "Columns" but the loop go also through the worksheet that I don't need..
after 4 hours of tries I'm lost... Someone could give me an advice? ThankX Marina B.
I have used for-each loop container for loading excel sheet contains multiple sheets with same structure. It is loading data into SQL table even there is no data in sheets.
Purpose: Need to import excel source data into SQL Server 2005 tables. Excel source data comes in nulitple excel files with the same structure but different data. I would appreciate someone taking a look at the following information and notifying me of what I am doing incorrectly.
I Inserted a foreach loop container, a data flow task located inside the foreach loop contaiiner, an excel and SQL Server 2005 connections.
After trying multiple times I went the following URL and followed step by step direction on how to connect excel workbooks dynamically: http://msdn2.microsoft.com/en-us/library/ms345182.aspx . I also used http://www.sqlstrings.com/ as a reference when creating the connection string.
Creating a Foreach Loop Container:
1. Opened foreach loop container 2.Set the Enumerator to 'Foreach File Enumerator" and configured the enumerator by setting the directory location and file base name to E:ClientsDep CommBEABEA_Test_Source and *PersonnelExpense*.xls respectively. 3. Clicked Variable Mapping; created two variables called, "ExcelFile", and "ExtProperties" and closed out of the foreach loop container.
I. Created Excel Connection:
Created excel connection called, €œDynamic Excel Connection Manager,€? that initially pointed to one of the excel workbooks. Went to the connection properties by right clicking the connection manager. Expanded Expressions and clicked the ellipsis button to bring up property expressions Chose Connection String in the Property. Clicked the Expression Ellipsis button. Put the following inside the Expression multi line text box: A. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties="" + @[User::ExtProperties] + """
Clicked the Evaluate Expression button to get the following:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="" Clicked Ok button Inserted a Data flow task inside the foreach loop container.
II. Configured Tasks that is associated with Dynamic Excel Connection Manager or Package:
Set the Foreach loop container Delay Validation to true. Set the Data Flow Task Container Delay Validation to true. Set the Dynamic Excel Connection Manager Delay Validation to true. Set the SQL Server Connection Manager Delay Validation to true. Set the Package Delay Validation to true. Package Locale ID set to English
Ran the package after connecting the excel source data flow to the OLEDB destination and have inserted part of the error in this post. Please see below.
Error: 0xC0202009 at Package, Connection manager "Dynamic Excel Connection Manager": 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 find installable ISAM.".
I modified the connection string after receiving the error by removing the extended properties. The following is the modified connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile]
I repeated step I.6 above and received the following expression: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
I ran the package and received the following error in part: OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unrecognized database format 'E:ClientsDep CommBEABEA_Test_SourcePersonnelExpense_OCCs_051007.xls'."
I did not find anything helpful when I searched for the above errors and would very much appreciate anyone€™s assistance on this issue as this issue needs to be taken care of ASAP.
Does anyone have any ideas as to why I received this error and what can I do to resolve this issue?
Your assistance in this matter is truly appreicated! Thanks!! Lee
I am getting the following error when trying to load multiple excel files using for each loop container in SSIS, I tried to put the quotes in several different ways but still can't get rid of this error. I was able to successfully load single excel file, but when I use the for each loop container that's when I am having problems. Any help is greatly appreciated. Thx.
Error at Package1 [Connection manager "SourceConnectionExcel"]: The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes. This error occurs when values in the connection string contain unquoted semicolons, such as the InitialCatalog property.
Error at Package1: The result of the expression ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Folder] + @[User::file] + ";Extended Properties="Excel 8.0;HDR=NO";"
" on property "ExcelFilePath" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.
How can i create an excel dynamically in the foreach loop container.
Here a query Microsoft Access Query That I would like to convert to SQL. What replace the Word "Last" in SQL SELECT Contacts.FirstName, Last(Contacts.LastName) AS LastofLastNameFROM ContactsGROUP BY Contacts.FirstName;
I am using SQL Server 2005. I am trying to import data from CSV files into an SQL Server table using the Import wizard. The text qualifier is double quotes ("), column delimeter is a comma (,), first row has column names. One of the field name is "id", which is a GUID, whose datatype in SQL Server is uniqueidentifier. It looks like this in the file: ..."data","data","dbf7edf8-0ca8-4e53-91e3-5901cdc1819a","data"... As you can see, there are no enclosing curly braces for the guid value. The DTS chokes on this and throws this error: The value could not be converted because of a potential loss of data If I add curly braces like this {dbf7edf8-0ca8-4e53-91e3-5901cdc1819a}, it imports with no problem. Is there way to import this type of data, because there is no way I can edit these files, and I would prefer not changing the datatype of the id field? Or is this a limitation of SQL Server? Thanks,Bullpit
I converted my database to 2007 version and when I tried to execute VBA Module which adds records from one table to another it showed me a mistake (Type mismatch) Here is the code (it worked on 2003 version). Can anybode help me to fix the error?
Function AddBulletin() Dim st As String Dim i As Integer Dim db As Database Dim Table1 As Recordset Dim Table2 As Recordset DoCmd.OpenTable "Today", acNormal, acEdit DoCmd.ApplyFilter "", "[Today]![F3] Is Null" DoCmd.RunCommand acCmdSelectAllRecords DoCmd.RunCommand acCmdDeleteRecord DoCmd.Close acTable, "Today" Set db = DBEngine.Workspaces(0).Databases(0) Set Table1 = db.OpenRecordset("Today", dbOpenDynaset) Set Table2 = db.OpenRecordset("BULLETINS", dbOpenDynaset)
The mistake appears on the last two rows. It's probably easy but I cannot find it.
During import from CSV i am getting following error "The value could not be converted because of a potential loss of data". My CSV file contains a column "years" and i have selected datatime in the import wizar. Can I scape from this error and import all my data.
I've searched the threads and didn't see anything which seemed to fit this specific issue....
I have a Data Flow task which reads from an OLE DB Source (SQL Server 2005), uses a Data Conversion transformation to convert some field values, and finally outputs the result to distinct tabs of an Excel workbook. The task is failing with the following error:
There was an error with input column "oBBCompanyName" (2162) on input "Excel Destination Input" (57). The column status returned was: "The value could not be converted because of a potential loss of data.".
Using the Advanced Editor for the Excel Destination component, I examined the datatype of oBBCompanyName (ID = 2162) in the Input Columns list of the "Excel Destination Input" (identified with ID = 57). The Data Type is defined as DT_WSTR with Length = 255. The ExternalMetaDataColumnID = 203.
Also in the Advanced Editor for the Excel Destination, I examined the datatype of BBCompanyName (ID = 203) in the Extranl Columns list of the Excel Destination Input. The Data Type is defined as "Unicode String [DT_WSTR] with Length = 255.
What could I be overlooking that might be the root cause of this issue? The same error is occurring for different Excel Destination tasks in the data flow.
