Dynamic Column in Excel Source

Hi,I am having Excel Source Which needs to be imported into Sql Server Table using SSIS.In the Excel Source I dont have Month and Year Column.But in Table I have Month and year column and both the columns are Primary Key columns.So i am not able to Import data from Excel to Table.So is there any possiblities to add Columns Dynamically in Excel source inorder to get the Year and Month

More Related Resource Links

I have a situation where both the column names in the source (SQL) and destination (Excel) are being created dynamically. The number of columns, their positions, and datatypes remain the same.
How do I change the OLE DB Source and Excel Destination meta-data to handle these changes at run-time?

hi all
i am uploading excel (.xlsx) file. when i preview in excel source editor it shows all numeric value as 'null' value. it taking it has Unicode string [DT_WSTR] data type. i formatted all cells trying with general and text type but it still shows as
null value.
what could i do to this?
thank you ppl,
romal.

I am looking to develop a SSRS report, and the idea is that I want to view backup history on various DB servers. I would like to have a drop down list of servers, and from there I can choose a server and a query will run on that server. I have been trying
to find out some information on this, but my understanding is that this can be done via URL, but not sure if thats the best way to do this.
Thanks.

I'm trying to add some source redirect code to the end of a URL in a Data Form Web Part on a simple SharePoint aspx page. This is the basic url:
/itservices/helpdesk/Lists/Service%20Requests/DispForm.aspx?ID={@ID}
However I want to add a source redirect to the end of the URL so that it returns to a particular page rather than the list's default page. I have tried all of the following:
/itservices/helpdesk/Lists/Service%20Requests/DispForm.aspx?ID={@ID}&source=/itservices/Pages/MyOverview.aspx
That causes an error in the web part stating "This Web Part does not have a valid XSLT stylesheet: Error; A semi colon character was expected". I tried replacing the ampersand with the URL encoded character:
/itservices/helpdesk/Lists/Service%20Requests/DispForm.aspx?ID={@ID}%26source=/itservices/Pages/MyOverview.aspx
But when opening from this URL the form contains no data.
What am I missing or can this not be done?
Thanks.

Hi All,
I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column?
A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this:
SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable
Or is it better to use a DerivedColumn task in the DataFlow?
Or maybe it doesn't really matter...

Nuances of using Excel data sources with SSIS
Your Development Machine Setup
Needs Jet Drivers, available along with the default Office install so if you have Excel working fine locally, this is enough.
Installing the "Office Data Connectivity Components" (download file name AccessDatabaseEngine.exe) as well as having Office installed corrupts the Jet Driver stack so that SSIS in VS 2008 cannot create the object to read the XLS file. You will
see the title error "Unspecified Error" and "80004005" code.
Resolution:
Uninstall the Jet Engine pack on your development XP machine if you have Office. It will be listed as something like "Microsoft Access Database Engine (2010)".
Run Office 200x setup with the Repair option and reboot. Try configuring the task in SSIS and it should be able to read the Sheet names again and columns.
Live Server (32-bit and 64-bit)
Deploying your SSIS package to a server without Office 200x means it will not have the Jet Engine drivers to read XLS, XLSX files. You must install the 32-bit Office Data Connectivity Components - Jet Drivers mentioned above found here (http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en).
There are no 64-bit drivers; therefore regardless of 32 or 64-bit live environments, this step is the same.
You shoul

I want an example of error (not truncation) in individual row or individual cell that can occur in OLE DB Source component when source is Excel file.
I'm trying out Error Output of data task component. I have already tried out Error Output of OLE DB Destination component.

Hi,
I have a situation where I have a CSV that contains a bunch of data that populates a bunch of related tables with FK constraints directly. Thats no problem. The problem is that one of the columns specifies a filepath to an XML file that is supposed
to contain data to be populated in a table with an FK constraint. How do I specify the file name to the XML source component at runtime?
For example
TableA has columns:
TableAID GroupName
TableB has columns
TableBID TableAID (FK to Table A) MemberID (the data in question)
TableA has a 1 to many relationship with Table B.
The CSV has a bunch of columns including:
TableAID File path to list of members for each group.
I don't know how to solve this problem. I tried using a ForEach ... but I couldn't get it to work.
Thanks in advance for any assistance.

This is my first post i hope i get a clear answer for my question.
I recenty have been working on KPI's drawn from Microsoft Excel documents that i created. However, as we know, external data sources in excel don't work with sharepoint. If that was possible it would have saved the company a lot of time and effort
My questions are:
Is there any way to have external data sources with some advanced modifications, or any other way that would simulate this process? (External data update)
Can i directly update my data by tampering the database where the excel file is saved?
I don't know if the files uploaded in sharepoint are stored in a database, so i want some validation around this. (Name of the database, path etc.)
Hope you can shed some light to my questions
Thanks in advance
Theo

I would like to merge every 3 columns and so forth in excel. It will be as follow:
Column A,B,C as one column continued with D,E,F, and G,H,I, and so forth until say KU,KV,KW.
please let me know how to create the macros to select and then merge it. Thank you

I have an excel macro that pulls data down from columns within worksheets that will take an excel spreadsheet and save it as a PDF file into seperate folders based off criteria set in the files. Getting the files into the proper directory is not the problem
i'm having. When the script executes it saves the documents directly to our sharepoint site, but the custom columns A B C D etc are all blank and some are 'required' items which sets the document as checked out until i go into the list and enter the required
data.
Is there anyway to make this macro export as PDF and set the required data for me in the sharepoint column so it checks the documents in for me?
I'm figuring this is a simple set of meta datatags but I don't know the syntax for them and I didn't find anything like this on the forum anywhere else.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"http://DirectoryStructureHere/corporate%20BoFi/pdfs/" & NewFileName & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False 'Exports the print area as a PDF document named the new filename "Modified: to sharepoint"

Hi All, I am exporting gridview data into excel sheet. I have a column which is a code for which data is like 012,013 etc. But when this data is being exported , in the excel sheet I can see the data as 12,13,14. but actually it should be 012.... Is there any way to specify the format of such columns in my datatable so that they are in the same manner as in the database? Please provide some information on this.Thanks in advance.