SSIS : Registration information for this application needs to be updated in Script task or script component

Steps to resolve error in Script Task(SSIS)

 

 

 

1. Type regedit in command prompt and make a registry backup in local folder just in case something get wrong, you can again paste these in their original location. You need to export only HKEY_LOCAL_MACHINE and HKEY_CURRENT_USER to the local path.

2. Make a .bat file from the following text. Run from the command prompt with Admin permissions. This file will delete the keys (reg delete) mentioned, and also remove various directories (rd) related to loading SSIS settings in VSTA.

For SQL 2008:

rem replace HKCU\Software\Microsoft with HKCU\Software\Wow6432Node\Microsoft for x64 machines
REG DELETE HKCU\Software\Microsoft\VSTA /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f
rem replace HKLM\Software\Microsoft with HKLM\Software\Wow6432Node\Microsoft for x64 machines
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f

rem Some of these deletes may fail, which is OK
rd /s /q %AppData%\Microsoft\VSTA
rd /s /q %AppData%\Microsoft\VSTAHost\SSIS_ScriptTask
rd /s /q %AppData%\Microsoft\VSTAHost\SSIS_ScriptComponent
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTA"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptTask"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptComponent"

3. Followed by the following commands to re-register SQL Server Integration Services components:

SQL Server 2008:

cd "C:\program files (x86)\microsoft visual studio 9.0\common7\ide\"
vsta.exe /setup /hostid SSIS_ScriptTask
vsta.exe /setup /hostid SSIS_ScriptComponent

 

 It will definitely solve the problem.

 

Sql to get the week of year , month of quarter , month of year and quarter of year

Declare

@dd datetime;

Declare
@cntr int;

Set
@cntr=0;

Select
@dd=DATEADD (dd ,@cntr , '2013-08-11' )




Select

 
datediff
(week, dateadd(year, datediff(year, 0, @dd),0), @dd) + 1 weekOfYearCnt,

case
when (datepart(month,@dd))%3=0

then
'3'

else


(

datepart(month,@dd))%3

End
as monthOfQuarterCnt,

datepart
(month,@dd) as monthOfYearCnt,

Cast
(YEAR ( @dd ) as Varchar(4)) +' Q'+Cast((datepart (month,@dd) -1)/3+1 as Varchar(2)) quarterNm,

(
datepart (month,@dd) -1)/3+1 quarterOfYearCnt



 

Sql to getthe week of a month and week of a quarter

Declare

@dd datetime;

Declare
@cntr int;

Set
@cntr=0;

Select
@dd=DATEADD (dd ,@cntr , '2013-08-11' )

Select
 
(
DAY( @dd) +(DATEPART(dw, DATEADD (MONTH, DATEDIFF (MONTH, 0, @dd), 0))-1)-1)/7 +1 as weekOfMonthCnt,

DATEDIFF
(WEEK, DATEADD(QUARTER, DATEDIFF(QUARTER, 0,@dd), 0), @dd) weekOfQuarterCnt



 


 

Sql to get day of quarter and day of year

 
Declare
@dd datetime;

Declare
@cntr int;

Set
@cntr=0;

Select
@dd=DATEADD (dd ,@cntr , '2013-08-11' )

 
Select
 
DATEDIFF(d, DATEADD(qq, DATEDIFF(qq, 0, @dd), 0), @dd) + 1 AS dayOfQuarterCnt

,
DATEPART(dy, @dd) AS dayOfYearCnt


 

Sql to get day of week and day of month

 

Declare
@dd datetime;

Set @dd= '2013-08-11'
 
Select
datepart(dw, @dd) as dayofweekCnt , datename(dw, @dd) As dayOfWeekNm,DAY(@dd) AS dayOfMonthCnt

 

use of rowset destination in SSIS

 I want to create country specific text file dynamically contains the details of their employee having suffix as country name.

Suppose this is my Table named as ’CountryEmpDetails’

 

1>First open data flow task in SSIS.

Choose OLEDB Source from toolbox and open it. Then set the connection to database where your ‘CountryEmpDetails’ resides and select SQL command in Data Access Mode and paste the query as written here.

 

Select distinct Country from CountryEmpDetails

 

2>Create a variable names as ‘country’  and set its datatype as ‘object’

3> Then open rowset destination and set ‘country’  under VariableName property.

4> Define another variable ‘countryname’  and set its value null.

 

5>Open ‘ForEach Loop Container’ and set the property as shown below in the figure:

Select ‘Foreach ADO Enumerator’ and select ADO object source variable

as ‘user::country”

Then in the variable mapping part select “user::countryname”

6>Now create a separate folder name anything but in my case, I named it ‘Country’

 

And there I create one file contains the field like ‘name’,’designation ‘and ‘country’ and it is tab separated text file and saved it with any name . Here I saved this file as ‘EmpDetails.txt’

 

7>Under ForEach loop container drag another DataFlow task

8> Under Data Flow Task drag OLEDB SOURCE and FlatFile Destination.

Under OledB Source paste this query…and then click the parameter button to set the parameter value as ‘user::countryname’

 

9> Under Data Flow Task, first set the connection manager and browse the

EmpDetails.txt File and mapped the columns.

And then to make it dynamic, you have to follow the below mentioned steps.

a>  Define another variable ‘FilePath’ .

b>  Then  select the Filepath variable and go to property section on right side

 

Then Set the EvaluateAsExpression As ‘TRUE’ and open the wizard in expression part by clicking on  

 

And under expression part set the value like “Filepath”+countryname.

For ex:  "C:\\Documents and Settings\\mshukla\\Desktop\\Country\\EmpDetails_"+ @[User::countryname]+".txt"

c>  Then go to the FlatFileConnection Manager and select property by right click on it.

  Select Expression and then click on

 

After that Select the variable as “user::Filepath” in it.

 

 

d>Now run the package and u find that in your folder, 4 files creates dynamically having prefix as countryname and contains the contents of respected country.

If you see , EmpDetailsIndia.txt is created and contains the data for the employee belongs to country India.

 

 

 

 

 



 

 

 

 

 

 

 

Set the dynamic date in variable of SSIS

Suppose if you need to create dynamic file which contains the file name with current date and time associated with it  in SSIS,then you have to follow below steps:

1>Define a variable ‘FilePath’ and set its value as null.
   

 

 

  Then  select the Filepath variable and go to property section on right side.

 

Then Set the EvaluateAsExpression As ‘TRUE’ and open the wizard in expression part by clicking on  

 

And under expression part set the value like “Filepath”+"ddmmyy"-"hhmm".txt

For ex:  "C:\\Documents and Settings\\mshukla\\Desktop\\Country\\EmpDetails"+ "_" + (dt_str,4,1252)datepart("yyyy", @[System::StartTime]) + "_" + right("0" + (dt_str,4,1252)datepart("mm", @[System::StartTime]),2) + "_" + right("0" + (dt_str,4,1252)datepart("dd", @[System::StartTime]),2)+"_"+".txt"

 

 

 

  Then go to the FlatFileConnection Manager and select property by right click on it.

Select Expression and then click on

 

Then you get this window

After that Select the variable as “user::Filepath” under connection String property