Control Tables in SSIS

vG_Ins_Audit_C  ::

"EXEC [USP_Ins_Audit_C]                           

                     @SourceTableName   = '" + @[User::vG_SourceTableName]  + "'                            

                    ,@TargetTableName   = '" +  @[User::vG_TargetTableName]  + "'                            

                    ,@TotalRecieved   ="  +  (DT_WSTR, 10) @[User::vG_Audit_Received]  + "

                    ,@TotalInserted   ="  +  (DT_WSTR, 10) @[User::vG_Audit_Inserted]  + "

                    ,@TotalUpdated   ="  +  (DT_WSTR, 10) @[User::vG_Audit_Updated] +"

                    ,@TotalFailed  = "  +  (DT_WSTR, 10) @[User::vG_Audit_Failed] + "

                    ,@TotalErrors    ="  +  (DT_WSTR, 10) @[User::vG_Audit_Errors]  + "

                    ,@TotalWarnings  ="  +  (DT_WSTR, 10) @[User::vG_Audit_Warnings]  + "

                    ,@StartDateTime  =' " + (DT_WSTR, 50)  @[User::vG_Audit_StartDateTime] + " ' "  + "

                    ,@EndDateTime   = '" +  (DT_WSTR, 29) (DT_DATE) Getdate() + "'"  + "

                    ,@PackageName   =' " +  @[System::PackageName]+ " '     "+"

                    ,@ControlflowName   =   '" +  @[User::vG_ControlFlowName] + "    '" + "

                    ,@DataflowName   =      '" +  @[User::vG_DataFlowName] + "'" + "                          

                    ,@Created_By    =           '" +  @[System::UserName]+ "'"+"

                    ,@Source_System    =    '" +  @[User::vG_SourceSystem]  + "'          "

vG_Ins_Error_C  ::

"EXEC [USP_Ins_Error_C]   

                                 @PackageName  = "  +  "'" + @[System::PackageName]  + "' "  + "              

                                ,@ControlFlowName     = '" +  @[User::vG_ControlFlowName] + "'" + "

                                ,@DataFlowName           = '" +  @[User::vG_DataFlowName] + "'" + "       

                                ,@SourceTableName     = '" + @[User::vG_SourceTableName]  + "'         

                                ,@TargetTableName      = '" +  @[User::vG_TargetTableName]  + "'

                                ,@createdby                      = "+"'"+ @[System::UserName] +"'"+"

                                ,@ErrorType                      = 'OnError'

                                ,@ErrorMessage              = 'Error while updating the Date_D'

                                ,@SourceSystem             = '" + @[User::vG_SourceSystem]  + "'   “

 

vG_Ins_PackageHistory_C  ::

"DECLARE @PackageHistoryID INT  EXEC [USP_Ins_PackageHistory_C]                                                  

                 @PackageName     = "  +  "'" + @[System::PackageName] + "' "  + "

                ,@LoadStatus           = 'Running'                  

                ,@SourceSystem       = "  +  " ' " + @[System::PackageName] + " '  "  + "

                ,@TransactionDate   = ' " +  (DT_WSTR, 29)  (DT_DATE)    GETDATE() + " ' " + "      

                ,@CreatedBy              = "+" ' "+ @[System::UserName] +" ' " +"

                ,@PackageHistoryID  = @PackageHistoryID  OUTPUT

                SELECT @PackageHistoryID AS PackageHistoryID      "

How to query excel from sql server

This post takes us through on how to query a excel file from SQL Server, doing this using the distributed queries.
Firstly, make sure that the “Run_Value” is set to “1” for “Ad Hoc Distributed Queries”
 

sp_configure 'Ad Hoc Distributed Queries','1'

GO

RECONFIGURE

GO

The below is the result that we are going to get, and we can confirm the change of the configured value by again running sp_configure ‘Ad Hoc Distributed Queries’

 Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.

If we do not do this, we will end up getting an error like the below.
“Ad hoc access to OLE DB provider ‘Microsoft.Jet.OLEDB.40′ has been denied. You must access this provider through a linked server.””
We can query data by using either of the OPENDATASOURCE or OPENROWSET to query the excel directly.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\test\Excel2Sql.xls;Extended Properties=Excel 8.0')...[Sheet1$]

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database C:\test\Excel2Sql.xls', [Sheet2$])

We need to make sure that the source excel file is accessible, means we need to give the correct location, and also we need to make sure that the excel file is not open during this select process, if it is open we would generally get the below error.
“OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Unspecified error“.”
“Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.”
Note: – we cannot use this method on a 64 bit OS System, running 64 bit SQL SERVER

SQL Like Statement

Say, if we want to search for a name, which starts with “A” and in which the second alphabet is something between “A” and “M”, then our result has to be the first name that we had inserted, i.e “Amy”. For this we actually use the “[ ]” square brackets, they are used for searching any single character within the specified range ([a-g]) or set ([abcdefg]).

SELECT * FROM #SQL_Like  WHERE ContactName Like A[A-M]%'

Using SQL NOT Like
Till now we have seen how to directly use the “Like operator”. Now we shall take up an example where we will add “Not” in front of our “Like”. For this, the example that we can take is, say we need a list of all the name’s that do not have the alphabet “A” in them, then our query would resemble the below.

1

SELECT * FROM #SQL_Like  WHERE ContactName NOT Like '%A%'

Using SQL Like Escape Clause
Everything seems to be fine till now, so, let’s think like this, the wild card symbols are really cool, but what if I need to look into a string for those wild card character’s, puzzled!!!
For this we have these escape clause J. Check the syntax at the beginning of the article
In the below example, my escape character is “!”, so I am actually trying to search for John%, of course practically no names will exist in this way unless there is a type!!! This is just for the sake of taking an example and looking at on how to use it

SELECT * FROM #SQL_Like  WHERE ContactName LIKE '%John!%%' ESCAPE '!'

Let’s take one more example, here we want to look for the underscore symbol, and that’s a wildcard symbol, so decide to make use of the Escape clause over here as well.

SELECT * FROM #SQL_Like  WHERE ContactName LIKE '%John!_%' ESCAPE '!'

The basic thing over here is that we need to look for a symbol that can be used in the escape clause, means something, that does not exist in our string.

Using SQL Like with Case Statement
As stated earlier, the sql like condition returns a true or false, so we can use this in multiple scenarios in conjunction with a CASE statement. Here in the below code snippet we are actually trying to find all the names that start with A, B. We can also construct similar thing where the condition can equal to 0.

view sourceprint?

SELECT * FROM #SQL_Like

WHERE CASE

WHEN ContactName LIKE 'A%' THEN 1

WHEN ContactName LIKE 'B%' THEN 1

END = 1

SQL Server Query to Find First and Last Day of Month, Week,Quarter, Year

----Start and End of Today
SELECT DATEADD(DD,DATEDIFF(DD,0, GETDATE()),0) as [Start Of Day]
,DATEADD(s,-1,DATEADD(dd, DATEDIFF(dd,0,GETDATE())+1,0)) as [End Of Day]

----Start and End of Week
SELECT DATEADD(WK, DATEDIFF(WK,0,GETDATE()),0) as [Start Of Week]
,DATEADD(s,-1,DATEADD(WK, DATEDIFF(WK,0,GETDATE())+1,0)) as [End Of Week]

---Start and End of Month
SELECT DATEADD(M, DATEDIFF(M,0,GETDATE()),0) as [Start Of Month]
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as [End Of Month]

----Start and End of Next Month
SELECT DATEADD(DD,DATEDIFF(DD,0,DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE()))),0) as [Start of Next Month]
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) as [End Of Next Month]

----Start and End of Quarter
SELECT DATEADD(QQ, DATEDIFF(QQ,0,GETDATE()),0) as [Start Of Qaurter]
,DATEADD(s,-1,DATEADD(QQ, DATEDIFF(QQ,0,GETDATE())+1,0)) as [End Of Quarter]

----Start and End of Year
SELECT DATEADD(YY, DATEDIFF(YY,0,GETDATE()),0) as [Start Of Year]
,DATEADD(s,-1,DATEADD(YY, DATEDIFF(YY,0,GETDATE())+1,0)) as [End Of Year]