Metadata on data flow path not updating

I have a data flow task with a single source and destination task. I'm having the source task creating a table from a variable expression and the destination table also created from a variable expression. I'm running this under 3 scenarios in which each scenario has a different source and destination table. They are different in name but close in table structure with the exception of one column being different. The Metadata for the source flow path seems to be "sticky" in that it is not modifying the source table structure in the flow to account for this different column. I'm not sure how to adjust this. Any ideas? I've modified several properties in the task and data flow but nothing seems to make this adjustment in run-time.Metadata cannot and does not change dynamically nor at runtime.

This is by design and there's very little you can do to get around that, short of coding your own application using the SSIS API.

metadata driven SSIS package--table variable

Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!

Oliver

Hmm,

I suppose you could do it that way. I am currently generating complete SSIS packages from metadata. No variables. Explicit references. You would still need to handle the objects of the package that change, like columns datatypes, etc...|||You cannot have 1 data-flow that is used for different tables (unless the structure of each table is exactly the same - which I doubt).

You will need multiple data-flows, one for each table.

Once you have your multiple data-flows, decide which one of them to execute using workflow expressions. Allan and Darren have a good example of this here: http://www.sqlis.com/default.aspx?306

-Jamie|||Patrick,

Do you think you could post a simple example of what you are doing? I think there are a lot of people in the SSIS community who are realizing they need to head toward dynamic package creation in order to achieve the maintainability that they were able to with DTS and ActiveX scripts.

Thanks,

Jason.|||Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver|||

Oliver77 wrote:

Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver

"More or less the same" isn't enough. They have to be exactly the same for them to be able to use the same data-flow.

-Jamie|||

Can we create a SSIS package using VB .NET? If we can, we can call metadata driven stored procedure to build the transformation task specifically for each table.

|||

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

|||

Oliver77 wrote:

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

One of the samples provided with the product shows how to build a package progamatically (although I've just looked at my Junne CTP installation and can't find it - perhaps its been removed?). Once you have mastered how to do that you could work on building them based on metadata. I haven't seen any examples of doing that - perhaps you'd be the first person to do it?

-Jamie|||Patrick,

DO you have sample code? Thanks!|||Patrick,

Can you give some rough ideas as what you are using in terms of programming languages, scripts or SSIS tasks to create metadata driven SSIS packages.

Many thanks!

Oliver

MERGING Variable in FOR LOOP COntainer

Hi All,

Seems like a simple task, but been a struggle.

Simply trying to move a group of files from one folder to another folder and renaming the files with the monthyear in the middle of the filename..

I'm using a FOR Loop container and works find. The added complexity is I'm trying to rename the files the same tine and putting the Month Year into the file name.

I guess the struggle is how to get the file name out so I can manipulate it.

I tried creating variable(V_SOURCE) which stores the file path and create another variable(V_FILENAME) to hold the filename. I believe on the expression page of the FOR LOOP editor if I select the the file name and extension radio button, it should put the file name in the V_FILENAME variable.

In my file task trying to join the two variables together in another variable, keep saying my path is wrong with the file task kicks off.

here's the syntaxt

@.[User::V_SourcePath] + @.[User::V_FILE_NAME]

JUst to follow up if someone can help me with syntaxt for the merge and if they have a better approach

to moving and renaming the files

|||

I haven't checked myself, but do you need to have a \ between the folder and file, or is it included?

You can check the value of your variables by setting a breakpoint and typing the variable into the watch window.

|||THanks is helping out getting a picture of what's going on|||

Dan Cleary wrote:

JUst to follow up if someone can help me with syntaxt for the merge and if they have a better approach

to moving and renaming the files

I have done that using the File System task. I just posted an example in my blog:

http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

I hope you find it helpful.

|||

Rafeal, looks like just what the docotor ordered, does the scope of the variable make a difference?

Having an issue when joining the SourcePath with the file name. The scope of those variables are at the package level not at For Loop.

I do appreciate your response

|||

Variable scope is not an issue here; if you need it, just define all the variables at the package level.

regards

|||

Rafeal your Blog was great and very useful. I uess my struggle is doing the syntaxt on the file name in the expression builder.

I'm trying to take a group of file that are currently named RPT_BANK_NAME_@.MONTHYEAR_BANKNAME.XLS and

convert it to RPT_BANK_NAME_03_2007_BANKNAME.XLS. I have the report named storec in the variable just not sure on the syntaxt to strip out the @.MONTHYEAR and replace with the month and the year.

Any suggestions?

I tried to do a substring but not recognizing that function in the eexpresion builder

|||

Dan,

I guess I don't understand what is the format of the original name of the file. What I don;t get is the @.MONTHYEAR part.

is 03_2007 the month and year of the package execution date? or are they part of the original file name?

|||

THe @.monthyear is confusing, it's just hardcoded in the report name.

SO what I'm attempting to do in my expresion is take the Variable which is storing the report name

RPT_BANK_NAME_MONTHYEAR_BANKNAME.XLS and replace the monthyear with the curent Month and date.

I tried this but the expresion keeps failing the validation checks. Pretty much what you had in your blog

@.[User::V_DestinationFolder] + SUBSTRING( @.[User::V_Invoice_File] , 1 , FINDSTRING( @.[User::V_Invoice_File],".",1) 1 ) + "-" + (DT_STR, 2, 1252) Month( @.[System::StartTime] )+ (DT_STR, 4, 1252) Year( @.[System::StartTime] )+ SUBSTRING( @.[User::V_Invoice_File] , FINDSTRING( @.[User::V_Invoice_File],".",1) , LEN( @.[User::V_Invoice_File] ) )

|||

You've got an extra 1 in the expression (see red below) After correction, and assuming V_DestinationFolder is c:\temp\, it gives c:\temp\RPT_BANK_NAME_@.MONTHYEAR_BANKNAME.-32007.XLS, which I don't think is exactly what you want.

@.[User::V_DestinationFolder] + SUBSTRING( @.[User::V_Invoice_File] , 1 , FINDSTRING( @.[User::V_Invoice_File],".",1) 1 ) + "-" + (DT_STR, 2, 1252) Month( @.[System::StartTime] )+ (DT_STR, 4, 1252) Year( @.[System::StartTime] )+ SUBSTRING( @.[User::V_Invoice_File] , FINDSTRING( @.[User::V_Invoice_File],".",1) , LEN( @.[User::V_Invoice_File] ) )

The one below returns c:\temp\RPT_BANK_NAME_3-2007_BANKNAME.XLS and uses the Replace function. However, I have not tested it in the context of Rafael's example, so you might need to make some modifications to get it to work for you.

@.[User::V_DestinationFolder] + REPLACE( @.[User::V_Invoice_File] ,"@.MONTHYEAR", ((DT_STR, 2, 1252) MONTH( GETDATE() )) + "-" + ((DT_STR, 4, 1252) YEAR( GETDATE() )) )

|||

John is spot on.

In your case, REPLACE is a better option since '@.MONTHYEAR' is a literal that is always part of the file name. The expression in my example adds the month and year at the end of the file name and before the file extension (.txt); so I used FINDSTRING.

BTW, notice the expressions we are given use @.system::startTime; which gives you the month and year of the package execution date; so make sure that meets your requirements.

|||

Guys, you've been a huge help! I'm doing a watch and looking at the V_DESTINATION_PATH and it shows the

following in the watch window

+ User::V_DestinationPath {E:\\Client Billing 2\\FTP_Incoming\\INVOICE\\RPT-234-3-2007-BILL234.xls} String

but when running the task getting a path error

[File System Task] Error: An error occurred with the following error message: "Could not find a part of the path.". , any idea?

|||Looks like your variable V_Destination_Folder contains too many slashes, perhaps.|||

When you view it in the watch window, it shows the escape characters, thus the doubled slashes. You can validate the value by using a script task with a MsgBox.

Dan, you might want to double-check that the folder and filename you are referencing exist. Also, check permissions to ensure you can write to that location.