December 2, 2009 at 4:01 am
Ive been given the task of automating the execution of an existing SSIS package. Im going to schedule a job on sql server to run the package once a month. Im pretty new to SSIS and have a few questions.
Some of the steps inside the package invloves the transfer of data from supplied .mdb files. These files have different names depending on the month they are for
eg 080209.mdb, 08032009.mdb
This name format should be consistant, but the dates will be different depending on which month im processing eg 08032009.mdb will be the next months file
At the moment the SSIS package is run manually and the contents changed to reflect the file being processed. In order to automate this, id need to use a variable which id determine from the system date (I think !)
so If was was processing the file in February, id need some way to 'create' the name of the file inside the package, i could then use that to load up the appropriate .mdb file
something like
ProcessFileName = FormatForfileName(date.now) + '.mdb'
this should give me a result of '08022009.mdb' (or whatever day/month/year was the current date)
Id then use that name for the name of the .mdb file in the the OLE DB Source object.
There are several files involved in this process, but they all have a consistent naming convention that includes the date.
So when I look at the details of an OLE DB Source object, it specifies the OLE DB Connection manager (which is the name of the .mdb file id be dynamically creating)
It will always be a table and the table name will always be the same for that particular OLE DB Source object.
As I said, Im a complete newbie to SSIS, so any help/pointers/resources would be greatly appreciated.
December 2, 2009 at 6:13 am
Hi there,
Add a Foreach loop container to your package and place the Data Flow task inside it.
Configure the Foreach loop:
- Collection Enumerator: Foreach File Enumerator
- Specify the location of the folder that contains the .mdb files
- Variable Mappings: Create a new variable to store the .mdb filename
In the Data Flow task:
- Select the connection manager that you use for the .mdb files, go to it properties and look for Expressions
- Expand the Expressions and click [...]. Set the ConnectionString Property to use the Variable you created in the earlier step.
This should give you the freedom you are after.
December 2, 2009 at 6:47 am
How would i create this variable ? it would have to be based on the current month and year (possibly a day as well, this will be confirmed)
Is it possible to code it based on my previous pseudo code ?
December 2, 2009 at 7:22 am
You can set the path of the file from an expression - which can include a variable...
I've not got an mdb connection setup, but principals should be the same as a flat file connection.
If you click on the connection, use the Properties window. You'll see the ConnectionString. Further down you'll see Expressions with a little cross. Expand and use the elipses button (...)
There you can set properties dynamically. Drop down the proeprty you're insterested in, then hit the elipses button for the expression you're going to use.
Use evaluate expression to check it works ok.
I've found Jamie's old blog useful. This may help http://consultingblogs.emc.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx...
Also I've found this site extremely helpful http://www.sqlshare.com/Channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd...
I am new to this myself!
December 2, 2009 at 7:58 am
Is it also possible to do the following with SSIS ?
use FTP to download a zip file to a specified location. Once this file has been successfully downloaded, unzip it, and once successfully unzipped, rename specified files to a common format (eg 'Financial090203.mdb' to 'Financial.mdb') and finally process the data utilizing the original stages in the SSIS package. In here Ill hard code the required .mdb file to 'Financial.mdb'
December 2, 2009 at 8:22 am
Ok bad answer, more than likely... not done myself.
All activity would likely stay on the control flow area. There's an FTP task, a File System Task to move, copy, create, delete files and folders. Though can't say about the zipping, perhaps the Execute Process Task? You'd need, if using WinZip, the command line utility.. I am guessing here though.
December 2, 2009 at 8:42 am
Ive googled this and found these steps
Steps:
1. Drag and drop execute Process task
2. Edit the component.
3.In the Process Menu,
'Executable' =C:\Program Files\WinZip\WINZIP32.EXE
'Arguments'= -e -o "fileNameinZipformat.zip "
Working Dir='C:\TestFolder '
4. Arguments tips: -e extract -o OverWrite exist
Im about to try it alongside the FTP task (If I can get the use of an ftp server somewhere)
will keep you posted
December 2, 2009 at 9:15 am
If you need a zip/unzip utility, check out this custom task:
December 2, 2009 at 2:11 pm
There are a few ZIP tasks out there - some free. Check out the SSIS Community Tasks and Components[/url] directory, I list them there.
Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
December 2, 2009 at 2:22 pm
There is also the TaskUnZip component available on CodePlex: http://taskunzip.codeplex.com/
December 3, 2009 at 4:52 am
Im following this example here
but converting it to c#.
The process seems very 'flaky' to me. For example, If I give the Foreach process a name and description, then come out of the editor, save and go back in, the name and description revert back to its original !
If I move to the collection area, i initially have no enumerator configuration option.
If I create variables, the enumerator configuration options magically appear. I entered a value into the configuration area, saved it, came out, went back in, modified it, came out of the editor, saved it, went back in and it kept the original values !! Also If i create any variables and give them values, i cant then go back into the variable editor to change the values ! If I delete the variables, they don't actually get deleted, so I have no way of removing them and staring again. I would think that if I create anything in this process that I should subsequently be able to go back and edit or remove them, but it doesn't seem to be the case.
Am I missing something fundamental here ?? Is it not possible to change anything thats created in the foreach loop process once its been created ?
December 3, 2009 at 6:56 am
Ok,
1. Renaming a task: You cannot use any special characters. keep it to simple alphanumerics and you'll be fine.
2. Enumerator Config: I can tell you are using SS 2008 (BIDS). Open the Foreach container task, then click the collections drop down and select the Foreach file enumerator option. This will give you the config screen.
3. Original Value: have no idea why this is happening for you. Anything I am attempting to update, remains updated.
4. Updating Variable Values: Manage your variables in the Variables window. (right-click the design area and choose 'Variables'). to update the value of a variable, locate the variable in the variables window, scroll across until you find the 'Value' column, enter the desired value and hit enter.
5. Deleting Variables: you can delete a variable by highlighting the variable (in the variables window) and clicking the icon with a tiny red cross, or hitting delete.
December 3, 2009 at 7:59 am
Also with Variables watch out for their scope...
They can exist globally in the package.. or within a data task. If you've declared a variable in a one data task and try to use it in another, you won't be able to.
December 3, 2009 at 8:02 am
You can also change the name and description of the object without going into the editor..
Select the object, then use the properties window. Under Identification you can set the names.
F4 is the shortcut to get to properties if the windows not open.
December 3, 2009 at 8:22 am
I think im slowly getting the hang of SSIS, its not particularly intuitive though, I was expecting to edit the variables in the edit window of the task, but had to open another window separately. I did, however, manage to achieve the functionality i wanted and now have the zip files being unzipped into my specified directory.
now on to the ftp task (see my other post for my problems with that)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply