March 14, 2012 at 3:49 pm
Hi Folks
I am looking for design guidelines from you for creating SSIS package to load Excel sheet into SQL Server Table.
I am using SSIS 2008 32 Bit.
here is info:
The workbook is located on SHARED Folder on REMOTE Computer.
The workbook has only 1 sheet.
Sheet has a data of 2 week in separate columns i.e. Week1_Total_Salary & Week2_Total_Salary
Workbook has FileName like this: FileName_01152012.XLS (MMDDYYYY)
I need to retrieve date from FileName & deduct 7 days so that i will get Week1-End-Date, which i need to insert as one-of column value.
So based on this info, i am planning to create package as follows:
1- Take ForEachLoop container which will loop thro all Excel files on shared folder & retrieve FileName in LocalVariable.
2- Inside ForEachLoop container, take 2 DataFlow Tasks.
3 DataFlowTask-1 will transfer data for week-1.
4 DataFlowTask-2 will transfer data for week-2.
So here are my questions:
Q-1
Is this design of Package looks ok, Or...I am doing something wrong, which later might come into picture.
Q-2
I have Excel sheet having .XLS & .XLSX extensions.
So if i use EXCEL-CONNECTION-MANAGER, then i need one connection manager for .XLS & another for .XLSX
Is there a way that i can use 1 connection manager for both if there files.
the fact is, In DataFlow Task, i am using Excel-Source which will be based on EXCEL-CONNECTION-MANAGER.
So if 1 EXCEL-CONNECTION-MANAGER can pull-out both .XLS & .XLSX files then Package design would become cleaner & easy to debug.
Q-3
Workbook has FileName like this: FileName_01152012.XLS (MMDDYYYY)
i am able to retrieve Date using SUBSTRING, but when i CAST to Date, like this:
(DT_DBDATE)
(
SUBSTRING( (@[User::ExcelFile_Location]), FindString( UPPER(@[User::ExcelFile_Location]), (".XLS"), 1)-8, 8)
)
it gives error like below:
Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_DBDATE.
Casting expression "(SUBSTRING((@[User::ExcelFile_Location]),FINDSTRING(UPPER(@[User::ExcelFile_Location]),(".XLS"),1) - 8,8))" from data type "DT_WSTR" to data type "DT_DBDATE" failed with error code 0xC00470C2.
I got some help from below, but its not working.
http://www.sqlservercentral.com/Forums/Topic414629-148-1.aspx#bm414681
SUBSTRING gives me this: 01152012
So if i can cast that as Date then later on i can manipulate Date easily ( deducting 7 days will be easier).
Can you guide me here...!!!
Thanks in advance.
DEVSQL123
March 14, 2012 at 4:19 pm
Hi Folks,
I got partial success for Q-3.
I was using expression inside Variable.
So i changed datatype of variable from String to DateTime & it started working.
I further enhanced it using below:
DATEADD("dd", -6, (DT_DBDATE)
(
SUBSTRING( (@[User::ExcelFile_Location]), FindString( UPPER(@[User::ExcelFile_Location]), (".XLS"), 1)-4, 4)
+
"-"
+
SUBSTRING( (@[User::ExcelFile_Location]), FindString( UPPER(@[User::ExcelFile_Location]), (".XLS"), 1)-8, 2)
+
"-"
+
SUBSTRING( (@[User::ExcelFile_Location]), FindString( UPPER(@[User::ExcelFile_Location]), (".XLS"), 1)-6, 2)
)
)
This gives me desired result.
But i am still looking for answer of Q-2.
Please reply me when you get chance.
Thanks
DEVSQL
March 15, 2012 at 2:15 am
Try using the ACE OLE DB provider (used for .xlsx). It should normally be able to read .xls as well.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply