Get index creation Date

Select Name as IndexName,

STATS_DATE ( object_id , index_id ) as IndexCreatedDate

From sys.indexes where object_id=object_id('dbo.datagathering')

Single Row set

Requirements :If the query return only one row and also specific column value and that column value required to use for some store procedure .

Solution :

On the First Execute SQL Task , Point the Result set to Single Row.




On the SQL Statement select top(1) userID from Testtbl , It will return the Id number
and assign this ID value to resultset user variable userIDResult as shown as below intend to use for next SQL Task parameter Mapping.

On the parameter mapping , we assigned Resultset variable that  required for store Procedure.

use Expression in the variables

Press F4 on variables pane on the selected variable , Then Edit the Expression as

DATEDIFF("mi", @[System::StartTime] , GETDATE())

Then pass these  variables as an input parameter in the SQL Task as shown as below.

Result set property not set correctly

If variables are not assigned in a appropriate way , possible error occurs as follows.



diagnosing Steps.

1.On the Expression Editor may be variables are not used correctly .

e.g. when we want to override the connection string through variables , we need to assign our user defined variable to the system variable. In this case we need to select the system  servername property correctly as opposed to chose other property.

DTS_E_CANTLOADFROMNODE Error

If we point the location of the files wrongly in the dtsconfig file.

We may encounter the following error.

 The runtime object cannot be loaded from the specified XML node.
This happens when trying to load a package or other object from an XML node
that is not of the correct type, such as a non-SSIS XML node.

Deploying Multiple package from one Location to SQL server

Note:"dtutil.exe" application which is installed by default with SQL

Server installation.eg: C:\ProgramFiles\Microsoft SQL Server\110\DTS\Binn\dtutil.exe)

Main steps are as follows:

Hold+Right click on Location , desired path will appear in the command prompt

/Destserver :servername

/Foldername in SSIs : In our case Staging

for %I in (*.dtsx) means searching for all the SSIS packages

dtutil /File means invoking the dtutil tool

--staging/%~nI" : staging is the folder name and /%~nI is the continue of

sequence SSIS package from the Location folder

for %I in (*.dtsx) do dtutil /FILE "%I" /COPY SQL;"/staging/%~nI" /DESTSERVER localhost.

Reference :http://sqlserverzest.com/2013/11/04/sql-server-deploying-multiple-ssis-packages-into-sql-server-using-command-prompt-utility-dtutil/

Truncation Error

The Source and Destination datatypes has to be same length

In the component , Ensure Input and External Columns are the same fixed length and verify
hardcoded values doesn't exceed the datatype length values .

To resolve this , Drag the Derived column from the Toolbar Section.

Edit the Expression Property , drag the attributes from the Type cast Section appears in the  right side.

(DT_STR,15,1252)CHARFTFILEDVALUE

E.g., DT_STR is varchar  , 15 is the fixed length that will match to the desination,1252 is the default value ;CHARFTFILEDVALUE  is the actual column name.

 
 

Data Conversion for Excel

Correct the Unicode and non-unicode Characters

Unicode string [DT_WSTR] which is nvarchar

string [DT_STR] which is varchar

To resolve this , use Data converion to convert nvarchar to varchar avaiable in SSIS as shown as below


 

MSDB Access Denied

The following Error appears when try to expand the MSDB Database.

To workaround this

1. Ensure Database Engine Services ETL Server is running.

2.Locate the path as shown as below for 2008

C:\Program Files\Microsoft SQL Server\100\DTS\Binn

3. Edit the MsDtsSrvr.ini XML file , Ensure the ETL Servername is configured properly

4.If Access is denied , Go the component services and search for MsDtsServer as
shown as below

5.Right click and go to properties and view the security tab

6.Give Appropriate permission to the Configuration and click apply

7.Ensure  restart the SSIS Services to take effect of the Configuration changes in the MsDtsSrvr.ini XML file.

Ref: http://msdn.microsoft.com/en-us/library/ms137789(v=sql.100).aspx