Using dynamic excel file names with dynamic sheet names

Recently, I came across a problem when writing an SSIS package. I had done the dynamic names of SSIS excel files with the same meta data but using dynamic sheet names for the meta data came as a bit of a challenge to me. After a lot of googling, I simplified the steps to the following



-Declare two variables of String type

• SheetName - To fetch names of sheet from the excel file

• FileName - To fetch names of excel files.

- Create a Script task from which you want to fetch your sheet names 





-Write the following code in the script task

Public Sub Main()


Dim xlApp As Excel.Application = New Excel.ApplicationClass()

Dim xlWorkBook As Excel.Workbook

Dim xlWorkSheet As Excel.Worksheet



xlWorkBook = xlApp.Workbooks.Add(Dts.Variables("FileName").Value)

xlWorkSheet = CType(xlWorkBook.Sheets(1), Excel.Worksheet)

Dts.Variables("SheetName").Value = xlWorkSheet.Name


Dts.TaskResult = ScriptResults.Success
End Sub


-You can loop the Sheets if there are multiple sheets.


-Then, create another variable with the name of SQLcommand and give the following in it's expression:

"Select * from ["+ @[User::SheetName]+"$A1:CJ]"

Note: I used A1:CJ as my own range, so give the range you want the sheet to pick up.



-Give this variable in your excel source



-Now, to come to the point where all the excel files in the folder are traversed dynamically. To do this, create another variable called "SourceFile" of String data type
-Give this variable (usually configurable ) a path of the folder where your excel files reside.
-Then use a Foreachloop task with the following settings: 


Then, in this Foreachloop task, place your script task and the data flow task where the excel is getting transformed.

MDX source data to OLEDB destination in SSIS?

So once I was working with my usual SSIS packages and this time the source specified was fetching data from MDX. During the testing phase, everything went well and the staging ETL went smoothly as expected. An SSMS job was created for calling this package everyday.

After a few days, we realized there was something wrong with the data.The data populated in the stage table was very selected. For example if the MDX specified 3 years worth data, then only the data for 2 years would get populated. This was quite a mystery for us knowing that the job was running as scheduled and the MDX was bringing data once run separately. After some rnd, we found the following as our solution :-

Previously, I had simply used "OpenRowset" which was truncating some data. Changing this to "OpenRowset with Fastload" solved everything and I haven't faced that problem ever since. 

Pilot

So being new to blogging, all sorts of random ideas which turn into nonsensical rants (in my head) cross my mind. From SQL Server to professional environment experiences, from certifications to just plain blah, all could bring in some sort of  flavor. I could talk about the following though.

1. Experience with SQL Server 2008 R2
2. New functions in SQL Server 2010
3. My queries about Big Data and Hadoop
4. Other problems that I solved using my genius