Archive for May, 2010

In my case, I was supposed to take all excel file from a folder with similar schema and load them in table and then move to Archive folder.

Create a variable (right click on control flow tab and choose varaibles ) FileName.

FilePath will contain output of ForEachLoop that will hold complete path(path,file name and extension).

So i set up a For each Loop container to get all excel files and hold in variable  FileName. 

After loading files in database(Data Flow Task) i used File System Task .

I chose Rename instead of move file in File System Task,Already i had source connection to the files (FileName Variable above)

**Don’t forget to choose source connection as variable in file System Task and then coose FileName Variable in drop down.

I needed a destination connection so i created another variable FilePath

Click on properties like shown below and choose TRUE

Now finally set expressions of this variable with below:

substring(@[User::FileName],1,len(@[User::FileName])-findstring(reverse(@[User::FileName]),”\\”,1)+1)+”Archive”+”\\”+”Bak”+”_”+(DT_STR, 4, 1252)  YEAR( GETDATE()  )+(DT_STR, 2, 1252)  MONTH( GETDATE()  )+(DT_STR, 2, 1252)  DAY( GETDATE()  )+”_”+  right(@[User::FileName],findstring(reverse(@[User::FileName]),”\\”,1)-1)

In your case Archive folder can be in different location,please modify this code accordingly.

Then choose destination as variable File System task and choose this Variable.

Read Full Post »

In control flow window, right click and choose variables.

Then define three variables FilePath, FileName and IfFileExists of string,string and Boolean type respectively.

In same window drag one script task and two Execute SQL Tasks.

Open script task, make variables FilePath and FileName as read only variables .

IfFileExists variable as read write variable like below

Now click on design script and use below code

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim FLName, FName As String

If Dts.Variables.Contains(“User::FilePath”) = True AndAlso _

Dts.Variables.Contains(“User::FileName”) = True Then

FLName = CStr(Dts.Variables(“User::FilePath”).Value)

FName = CStr(Dts.Variables.Item(“User::FileName”).Value)

If File.Exists(FLName + FName) Then

Dts.Variables.Item(“User::IfFileExists”).Value = True


Dts.Variables.Item(“User::IfFileExists”).Value = False

End If

Dts.TaskResult = Dts.Results.Success


Dts.TaskResult = Dts.Results.Failure

End If

End Sub

End Class

Then set precedence constraint based on IfFileExists like below against both Execute SQL tasks

@[IfFileExists]==TRUE in one and @[IfFileExists]!=TRUE in other one

Finally, it looks like below:

Read Full Post »


Assume a table having name column having First Name and Last
Name seprated by space.


First get the index of space from right and then use this index
value minus one to get as many charcters from end by using RIGHT operator.




TABLE TableName








‘A Singh’


‘D Kumar’




CHARINDEX(‘ ‘, REVERSE (Name_Column)) 1) AS [LastName]










Read Full Post »

Open expressions for Send Mail Task and set FileAttachments property like shown below.

Multiple files are separated by pipe(vertical bar) ( | )

Read Full Post »


SSIS Error: Data conversion failed. The data conversion for column “”RECIPIENT”” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.

  • Right Click on Flat File Source and choose “Show Advanced Editor”
  • Go to “Input and Output Properties “ Tab
  • Expand “Flat File Source Output” and choose “External Columns


  • Select column that is causing this error (In my case column is “RECIPIENT” as per above error message) and on right hand side, increase length say 100 or 200 or depending on your column length
  • Now select “Output Columns” and select same column as above and make this value same as we did in Step 4, in my case its 200 as shown below.

  • Now run your package

Note: In my case column that’s caused this error is “RECIPIENT”


Read Full Post »

Get Primary Key Columns of A Table

–Run below script

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner

FROM all_constraints cons, all_cons_columns cols

WHERE cols.table_name = ‘[TableName]’

AND cons.constraint_type = ‘P’

AND cons.constraint_name = cols.constraint_name

AND cons.owner = cols.owner

ORDER BY cols.table_name, cols.position

Read Full Post »

%d bloggers like this: