Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Dynamic modification of SSIS packages

A common feature of DTS packages is that they can be dynamically edited at runtime. This is accomplished by using ActiveX script code to alter the DTS package which is in itself an instance of the DTS Object Model. Once you have access to that object model (which in script is done by using the line "DTSGlobalVarables.Parent") you can navigate over the variables, steps and tasks of that package changing them accordingly.

DTS developers used this ability to allow them to do things such as:

  1. Altering workflow based on certain conditions
  2. Implementing looping within a package
  3. Dynamically changing a data pump's source, destination & column mapping using metadata available at runtime. This technique is used to build generic loading packages that can load from any data source as long as the metadata is known.

#1 & #2 here are achieved much more easily in SSIS by use of expressions on precedence contraints and the For/Foreach loop containers. Not the case with #3 however.

You cannot do this (i.e. change the metadata of a data-flow at runtime) in SSIS. DTS developers may be reading this aghast at what I'm saying but I'm afraid its the truth. Yes there is an SSIS Object model, just as there is the DTS Object Model. Yes, you can reference parts of that object model from script code. But you cannot change it.

There are some other options available to you though:

  • Although an SSIS package's object model cannot be changed at runtime by script code you can still build dynamic packages. Variables (and the use of them) have a much bigger story to tell in SSIS than they did in DTS. Variables now have scope, they can be evaluated as an expression rather than just storing an explicit value, they can have namespaces, they can raise an error when altered and, in conjunction with property expressions, can be used to dynamically alter tasks at runtime. A typical use of a variable with a property expression would be to build dynamic SQL statements in the Execute SQL Task. The screenshot below shows a very simplistic example of doing this:
Property Expression Builder

Here you can see how a SQL statement has been dynamically generated using a value stored in the "KeyValue" variable.

Property expressions can be used to change any property of a workflow task and are therefore very useful indeed. What they cannot do is change properties of data-flow components hence they cannot be used to do the same as described in #3 (above)

 

  • If you want to dynamically affect the column metadata of a data-flow at runtime you're outta luck, it can't be done. However all is not lost. You have the option of using code to instantiate a new package at runtime or change another existing package and execute that package within the within the confines of your curently executing package. I haven't attempted to do this yet but plan to at some point. Keep checking this blog to see how I get on.

- Jamie

 

 

 

Published Monday, February 28, 2005 11:02 PM by jamie.thomson

Comments

 

jamie.thomson said:

Is there no "Dynamic Properties" task in SSIS?
I always use that task in SQL 2000 DTS to set Data Pump source and destination at run time.
March 1, 2005 7:30 AM
 

jamie.thomson said:

Paul,
Ah, that's one thing I neglected to mention that I should have done. You can change connection manager properties at runtime using configurations. I said in the above post that you cannot change data-flow component properties at runtime but the properties you need to change do not fall into this category.

Configurations are a method of changing any property of a control-flow task or connection manager at runtime. I'll put up another blog post about this in good time but until then you may want to check out http://www.sqlis.com/default.aspx?26 & (to a slightly lesser extent because it doesnt explain exactly what you want) http://www.sqlis.com/default.aspx?27

I blogged about this previously at http://blogs.conchango.com/jamiethomson/archive/2004/11/29/349.aspx but there isn't enough info here so I will write something more detailed soon.

-Jamie
March 1, 2005 10:04 AM
 

TrackBack said:

March 19, 2005 4:39 PM
 

Jason said:

Jamie,

How do you recommend approaching the #3 situation. I have a series of extracts that all work the same way, and I would like to use the same package and just modify some data flow properties for each extract I perform, but I'm running into the limitation that you outlined...what are the alternatives?

Thanks,

Jason.
August 2, 2005 9:25 PM
 

jamie.thomson said:

Hi Jason,
If you want a truly generic solution then there is only one alternative that I know of.

You need to build a custom task that can be supplied the metadata, build a package in memory at runtime, and then execute the package.
I'm not saying for one minute that that is easy, but there you are. Up to now it is the only downside I have seen of the move from DTS to SSIS (admittedly quite a big one).

-Jamie

August 2, 2005 10:13 PM
 

Ananda said:

In one of my packages, I was using a variable to set the connection string for the file connection manager but the connection manager didn't pick up the variable value. The variable was having a design time value of 'C:\temp\import.txt' and surprisingly when I changed this to 'C:\\temp\\import.txt' the connection manager picked up straightaway. I couldn't understand this as design time value should be redundant anyway for the variable. I see your comment to use package configurations to change connection managers at run time. Am I doing something which is not recommended or hitting a bug?

Thanks and regards,
Ananda
August 13, 2005 1:10 PM
 

jamie.thomson said:

Hello Ananda,
I'm a little confused about this. You say that you are using a variable to set the connection string (I presume using Property BLOCKED EXPRESSION but then mention package configurations as well which are something different.
Could you send your package plus source file and I'll try and look into this. I can't promise anything as I am very busy at the moment :)

-Jamie
August 15, 2005 7:09 PM
 

Adrian Crawford said:

Hey Jamie,
Thanks so much for your insightful blog. It has helped a great deal in getting off the ground with SSIS. In regards to dynamically effecting dataflows at runtime, I thought that I had heard that in the new June CTP that certain properties in dataflow tasks, not all, would be able to use variables and such. Am I wrong on this? I believe Kirk Haselden had blogged about this coming about, but I can't seem to make it work on any dataflow properties.
Thanks,
Adrian Crawford
August 16, 2005 2:00 PM
 

Jim Rushing said:

Hello, thanks for the information. This has saved a lot of time.

I am trying to write a script task that will dynamically configure logging for the current package. Is this possible?? I want to check and see if any SQL log providers are configured. If not, create one so that the other tasks in the package will make use of the SQL error loggin.

I am I dreaming here ??

Thanks!!
Jim
January 27, 2006 1:24 AM
 

Agson Chellakudam said:

It is very hard to believe that, SSIS is not allowing to set dynamic mappings to Data Flow Task...
My DTS workflows are having almost all the transformations which are created dynamically(From a metadata Database)..I am now little worried about how I can achieve this with SSIS...
I can present a scenarion where I am facing the issue..

I have a 'Data Flow Task', which having a OLE DB Source and Destination..
I am setting source and destination table name from a Package Variable...
From a loop container, I am calling this Data Flow Task...
Loop container will map source and destination table  variable..
Since the structure of these tables are varying, I want to change the Data Flow mappings Dynamically...
Is it possible through 'Script Task'?.. Or I am pointing the Scenario #3 Again(if it plz excuse me)?..

March 22, 2006 3:25 PM
 

jamie.thomson said:

Agson,
As alluded to in the post, this can be done by building a package based on that metadata.
Take a look at this which may help you: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

-Jamie
March 22, 2006 3:55 PM
 

Abha said:

can u please give an example where in we can change oledb connetion manager runtime. It will be really helpful.

Thanks

January 8, 2007 5:18 PM
 

SSIS Junkie said:

I've said on numerous occasions, in slightly different ways, that making the move from DTS to SSIS

January 15, 2007 10:36 PM
 

Steve said:

I had the issue of dynamically changing a flat file connsection string during mid execustion and have finally got it working.

I found that if I go in to expressions under the flat file connection and link the connection string to a variable, it does not work.

Rather, I had a stored procedure that has an output parameter which I stored in a variable. This is the connection string I want to set. I then call a script task which sets it as

Dts.Connections("OutputFileConnect").ConnectionString = Dts.Variables("OutputFile").Value.ToString().Trim

And all is fine.

Cheers

January 17, 2007 11:10 AM
 

Partha said:

Where are SSIS packages stored in SQL Server 2005 and how can I modify them?

February 6, 2007 2:28 PM
 

Vikram said:

Hi,

I am having 3 Execute Package Task in my package, basedupon the value(parameter) i want to run those packages. (maybe 1 or 2 or 3)

How can i do that

Thanks in Advance

February 18, 2007 11:46 AM
 

Paul said:

Hi Jamie, any clues on how to populate variable values at runtime by executing a SQL SP and mapping the outputs of the SP to the SSIS variables? I'm hoping I can do this in an Execute SQL Task...

Cheers!

Paul

March 7, 2007 10:47 AM
 

jamie.thomson said:

Paul,

You CAN do it using the Execute SQL Task. There's a good article on www.sqlis.com that explains all about the Execute SQL Task and what you can do with it.

-Jamie

March 7, 2007 3:01 PM
 

Paul said:

Cheers Jamie, found the article and got the variables working with the Execute SQL task. Useful stuff!

March 8, 2007 4:48 PM
 

Subhash said:

I have an urgent need, I have 5 tasks in DTS migrated to SSIS . DTS also migrates List of global variables like Packageserver, DestinationObject, DestinationDB, DestinationDBServer, PackageLogName

1) An Activex - in which I set values for _PackageLogName and DestinationObjectName

Here we use oPkg=DTSGlobalVariables.Parent

_PackageLogName= opkg.Name & "*.dts"

_DestinationObjectName =dbo.[Table]

The Values of above configurations are assigned to the one below respectively

2) Dynamic Properties task

DestinationObjectName (Destination table on the DataPump task)

LogFileName (Some Path used to store the log file)

once completed connects to

3) An Execute SQL Task to truncate the destination table

4) A source and a Destination Connection

In which Datapump for Destination object is set to change under Dynamic properties

Would you please provide me a fully working SSIS equivalent, as I am really confused about lots of things to be changed once the package is migrated to SSIS

March 9, 2007 12:47 PM
 

jamie.thomson said:

Subhash,

No, I won't I'm not here to do your work for you not to provide support. I you want help, head for the SSIS forum http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1

-Jamie

March 9, 2007 2:48 PM
 

Guna said:

March 30, 2007 10:19 AM
 

Dieter said:

Hi Jamie, Can you tell me if it is possible to use other properties for a component in an propperty expression. i e I have a flat file connection which is using a system variable as the folder/directory to store write to but the file name I want to using is the name of the connection mananger. So something like

@[User::varErrorDir]+ @[Property::Name] +".txt"

May 17, 2007 3:00 AM
 

jamie.thomson said:

Dieter,

No That's not possible I'm afraid.

-Jamie

May 21, 2007 11:11 PM
 

andrew.chudley said:

Can you please solve a mystery in the ExecuteSQLStatementsInLoop.dtsx sample.

The CreateTables FileConnection does not have a file set because it is dynamically provided, presumably from the vFileName variable which in turn is set by the For Each loop. That's fine.

1. How was the CreateTables connection created in the first place as a usage type of Existing File with a blank File is cannot be saved?

2. How does the CreateTables File Connection access the vFileName variable as it doesn't seem to be set anywhere?

Thanks

June 13, 2007 3:09 PM
 

andrew.chudley said:

Mystery solved. You have to set the connector to a file, even though it will be dynamically replaced. Once the package runs it blanks out the file path as if it were saved with a blank entry.

June 13, 2007 4:58 PM
 

Joyce said:

I am reading a list of database names (from sysdatabases) into a recordset object.  I then use a for each loop on the recordset to set another variable to the database name.  This allows me to set the connections' IntialCatalog dynamically.  That works great; however, if for some reason it can't connect, I would like to ignore that one & continue in the loop without failing the pkg.

I've tried setting maximum error count & ForceExecutionResult, but nothing has worked.

Any help is appreciated.

Thanks!

June 18, 2007 10:20 PM
 

jamie.thomson said:

Joyce,

Try setting FailParentOnFailure=FALSE on the task that is failing along with ForceExecutoinResult=Success.

-Jamie

June 18, 2007 10:35 PM
 

John Jayaseelan said:

Good Morning,

I tried to assign a value stored in a variable to the flat file source connection using a script task as below and received the following error message.

Dts.Connections("REJECTION_FILE").ConnectionString = Dts.Variables("gv_Extract_file").Value.ToString().Trim

Error Message:-

The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.

The sql server build is 'Microsoft SQL Server 2005 - 9.00.3042.00 (X64) '

Any solution to solve this?

Thanks,

John Jayaseelan

June 22, 2007 8:57 AM
 

Annetch said:

I used SSIS wizard to import text file to table in database but this wizard ask for fixed source file and I want it to be variable. Please help me on my code in .NET or may be a stored procedure in SQL on how to pass in the package a variable source file. Nid help. Thanks.

June 23, 2007 10:20 PM
 

DoesNotCompute said:

What a cok$ucker this subash guy is! why don't you just give us your project requirements and we can complete them for you?  Then you can say to your manager -- dubba-dubba ding ding ding i have completed everything.

August 18, 2007 3:00 PM
 

Papillon2811 said:

Hi

If i have wrong input file with me i.e suppose my DB table has three columns and my text file has two columns then how i come to knoe that this is wrong file..how i get error that third column is missing???

August 30, 2007 6:32 AM
 

Jeff said:

Hi,

I'm trying to have all of my SSIS packages write to the same log and rather than add this variable to each package which I would have to change at some point in the future I would rather set the logging provider programmatically.  Whenever I try to access the SQL logging provider though logging doesn't work.  I have a feeling it is because I am using the wrong ConfigString for the provider but I can't find any examples of what the config string should look like for an OLEDB source.  Any clues?

Thanks,

Jeff

September 17, 2007 8:05 PM
 

Kiran said:

I have one doubt... Here i have to load CSV files into sql server database. But the problem is the columns order is not fixed every time in the input files. Is there any solution for this type of issue?

October 10, 2007 3:32 AM
 

jamie.thomson said:

Kiran,

Not really. You could try building packages on the fly but I don't recommend it. Your best bet is to endeavour to get all your files delivered in the same format every time.

I suppose if you knew all possible combinations of column order that you might possibly get then you could build a dataflow for each case. At execution time you would have to work out which dataflow to execute.

-Jamie

October 10, 2007 5:33 AM
 

joshcsmith13 said:

I don't think I've seen this question yet... I have a package that needs only a few tweaks to be able to run in different environments.  Specifically, the sqlCommand of a lookup transformation.  Is there a way to get a SSIS package to create a copy of itself??  I'm saving the package to a file.  If I could get the package to copy itself and then modify the copy, there should not be any access violations, right.?

October 25, 2007 9:52 PM
 

Korshikov said:

Can use variables in SQLcommand string in DataReader Siurce component?

December 18, 2007 8:26 AM
 

Karthik said:

Hi,

I wanna create a package that wud change its source and destination dynamically.

is this possible, if so cud u provide the links

January 2, 2008 2:01 PM
 

jamie.thomson said:

Karthik,

Use configurations or the /SET option of dtexec.

-Jamie

January 3, 2008 8:40 AM
 

toni said:

I'm new to ssis, so if I'm not making sense I apologize in advance. I have a simple package with only one data flow task. in the data flow task I am moving records from one table to another, massaging data in-between using the script component. What i would like to know is how can I use variables to actually tell my oledb source which data to pull? i.e. in oledb source, I would specify "sql command from variable" and than use that variable (which should somehow hold the dynamic sql statement)...

I would be calling this package from a c# application passing in the variable values... I hope this made sense. Thank you in advance.

January 22, 2008 5:07 PM
 

jamie.thomson said:

toni,

Take a read of this:

Evaluating variables as expressions

(http://blogs.conchango.com/jamiethomson/archive/2005/03/19/SSIS_3A00_-Evaluating-variables-as-expressions.aspx)

I think it will help.

-Jamie

January 22, 2008 5:12 PM
 

toni said:

Jamie,

thank you for the fast reply... I did read that article before posting... I created a variable and set the EvaluateAsExpression to true and I created an expression and the expression seemed to evaluate fine, but if I want to use that variable in my oledb source (using oledb source editor, specifying "sql command from variable" in data access mode and selecting the previously created variable in variable name dropdown), it will not work. Do I have to somehow dynamically assign the oledb source data access mode prior to my data flow task? if so, how would you accomplish that? Thank you again.

January 22, 2008 5:26 PM
 

jamie.thomson said:

Bizarre. That should most definitely work.

Sorry, beats me why it doesn't work. When you say "it does not work, what exactly happens?

-Jamie

January 22, 2008 5:33 PM
 

toni said:

I get following error:

"Statement could not be prepared." and "Incorrect syntax near '='"

This is how my variable properties  look like:

Name: select

EvaluateAsExpression: true

Expression: "select * from employee where emp_id =" +  (DT_STR, 5, 1252)@[empid]

Value: select * from sr6h where emp_id =

ValueType: string

One bizarre thing does happen though... I have another variable named empid and if I delete it than my expression does not evaluate. I do however remember reading that you can not use other variables when building an expression, so I am confused as to why it works with the variable there? I might be wrong though. In any case, I am not able to use the variable with the expression as my data access mode in oledb source.

January 22, 2008 5:45 PM
 

jamie.thomson said:

AHA! You shouldn't type anything into the 'Value' property. leave that alone, it should display the result of the evaluated expression.

"I do however remember reading that you can not use other variables when building an expression"

That's not true. There wouldn't be a lot of point if you couldn't do that.

-Jamie

January 22, 2008 5:58 PM
 

toni said:

well, I did not put that value in there it is done automatically... I tried to take it out like you suggested but the value is being put there again (automatically)... any other suggestions. Should I maybe set the variable value at "runtime", add a script component to the package maybe, before my data flow task and set the variable value there? but than how would I assign that value to my oledb source?!

January 22, 2008 6:04 PM
 

jamie.thomson said:

toni,

I must be missing something then because how can:

"select * from employee where emp_id =" +  (DT_STR, 5, 1252)@[empid]

evaluate to

select * from sr6h where emp_id =

???

Is that a typo?

-Jamie

January 22, 2008 6:13 PM
 

toni said:

yeah sorry it's a typo... should be "employee" not sr6h...

btw. I figured why the expression evaluates to "select * from employee = "...it's because I have no value assigned to empid variable. Should I assign it some bogus value at first and than assign the right value at runtime? that might be a solution to the problem huh?

January 22, 2008 6:21 PM
 

toni said:

that was it. I added a value for the empid variable and I was able to select the "select" variable as a data source. Thank you Jamie for your help...

January 22, 2008 6:25 PM
 

Chris said:

I want to build a data flow that would connect to several different servers and pull data into a local table. I want to make the connections to those servers dynamically so that I could loop through the data flow with each connection. The loop would read in a new variable for the connection string and process the data flow. Nothing else needs to change except for the connection string variable. If this would work I would never have to edit the SSIS package, only the table or file that the connection strings reside in. IS THIS POSSIBLE? Thanks

January 24, 2008 5:02 PM
 

jamie.thomson said:

Hi Chris,

Yes, its possible. Here's an example I put together:

Execute SQL Task into an object variable - Shred it with a Foreach loop

(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx)

There's plenty of other examples around the web.

-Jamie

January 24, 2008 5:17 PM
 

PS said:

Hi Jame,

I am executing a package whose configurations i am changing at runtime. The source in my case are excels and destination is SQL Server. Unfortunately, when I change the configuration file , these changes are not being picked up by the configuration file and it still goes and searches for the data in the location which it was given when creatng a package. I am new to this . I think there could be an issue with the way the package is designed and the manner in which the config file is being generated, Could u please suggest how to go about it . I need to set the connection string proerty dynamically for both the source and the destination, move the file to a success folder or the failure folder depending upon the status . All this is being acheived now also but I need to assign the paths dynamically .

Thanks,

Prashant

March 9, 2008 3:17 PM
 

Aaron said:

I think you may find this interesting.

I have two file connection managers that have their connection strings set dynamically by an expression.  The expression simply concatenates a root directory from a variable with a hard-coded file name (e.g., @[User::RootDir] + "package1.dtsx", where @[User::RootDir] = "C:\Ssis\Packages\").

The package is designed to execute multiple (in this example, two) packages simultaneously using the Execute Package Task (EPT) and each EPT is configured to use its own connection manager as described above.

However, when I execute the main package with DTExec the package fails and the error is not always the same.  Sometimes the package reports that the variable is the wrong data type to be used in an expression.  It is a String.  Other times, it says that it cannot find the path to the package for one of the EPT tasks, but executes the other one.  The path for the one it cannot find shows the path that I provided when configuring the connection manager at design time and not the path it should have from evaluating the expression.  On subsequent executions, it will flip-flop which EPT task it cannot find.

Have you run into this before?  Any thoughts?

Btw, If I make one EPT dependent on the execution of the other (so they don't execute at the same time) or add an Execute SQL Task to delay the start of one of the EPT tasks, then the main package executes successfully.

March 29, 2008 1:00 AM
 

jamie.thomson said:

Aaron,

Strange. I'm assuming that the 2 conenction managers are not pointing to the same file?

-Jamie

March 31, 2008 7:40 AM
 

Aaron said:

That's right.  The two connection managers each point to their own .dtsx file under the same folder.  The folder path is stored in a variable and used in the expression for both connection managers, appending the unique file name for each.  It seems as if there is a variable access conflict when the expressions are evaluating at the same time and reference the same variable.

March 31, 2008 10:19 PM
 

jamie.thomson said:

Its possible. Do you have a service pack installed?

-Jamie

March 31, 2008 10:22 PM
 

Aaron said:

Yes, I have two servers running SQL 2005 Dev. Ed. with SP2 installed, one is 32-bit and the other is 64-bit.  Both servers have the issue.

Thanks for responding to this issue I unfortunately encountered.  I appreciate your insight.

April 1, 2008 2:27 AM
 

Jim Carter said:

Aaron, do you have script tasks or components?

Apparently, copy and pasting packages with those can cause an issue..

http://support.microsoft.com/kb/928323

June 2, 2008 5:33 PM
 

justin said:

every time i have to open the connection manager and to set it manually for all package is there anyautomated way to set datasource for all package?

June 5, 2008 9:42 AM
 

jamie.thomson said:

Justin,

Use configurations.

-Jamie

June 5, 2008 10:17 AM
 

Nick said:

I'm hoping someone can help.  I've got several packages that I'm trying to convert from DTS.  These are all data exports from a SQL database to an Excel file.  In DTS I can change the Excel file name or path based on the date.  The results typically look like:

\\somePath\[month]\my file.xls

\\somePath\my file [year].xls

\\somePath\my file [date].xls

So I'm trying to do something like this in a script task:

       Dim cnn As ConnectionManager

       cnn = Dts.Connections("Excel Connection Manager")

       Dim myfile As String

       myfile = cnn.Properties("ExcelFilePath").GetValue(cnn).ToString

       myfile = IO.Path.GetDirectoryName(myfile) & "\" & Now.ToString("MMMM") & "\" & IO.Path.GetFileName(myfile)

       cnn.Properties("ExcelFilePath").SetValue(cnn, myfile)

If I step through this code, I can see the ConnectionString change for the connection.  However, once I leave the script task (still debuging the rest of the package), if I look at the properties on the Excel Connection Manager, it still has the original file name.

Am I missing something?  I'm considering moving/renaming the file after the export is complete, but there has got to be a better solution.  I'm starting to play around with configurations, but I've hit a wall.  Is this the right direction to go or is there a better way?

June 12, 2008 4:06 PM
 

Facundo said:

Hi,

first i want to say thanks for all your help. I always find here new ideas and solutions for my problems in SSIS.  Just wanna ask if there is any workaround about setting dynamically the table name in an OLEB Destination. Currently i'm using Package Configuration to set properties dynamically but table name property doesn't exist.

Thank you guys in advance.

July 2, 2008 2:55 PM
 

jamie.thomson said:

Facundo,

I don't have a SSIS instance to hand but if I remember correctly its possible to configure the OLE DB Destination take the name of the destination table from a variable.

-Jamie

July 2, 2008 3:49 PM
 

Facundo said:

Thanks Jamie, i realized that i had to set AccessMode=OpenRowset From Variable in OLE DB Destination Advanced Editor in order to set a variable as value for OpenRowSet .

July 2, 2008 7:12 PM
 

mparash said:

Jamie -

I am having problem updating a connection in SSIS package. I am trying to edit it as it's set to localhost with windows auth but I need to change it to a server name with SQL Auth ... I am able to test the connection and it works fine but when I hit ok it freezes for while and is again set to localhost and old settings.

Need help

Thanks

August 6, 2008 10:53 PM
 

jamie.thomson said:

mparash,

Strange, never sen that before I'm afraid. Do you hae an expression on the property that is changing it?

-Jamie

August 8, 2008 9:21 AM
 

Maju V Poulose said:

Hi Jamie,

Can u tell me is it possible to do a data driven task to a flat file destination with dynamic mapping. I am doing the data import from Inter Base to Text file. I am giving Inter Base table name through a variable, so the variable will forward different table names to import. I am using single flat file for the task. In this case multiple tables should import to a single flat file, so the mapping may be different in each time. Is there any solution for this ?

Thanks

Maju

August 22, 2008 8:54 AM
 

jamie.thomson said:

Maju,

Are you asking if its possible to dynamically change the metadata (i.e. number of columns, column names, column types) of the dataflow? If so the answer is "no".

-Jamie

August 22, 2008 9:01 AM
 

Dave said:

What do you set the sql source type to in the General properties of the Execute SQL Task when you want to use an expression to build the sql statement?

August 26, 2008 3:27 PM
 

jamie.thomson said:

Dave,

"Direct input"

-Jamie

August 26, 2008 3:33 PM
 

Dave said:

Scratch that previous comment, I see now that I set the expression in the variable properties and not the proerties of the Execute SQL Task.

P.S.  Your blogs rock, Jamie

August 26, 2008 3:37 PM
 

Dave said:

So I can do it in the Execute SQL Task also then.  Under the general tab, sql source type is Direct Input.  Does sqlstatement remain blank?  Then I would go to expressions and set the sql statement source to whatever my sql statement is, and then it should run?

Is one method (setting the expression in the variable properties vs. doing it in the Execute SQL Task)  preferrable to the other for any reason?  Or is it six of one and a half dozen of the other?

August 26, 2008 3:43 PM
 

jamie.thomson said:

Dave,

Yes, you can do it in the Execute SQL Task exactly as you have outlined.

The choice about which to do is entirely up to you. Here's something to bear in mind: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/SSIS_3A00_-Using-variables-to-store-expressions.aspx

and by way of confirmation: http://blogs.conchango.com/jamiethomson/archive/2005/03/19/SSIS_3A00_-Evaluating-variables-as-expressions.aspx

-jamie

August 26, 2008 4:01 PM
 

Dave said:

Awesome, thanks for the help.

One last question that I think will be a quickie for you:  How do I specify a variable as the "sqlcommand" in a DataReader Source component?  I tried User::@Variable and @Variable but it doesn't like either....

August 26, 2008 4:16 PM
 

Dave said:

Sorry to waste your time on that one.  I just put the variable into an expression that populates the sqlcommand property.  Maybe I should do a little research first next time!  Thanks again for all the help

August 26, 2008 4:24 PM
 

jamie.thomson said:

Dave,

You can't! Don't worry though. Check the expressions of the containing Data Flow Task and you will see a property called

[<DataReaderSource-name>].[Sqlcommand]

Place an expression on that property and it'll work. Robert's your father's brother*.

-Jamie

*Bob's yer uncle

August 26, 2008 4:24 PM
 

sree said:

hi

November 1, 2008 6:00 AM
 

sree said:

hi,

dynamically generated sql command in datareader is not responding from .net windows application and working fine if i ecute the package or if excute the package from console application and web application but i need it through windows form. I urgently need , i am struggling from 2 days

November 1, 2008 6:04 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems