Dear Friends,
I currently have my excel source dynamic for the connectionstring. Using a global variable with the filename and using the expressions of this control to dynamically have the connecttionstring. My problem is that i need tto read always the first workseet and the name changes frequently and generates an erro in SSIS.
how can I set the excel source to goes only for the first sheet independently of the worksheet name?!
Regards!!
Hi,
I am creating an SSIS package witha a Dataflow task, which reads from an Excel source and then uses script component to dumpt the data to multiple tables in Sql Server database
I need to some how make my Excel source dynamic, that is my excel template which i would be using to map the excel columns to script component's input columns would be dynamic..
In other words, I should be able to define the Excel Source, Column Mapping Information, Precedence constraint to the Script component dynamically
Please suggest how could i accomplish this
Regards,
Kalyan
Hi,
I've seen a number of posts similar to this but i still cannot figure out what i need to do to get it working. So here goes with a couple of newbie questions.
Question 1:
Once created how do i go about executing a SSIS package. I want to be able to call it from a C# application from which i pass in a couple of parameters?
Question 2:
How do i go about setting the file path of my Excel source to a dynamic value passed at runtime. I want to be able to loop through a number of Excel files and do some processing on them. I've set up a variable (which i think i need to do) after that i get stuck however. Some other posts suggest configuration packages but i cannot get my head around how they work?
Any help on this matter would be gratefully recieved.
Thanks in advance,
Grant
Hi all,
I am able to set dynamic source for the text file(flat file) but i want to set the connection string (file name) to excel source dynamicaly. I have tryied lots of time by taking a variable in foreachloop container . Variable is itself able to pick the file name dynamicaly but whern i am tying to set connectionstring to excel source it gives error.
Steps that i have done: -
1) Drag foreachloop container
2) set directory,FileNameRetrieval,FileSpec
3) Made VariableMapping
4) Now drag a dataflow task in the foreachloop container
5) select excel source
6) When i am selecting varaible as connectin string from properties of excel connectin manager, i am getting this error : -
TITLE: Microsoft Visual Studio
------------------------------
Error at Package3 [Connection manager "Excel Connection Manager 2"]: An OLE DB error has occurred. Error code: 0x80040E4D.
Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager 2" failed with error code 0xC0202009.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Please help me, whats the problem? can i set connectionstring via variable to excel source
Hello,
Kindly give me the solution ASAP how to do Dyanmic Connection in ExcelConnection manager.
Thanks
Thiru
Hi SSIsians,
I have a package with Excel Destination with dynamic connection.
I did ExcelFilePath = [@user::VarSourceFolder]+[@user::VarSourceFileName]
then i changed the Delayvalidation = True.
When i try to run the package in BIDS it gives the error.
ERROR:
[Excel Source [30501]] Error: An OLE DB error has occurred. Error code: 0x80040E37.
[Excel Source [30501]] Error: Opening a rowset for "DailySheet" failed. Check that the object exists in the database.
It saying there is no sheet in the name of "DailySheet" but when i removed the expression in connection manager property it is working fine.
Please let me know what is the problem OR how to configure the dynamic connection in ExcelSource.
Thanks
Thiru
SE - SSIS
Chennai
While importing data from Excel source , some column is getting null value even though excel column has value.To Resolve the issue we tried with
HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineEnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
• xls
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
the connection string of the excel
UPPER(REVERSE(SUBSTRING( REVERSE(@[User::VarInputExcelFile]), 1, 5) ) ) == ".XLSX" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::VarInputExcelFile] + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";":"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::VarInputExcelFile] + ";Extended Properties="EXCEL 8.0;HDR=Yes;IMEX=1";"
by doing the above setting also , the column is coming as null from excel source even though there is data in excel.
I have CSV file as source for SSIS package every time the filename will be changing like trd_1990M1_1990M12.csv,trd_1991M1_1991M12.csv , trd_1992M1_1992M12.csv etc.,
so it will vary as per user selection . i need to run the same SSIS package to execute the different file name with the same structure.
Please let me know the solution for that how to pass the file name dynamically to SSIS package.
I have created an SSIS package that is designed to move data from SQL Server 2000 to an Access db. I have set the package up to accept four parameters. They are:
the name of the SQL Server Database, which is used in an expression to provide the source connection manager connection string;
the full path to the Access db destination database which is used like #1 above.
the SELECT statement used in an OLE DB Source object to get the data from the source table
the table name which is used by an OLE DB Destination object.
I know that the source and destination tables have exactly the same structure and do not require a transformation.
In order to change variables 3 and 4 from above and have it work, I go through the following steps:
I change the variables to appropriate values.
Go to the Advanced Editor for the OLE DB Source object and click on "Refresh". This produces an error in the OLE DB Destination object that is something like "Validation error. Data Flow Task: DTS.Pipeline: input column "strRECTYPESUFFIX" (301) has lineage ID 17 that was not previously used in the Data Flow task.
Go to the Advanced Editor for the OLE DB Destination which brings up the "Restore Invalid Column Reference Editor". I mark all the columns that show up with the option to <Delete invalid column reference> and click OK
I then reopen the Advanced Editor for the OLE DB Destination, go to the 'Column Mappings' tab click 'Refresh', then in the upper pane where the input and output columns list appear I right click and choose "Map Items by Matching Names"
At this point I no longer have the error and the package will execute without any problems.
I am doing this so that I can load the SSIS package in VB.NET (2.0) so that I can then set these variables programmatically and then execute the package. The problem is that this actually performs steps 1 and 4 above. 2 and 3 are left out and the package fails miserably.
I have found some information that would be helpful if I could get my hands on the appropriate object. I realize that the data flow component would return VS_NEEDSNEWMETADATA from the Validate method, and that could be repaired with the ReinitializeMetaData method of the data flow component ( I assume that is the object to be using in this case). But I do not know how to grab the "Data Flow Component" as an object based upon the "Package" object I have loaded so that I can check if it is valid and manipulate it if necessary.
Thank you in advance for your help.
Dear Friends,
I need to import data from several excel files. How can I configure excel source object to dinamically import each file?
The name of the file will be in a parameter of ssis package and this name change frequently, and ach time the filename change I dont want to change the configuration on the excel source?
What you sugest?
Shoul I use a script component as source?!
Regards!
I have a SSIS Package that exports data from Sql Server to an Excel file.
I need help figuring out how to have the file name be "Report_02132007.xls". Basically I want to append the date to the file name.
Any ideas?
Hi All, i've been reading this article http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
in regards to creating an excel spreadsheet dynamically in SQL Server 2005 SSIS. However, i'm constantly getting an where the tab is created but not being populated. Can somebody post up a clearer example?
The problem I'm trying to solve is to automate the export of a query onto a new dynamic spreadsheet each time I run this SSIS package.
Any help would be greatly appreciated.
Thank you.
Hi,
I am using Excel datasource and Excel destination in a simple SSIS package which i created. I have got two queries regarding this
How does excel datasouce/destination accept .xlsx files, since i observed that they accept only .xsl file
Does excel destination ascept .xlsm files, because i have some macros enabled in destination
Thanks in advance for advice
Warm Regards,
gchanduu.
Hi,
I am migrating one of my DTS package to SSIS.
My task is to read the filename from a database table and transfer the flat file data in to a table.
In SSIS,I am able to fetch the file name using a Data Reader Source; but how to pass this fileName parameter to Flat File Source ?
In DTS I have used ActiveX script to pass filename variable as flatfilecon.Source.
Any help ?
Thanks,
Ravi
Hi all,
I did a few searches but did not find this specific scenario. Can anyone state with confidence whether this is possible (and if so, how)? Scenario:
-One table with a couple million rows (one column indicates which Country the record belongs to)
-Need to create an Excel 2007 file dynamically (for each Country) using SSIS 2005. Filename should include the Country Name (Sweden_Affliliated.xlsx). I have a table that contains a distinct list of all the countries. Each worksheet will have the same structure / schema across all files.
What seems to be working:
**I understand how to use an Execute SQL task to get the list of Country Names and bind to an object variable.
**I understand how to set variable mappings for a String variable to contain the "current Country" in a ForEach Loop.
**I understand how to set the OLE DB Data Flow Source to use a SQL command from a String variable that has the CountryName dynamically embedded within it.
**I understand I need to convert my varchar to Unicode using a Data Conversion task in my scenario.
**I understand that in order to write to an Excel 2007 file I need to use an OLE DB Destination with an Extended Property value of "Excel 12.0" and the ServerName property should contain a path to the file with no quotation marks.
Problems I have:
**OLE DB Destination: How do I set up the mappings when the file does not exist yet?
What I want to avoid, is having to create a template source XLSX file and using a File Copy task (I have gone this route before, but it would be best if I did not require a template). Is there a way to configure the SSIS package without using a File Copy Task? Creating the Excel file on the fly?
Thanks for any assistance in advance,
Brian
An Excel Source Data Flow object (which used to work fine) sudenly started display the following error box:
TITLE: Microsoft Visual Studio
------------------------------
Error at Create BusStop Table [DTS.Pipeline]: The index is not valid.
ADDITIONAL INFORMATION:Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap)
What could the cause be?
What is the meaning of: HRESULT: 0xC0048004 ? How could this info be used?
Thanks and best regards,
Dan
I have a problem with reading data from an Excel file in SSIS. I'm trying to read a column that mostly consists of decimal values, but there are couple places where column entry is 2 numbers separated by a slash (e.g. "100/6.0"). SSIS tries to be smart and identifies the column data type as decimal and when it reads the cell with the slash in it, it reads as NULL. I tried to make my excel source reader component to read that cell as a string, but it gives me an error. If anybody has come across something like this, I would highly appreciate some help
-Erlan
Hello kindly help me in this issue , i have an ssis package with excel data source , when i run it outside the sql
agent job it run normaly when i create a job to execute it from the sql server agent it fail.
can any body help me.
also i had same problem with packages that call the execution of other packages.
I have an excel file which has dynamic columns
i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.
My problem is to Unpivot the data
Date 8/2/2013 8/9/2013 8/16/2013
Stock 1,561 1,661 1,761
i.e. the abobe table should become as
Date Stock
8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761
How can I unpivot the dynamic columns given that the columns will keep on increasing every week.
I want to export the data into multiple sheets with same template, all the worksheets have to split dynamically with specific Sheet Name and template also copied to all other sheets
For Example:
Sheet Name: Guru
Name Age
Guru        24
Sheet Name: Johnson
Name Age
Johnson     32
it goes on......
(Applies to SQLServer 2005 SP1)
We have found that using the SSIS "Import and Export Wizard" using the "Microsoft Excel" data source that there appears to be a maximum column length of 255 characters for any row.
Even when defining the destination table columns as nvarchar(4000), the wizard fails with the errors shown below.
We have found no workaround except manually changing the imput data. There doesn't appear to be any "Advanced" options for the Excel importer as there are for the flat-text importer. So, no question here, just posting the bug so that *next* time someone searches the web for an answer, this post comes up
MessagesError 0xc020901c: Data Flow Task: There was an error with output column "English String" (18) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task: The "output column "English String" (18)" failed because truncation occurred, and the truncation row disposition on "output column "English String" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038. (SQL Server Import and Export Wizard) Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039. (SQL Server Import and Export Wizard)
edit: After searching further this is documented under "Excel Source"
in BOL which provides a registry-based workaround. I guess the issue
is that the wizard considers truncation to be a 'fail' case and
there's no easy way to override this behaviour, specify the column
types nor determine which line is in error)
Truncated text. When the driver determines that an Excel column contains
text data, the driver selects the data type (string or memo) based on the
longest value that it samples. If the driver does not discover any values longer
than 255 characters in the rows that it samples, it treats the column as a
255-character string column instead of a memo column. Therefore, values longer
than 255 characters may be truncated. To import data from a memo column without
truncation, you must make sure that the memo column in at least one of the
sampled rows contains a value longer than 255 characters, or you must increase
the number of rows sampled by the driver to include such a row. You can increase
the number of rows sampled by increasing the value of TypeGuessRows under
the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel registry
key.
)
Hi Folks,
My situation is that Excel files are to be downloaded into a SQL Server 2005 table (perhaps as type image or nvarchar), which serves as a document repository. From there, they should be converted to XML. Use of an NT file directory is strongly discouraged. I would like to have SSIS read the Excel from one field in a table and then write the XML into another field in the same (or perhaps another) table. Is this possible? If not, is the a strait-forward way to do this?
Also, I€™m hoping to invoke the SSIS script from a SQL Server INSERT trigger so the conversion is done during the INSERT.
Thanks,
Rob
I'm trying to import some XLS files that I receive from some suppliers. The problem is that every time they send some columns with text values but formatted as number. When I read those columns with SSIS Excel Source, they come all with null values.
I don't want to change the columns data types every time, so I would like to know if there's a way to bypass the column types that are already there.
I tried to use both the Jet driver and the Office 12 driver. I've already used the IMEX=1 on ExtendedProperties too with no success. Is there a way to force reading the columns as text, even if they have data types assigned to them?
How to load .xlsb file using Excel source component in SSIS. Below is the connection manager i see in the properties window.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="Excel 8.0;HDR=YES";
Do I need to change any values here to process .xlsb file
I'm using SSIS 2005 Enterprise edition, I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server). When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.
The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it. URL... but it only works when the first row of data has a number beginning with zero on this column. How to get the data imported correctly?
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
Thanks,
Manisha
I am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.
For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".
And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.
So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.
Thanks & Regards,
Prakash Srinivasan
Hello,
I have a problem with retreving a excel data through excel source component.
I have source component as Excel Source which will connect to my .xls sheet.
To retrieve the values from the sheet i am using a query as,
"SELECT F14,F3 FROM [Charac Defn & Assgnment$]"
The column F14 is not formatted so that the format of the cell is "General" I have a different type of values in the F14 column such as "PE","PES",15,20,20.00,8888.9999 etc..
While i click on preview button of Excel source it shows only the text values and not the int or decimal values, its returning NULL for those cells. I tried to use convert function, its throwing an error as
TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
Undefined function 'Convert' in expression. (Microsoft JET Database Engine)
Is there any other function to change the format of the cell or i need to some thing else
Please help me how to solve this issue.
I have a requirment which i have partly accomplished , but could not get through completely
i have a file which comes in a standard format ending with date and seq number ,
suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .
then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder
what i have taken apprach is
script task select source file --------------------> data flow task------------------------------------------> script task to destination file
dataflow task -------------------------> does count and copy in delimited format
what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .
but cannot work the dynamic pick of a source file.
please advise with your comments or solution you have
Hi
I am having a huge xml file with nested section.
i also have a xsd file for that xml.
i have a destination table where the data from the xml should be loaded into.
i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins.
so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination.
on comparing the two approach the one using the script source is working much faster than the xml source transformation.
i wanted to know is there any limitaion using the script source to parse through xml files.
also i would like to know any other better way of getting the data from xml source without using the joins.
Hari
I have a source file name which changes every day , basically the filename like 9500 , the next file would come is 9501 , then next could be anything may be 1000Q. I need the source connection to pick dynamicallly those file name. is their any way i could achieve this in SSIS.
I would not know what is the name of the file i would expect
please advise