SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving zip files


Moving zip files

Author
Message
steve.alston
steve.alston
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 127
Hi guys

This sounds like it should be simple but I'm banging my head on the brick wall again.
I have an SSIS process that extracts the contents of a zip file in a specific folder then processes it.

The contents of the zip are the same, the source folder never changes BUT the zip file name can be anything.

I'd like SSIS to move the source file to a specific folder (processed files) after the unzip is done. The Unzip works fine with a variable (*.zip) in the argument. My project uses variables to rename files from data they contain later on, but this time I want to leave the name the same, just move it. The standard file move uses a fixed name, and I can't seem to get a variable name to work. The flat file connection seems to barf on a zip file when I try it.
I don't need to do a for each loop, just a single move on a single file.

I'd be happy if anyone could point me in the right direction.
Thanks
Steve
Steve Jones
Steve Jones
SSC Guru
SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)

Group: Administrators
Points: 351192 Visits: 20201
Are there multiple files in the source? I'm not much of an SSIS guy, but the pattern I've seen is use a loop to process all files in a folder, move, load, ignore, whatever. Then as you process each file, you have the name of that file inside the package and can access that. Usually people loop through the files, do something, then move. You're the opposite in that you loop, move, then load. I'd think you really are looping, move, then another loop (perhaps another package) that processes the folder in which you've moved files.

If you process the folder and enumerate each file, the file task inside your loop should be able to access each name and load that.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)

Group: General Forum Members
Points: 137605 Visits: 22866
Steve has got it right, despite his 'not much of an SSIS guy' disclaimer Smile

Use a FOREACH loop to process the file, even though you know that there will be only one zip file present. Why? Because the FEL does the work of mapping the exact file name to a variable of your choosing (which also means that you can make your unzip command more refined).


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
xsevensinzx
xsevensinzx
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11586 Visits: 4364
Phil Parkin - Tuesday, February 6, 2018 5:50 AM
Steve has got it right, despite his 'not much of an SSIS guy' disclaimer Smile

Use a FOREACH loop to process the file, even though you know that there will be only one zip file present. Why? Because the FEL does the work of mapping the exact file name to a variable of your choosing (which also means that you can make your unzip command more refined).

Seconded. Very easy to setup and lots of guides online how to do this with any file extension. Matter of fact, I don't think I've ever not used one even when I know it's just one file. Just in case it becomes more than one, I don't have to go back to it.

Outside of that, I've actually used script tasks for unzipping with Python. Seemed easier to do those types of operations in another scripting language like Python or so that would unzip, rename, and so forth.

Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)

Group: General Forum Members
Points: 137605 Visits: 22866
xsevensinzx - Tuesday, February 6, 2018 6:16 AM
Outside of that, I've actually used script tasks for unzipping with Python. Seemed easier to do those types of operations in another scripting language like Python or so that would unzip, rename, and so forth.

Me too ... in C#.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)

Group: Administrators
Points: 351192 Visits: 20201
Sounds like someone should write me an article on this.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)

Group: Administrators
Points: 351192 Visits: 20201
This might help as well: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/99287/

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
steve.alston
steve.alston
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 127
Thanks guys. So many responses and all pointing me the same way!

My decision that I didn't need a loop was based on the single file process, but I guess the loop through a single file won't add much other than complication, and what I was trying was complicated and didn't even work!
A for-each loop with variables I can handle!
Thanks again guys.
xsevensinzx
xsevensinzx
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11586 Visits: 4364
steve.alston - Tuesday, February 6, 2018 2:34 PM
Thanks guys. So many responses and all pointing me the same way!

My decision that I didn't need a loop was based on the single file process, but I guess the loop through a single file won't add much other than complication, and what I was trying was complicated and didn't even work!
A for-each loop with variables I can handle!
Thanks again guys.

It's really not. I'm no SSIS expert either, but I remember reading one good article on how to loop through excel files one time and pretty much do it for every file I import, single or not. It's very easy to the point you should be able to do it by memory every time you make one.

I would only note here, that you should also consider file validation with your processing. Don't just import any file in a directory. Validate it first, then start your process.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search