Last week i got stuck with a question in my mind, it’s very usual to run a SSIS Package through the .batch file but can we run a .batch file through SSIS package? The answer is “yes”. yes we can execute batch file through SSIS package. i know it’s very easy and can easily find this on Google. after that i was stuck with some more curious questions can we pass parameter to a batch file? it’s also easy to Google and got the answer “YES”. we can pass upto 9 parameters to a batch file.
Now i give one up to my requirements, So here we are going to move multiple files from different-different location and pass the variable to SSIS package for source location to destination location. like we have 3 files on different source location F: , F:\FirstFile\ and in F:\SecondFile\, and I want to move all these 3 files to F:\DestinationFolder\.
So first we create a batch file:
For Move a file the command is : move F:\SecondFile\second.txt F:\DestinationFolder\
But here we are going to pass parameters to the batch file and we can pass up to 9 parameters to a batch file. for more details about batch file parameter the below link will help you.
So when you call a batch file, you can enter data after the command that the batch file refers to as %1, %2, etc. For example, in the batch file Testlocation.bat, the following command
@echo move %1 %2 file transfer
move F:\SecondFile\second.txt F:\DestinationFolder\ file transfer
if you called it as
Testlocation.bat F:\SecondFile\second.txt F:\DestinationFolder\
Now create a table which has a source location column and destination location column for two parameters,
CREATE TABLE FilePaths ( ID INT Identity ,SourceFolder VARCHAR(4000) ,TargetFolder VARCHAR(4000) )
INSERT [dbo].[FilePaths] ( [DashBoard] ,[SourceFolder] ,[TargetFolder] ) VALUES ( N'a' ,N'F:\first.txt' ,N'F:\DestinationFolder\' ) GO INSERT [dbo].[FilePaths] ( [DashBoard] ,[SourceFolder] ,[TargetFolder] ) VALUES ( N'b' ,N'F:\SecondFile\second.txt' ,N'F:\DestinationFolder\' ) GO INSERT [dbo].[FilePaths] ( [DashBoard] ,[SourceFolder] ,[TargetFolder] ) VALUES ( N'c' ,N'F:\ThirdFile\Third.txt' ,N'F:\DestinationFolder\' ) GO
Now we are going to create a SSIS Package for this:
First create some variable
Drag and drop a Execute SQL task
Now add the result set to this SQL Task. IDObject which will store all the ID values from the table.
Than pass this result set values to for Each loop, in that for each loop we will convert each value from IDobject into LONG value in a Execute SQL task.
and pass the SQL statement SELECT +'”‘+SourceFolder+'”‘, ‘”‘ + TargetFolder+'”‘ FROM RoambiBatchFileInfo WHERE ID = ?
Also we need to pass the result set with Single Row as shown in below screen shot
Now we reached to our last and main part of this package where we are going to execute the batch file,
first drag and drop Execute Process task and pass the path of batch file in Executable:
Left the argument field blank here and pass the argument through the Expression:
pass the variable through to expression argument with concatenation and add a space in between. like shows in below screenshot.
Finally our SSIS package look like the below screen shot:
Now execute the package and after completion the package files are moved to the destination folder.
Hope you will like this post on Microsoft SQL Server.
If you really like reading my blog and understood at least few thing then
please don’t forget to subscribe my blog.
Connect With me on