SSIS Study Note–16 oct 2014 – Read eXCEL FILE WITH MULTIPLE WORKSHEET

Things to note:

I am using a For-each Loop container and a Data flow Task inside it to read an Excel file with multiple worksheets and write the result to a flat file destination.

image

For the loop container, the enumerator is “Foreach ADO.NET Schema rowset”..

For the connection, we are using “.Net providers for OleDb\Microsoft access 12.0…”

image

On Connection Manager, click on “All” and add “Excel 12.0” to Extended Propeties.

image

I have defined a variable named “worksheet” with the scope to the whole package.  I use this variable to store worksheet names.

image

Back to Loop container.  I select the newly created connection, and for “Schema” I select “Tables”.  Here, each worksheet from the Excel file is considered as a table.

image

If I click on “Set Restrictions” button, I see the columns being returned by the enumerator.

image

I use “Table_Name” as this column will contain the worksheet name.  This is the 3rd (0 starting index) column of the result-set.  So, in “Variable Mappings”, I select the “worksheet” variable and assign index 2 to it.

image

Now, back to my Data flow Task, I have an excel source and a flat file destination.  for the Excel source, I select “Table name or view name variable” for data access mode and select the “worksheet” variable.

image

This means the Excel source will get the name of the worksheet to read from the user::worksheet variable.  The User::worksheet variable is getting populated from the for-each loop container with the names of all worksheets from the source excel file.

Some other tips:

Instead of for-each loop we can use a single Excel source and inside that we can write SELECT-UNION statement with the names of the worksheet.  this is easier to do if we know the names and number of worksheets beforehand.  if we do not know about the names and number of worksheets then for-each method is better.

When reading from Excel source, SSIS uses the first 8 columns to decide the data type for the columns.  This can create lot of problems.  To stop SSIS from guessing data type from 8 columns the easiest and best approach is to tweak the connection string of the connection manager.

image

Adding IMEX=1 in the connection string helps to do just this.  The string can be accessed from connection manager’s properties.

SSAS Deployment Error Due to Login failure

Lately I have been trying to learn SSAS.  I have been going though Stacia Misner’s video from Pluralsight about SSAS basics.  While trying to deploy the project I was getting the following error message:

Internal error: The operation terminated unsuccessfully.

Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘Dim Customer’, Name of ‘Customer’ was being processed.

OLE DB error: OLE DB or ODBC error: Login failed for user ‘NT Service\MSSQLServerOLAPService’.; 28000; Cannot open database “AdventureWorksDW2012” requested by the login. The login failed.; 42000

I was using ‘”Inherit” for Impersonation Information.  Luckily after little bit of search I found the answer from StackOverflow (http://stackoverflow.com/q/13986698).

To resolve this issue I had to add user ‘NT Service\MSSQLServerOLAPService’ into the database, give it READ access. 

How to get the last usage of a table

Our main database is never documented or source-controlled!  Probably does not sound new to you.  We have a dozen of application talking to this database all having their own tables and other objects.  Like the database, the applications are not documented either.  So no one knows for sure what the tables are for an application.  I tried this script to see usage of tables.  After running all the applications and using most of the ‘main’ features I got some idea on the tables being used by these application.

 

Here is the script:

 

declare @result table
(
Database_name varchar(50)
,Tablename varchar(50)
,last_user_lookup datetime
,last_user_scan datetime
,last_user_seek datetime
)

insert into @result(Database_name, Tablename, last_user_lookup, last_user_scan, last_user_seek)
SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
--AND ius.[object_id] = OBJECT_ID('tblproperty')
GROUP BY ius.[database_id], ius.[object_id];

select * from @result order by last_user_scan desc

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

I must thank Dave Pinal’s awesome blog sqlauthority (SQL SERVER – Identify Last User Access of Table using T-SQL Script) for the above code.

How to Get Only File Name from Full File Path

In our database we normally save the full path of files (i.e. photos, drawings etc.).  On many occasions I have to show only the actual file name.  Previously I was using PATINDEX and REVERSE for that, and I was also checking to see if the number of ‘\’s in the full path was zero or not.  I have now found a better way of doing this job.

SELECT
RIGHT('\' + MYFILEPATH, CHARINDEX('\', REVERSE(MYFILEPATH)) - 1)
FROM MYTABLE

How to Find Last Usage of Objects in SQL 2008 or higher?

When working with legacy database you will find lots of tables and other objects that you don’t use but have no clue if these are being used by any applications.  As we are moving our database to SQL Azure the need for having clustered index in all tables came up as requirement and we currently have many tables without clustered index!

So the plan is to figure out the tables not being used and then started to see how to add clustered index to remaining tables.

I have used the following SQL query to find out the last usage (seek, scan, update) for the tables in a heavily used database.

select OBJECT_NAME([object_id])
,last_user_seek, last_user_scan,last_user_update
from sys.dm_db_index_usage_stats
where database_id = DB_ID()

TSQL– From Datetime to String in yyyy-mm-dd hh:mm:ss.mmm Format

I had this requirement to create some dynamic SQL script for a client.  I had a data source with dates in UK format.  The script had to have dates in datetime’s normal format.  I was creating the script from the data source.  Lets look at an example:

 

I had a varchar value ‘18/10/2012’ in the data source.  I had to put equivalent datetime value in the database.  So the dynamic script was required to have correct value.

Using CONVERT (DATETIME, value, 103) gave me the correct datetime format but using that for dynamic SQL was not possible as the returned result was DATETIME.

So I had to again CONVERT the DATETIME into VARCHAR with option 121.  Here is an example:

 

 

2012-10-24_1037

T-Mobile, Orange and Everything Everywhere 3G Problem

Last morning I received an SMS from T-mobile (my current provider) stating that if I restarted my phone set I would see EE instead of T-mobile as a signal provider.  I did the restart and was able to see EE instead of T-Mobile at the top of the screen.  All fine and well.  Problem started when I wanted to make a phone call.  I was getting disconnected right away.  I went to the nearest T-mobile centre (Barnet Spires Mall), and the very helpful lady there was able to solve the issue.  Apparently Everything-Everywhere is having problem with its 3G network because of 4G network work.  The lady set my phone to get the 3G network from a manually selected list (when searching for available list two EE options came, she selected the bottom one).  The phone started working.  As I normally use the mobile only for phone calls I asked her is it better to change signal to 2G (GMS instead of UMTS).  She said that was also worth a try.  I changed my signal type to 2G.  This was also working fine.  During this rollover period for 4G I think this problem will go on for some time.

T-Mobile, Orange and Everything Everywhere 3G Problem

Last morning I received an SMS from T-mobile (my current provider) stating that if I restarted my phone set I would see EE instead of T-mobile as a signal provider.  I did the restart and was able to see EE instead of T-Mobile at the top of the screen.  All fine and well.  Problem started when I wanted to make a phone call.  I was getting disconnected right away.  I went to the nearest T-mobile centre (Barnet Spires Mall), and the very helpful lady there was able to solve the issue.  Apparently Everything-Everywhere is having problem with its 3G network because of 4G network work.  The lady set my phone to get the 3G network from a manually selected list (when searching for available list two EE options came, she selected the bottom one).  The phone started working.  As I normally use the mobile only for phone calls I asked her is it better to change signal to 2G (GMS instead of UMTS).  She said that was also worth a try.  I changed my signal type to 2G.  This was also working fine.  During this rollover period for 4G I think this problem will go on for some time.

Have Multiline Strings in VS

I frequently face situation where a string variable has a long length, so long that I have to scroll the screen left to right to see it.  What helps in this situation is to have breaks in that string so that the full string can be seen without having to scroll left-right.

Using “@” before the string’s value helps in this case.  For example,

string mysql = "select table1.col1,table1.col2,table1.col3, table2.column3, table2.col4, table2.col4 from table1 join table2 on table1.col1 = table2.col2"

can be written as:

string mysql = @"select table1.col1,table1.col2,table1.col3
, table2.column3, table2.col4, table2.col4
from table1 join table2
on table1.col1 = table2.col2"