March 6, 2012 at 12:21 pm
I am creating an SSIS package that is supposed to segment out one customer's data set from the database into another database. The 2nd database will be backed up and sent via SFTP to another location as a daily data update.
The long term problem I see (and am attempting to account for at the start) is the database this is being run against changes (and I have no control on these changes). Tables will be added or removed, same with fields. This is frustrating, but it is what I have to work with. I'm accounting for this by creating a log file during the SSIS process, but everything I know about VB is failing me. I tried to just use the file system object, but it would only return errors. My research suggests this is because each file must have an explicit Connection Manager object.
So this is where I'm at...still in testing only, but once I get this to work then I'll use this in the main process:
3 connection manager items:
** Transfer from database
** Transfer to database
** ExportLog.txt
Execute SQL Task: Get List Of Tables (Select Name From Sys.Tables Order By Name)
** This is just a basic list of tables that I'll use to compare against a known list of tables. The output goes to the variable User::vTableList.
Foreach Loop Task: Loop Through Table Names
** Collection:Enumerator = Foreach ADO Enumerator; ADO Object Source Variable = User::vTableList; Enumeration Mode = Rows in the first table; Variable Mappings:Variable / Index = User::vTableList / 0, User::vExportLogText / 1 (I believe this is wrong)
Inside the Foreach Loop - Script Task:Verify Tables Exist
** I'm testing for extra tables, but I also need to find a way to test if all of the tables I'm expecting exist.
** Script:ScriptLanguage = Microsoft Visual Basic 2008; ReadOnlyVariables = User::vTableList; ReadWriteVariables = User::vExportLogText; Edit Script...: See attached file
Data Flow Task: Export Log Test
Flat File Destination: Write To Export Log
Nothing can be set here due to an error message, "This component has no available input columns. Do you want to continue editing the available properties of this component?"
I am in a touch over my head here, and I have until the end of this week to have this up and working. I know it seems I'm trying to get to fancy and I'm sure many are asking why I'm doing it this way. To answer that, this is just the way that I came up with trying it. This will capture errors in case the package fails so that I have a basic idea of where to start looking.
Once I get past this part it should be fairly smooth sailing. I've been able to test for, drop, and recreate the database I'm going to populate for the backup. I have all of the select statements to segment out the data defined, and I can run the backup process while dynamically setting the date / time stamp in the file name. The SFTP transmission is being handled by a developer who has already done this with other files (he just does not know SSIS or even SQL that well).
Thoughts?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
March 6, 2012 at 12:54 pm
The Data Flow task contains the Flat File destination and the destination component is generating the error, is this correct? What is the source component in the Data Flow task that is connected to the destination? The error seems to indicate that there is no source component connected to the Flat File destination.
If you're only using the Data Flow task to write a log to a file, you can instead add and configure a SSIS log provider for Text Files by using the Configure SSIS Logs dialog box (click Logging on the SSIS menu). For more information on creating a log, see Implementing Logging and How to Enable Logging in Packages in Books Online.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply