Archive for June, 2010

This is in continuation of my last post https://dwhanalytics.wordpress.com/2010/06/14/zip-file-using-ssis/


1. Create a variable FullPathtoZip of string data
type to hold full path to zip file
created in last post that is C:\DestinationFolder\NameofZipFile.zip


Create another variable TargetFolder to hold value C:\TargetFolder,
where files are to be extracted


2. Repeat Step3 and Step 4 as per my last post


3. Step 5 will be
almost identical but slight change in argument


+" "+ @[User::FullPathtoZip]+"  "+"-o"+


Note:-o before folder path


4. Run your package


5. If the
file is password protected then argument will be modified like below


+" "+ @[User::FullPathtoZip]+"  "+"-o"+


6. What if
during extraction same file name already exist in output folder?

What you
want to do! Use switches mentioned as per below

Overwrite all destination files.

Skip over existing files without overwriting.
Use this for files where the earliest version is most important.

Avoid name collisions.

Argument will be modified like below

+" "+ @[User::FullPathtoZip]+"  "+"-o"+ @[User::TargetFolder]+"
"+"-p"+"PASSWORD" +""+"-aoa"



Read Full Post »

For this I will use7-Zip software, if you don’t have ,download
this it’s free!

Assuming files that has to zipped together are in
C:\SourceFolder and you have to output processed zip file in

1. Create two variables FileName and DestinationFolder
of string type to hold
File Name and destination folder values respectively.

Assign C:\DestinationFolder\ to variable DestinationFolder

2. Drag one “Foreach Loop Container” and edit this point to
directory C:\SourceFolder and provide file type if you have to filter on
certain file types, in my case I was supposed to zip only PDF files.

Go to Variable Mappings tab and use variable FileName to hold
all file names returned by  “Foreach Loop Container”

3. Drag an Execute Process Task inside “Foreach Loop Container”

4. Open Execute process, go to Process tab and point Executable to
C:\Program Files\7-Zip\7z.exe
or wherever you have installed

5. Go to Expressions tab and then expand this, then click on browse
button this will open Expressions designer 

Choose Arguments under Property window and then again click on browse button this will open the second editor, the


Then substitute values
in Expression window like below

"a"+" "+ @[User::DestinationFolder]   +
"NameofZipFile"+ ".zip " +" "
+"\""+ @[User::FileName]+"\""

Don’t forget to click on Evaluate Expression to check for any

Just to note in my case zip file will be created in C:\DestinationFolder\

6. Execute your Package




Read Full Post »

Recently i was supposed to generate a text file dynamically in run time with date and time appended to file name!

To accomplish this click on flat file connection ,in right hand side properties window will appear.
Look for expressions there and once you click on that another window will pop up asking for what you want to set expressions!

Choose connection string in drop down and then copy/paste below by modifying your directory.

I have choosen # to seprate date and time, you can choose your own!

Alternatively, you can use two variables say @FilePath and @Filename and compute like below if you are getiing this variable in runtime.

name”+”\\”+”FolderName”+”\\”+”SubFolderName”+”\\”+”FileNameTobeAppendedw ithdatetimeInEnd”+(DT_STR,4,1252) YEAR(GETDATE())+”-“+(DT_STR,2,1252) MONTH(GETDATE()) +”-“+(DT_STR,2,1252) DAY(GETDATE())+”#”+
(DT_STR,2,1252)DATEPART(“hh”,GETDATE())+”-“+(DT_STR,2,1252)DATEPART(“mi” ,GETDATE())+”-“+(DT_STR,2,1252)DATEPART(“ss”,GETDATE())+”.txt”

Read Full Post »

To find a column name in entire database or get all the tables where this column name appear!

And then generate dynamic SQL on that table



+’ ‘


+’ ‘

+’WHERE someCondition=value’

–here some condition if you want

FROM sys.tables

WHERE object_id in


SELECT object_id FROM sys.columns

–column/field name you are trying to find in

WHERE NAME=’colName’
–remove this condition if you are generating for all tables )

Read Full Post »

%d bloggers like this: