Dynamic Query :: HELP!

I'm the new guy and I could use a hand. Any insight would be appreciated.

Basically my task is to create a dynamic way to merge columns from multiple rows. Way the table is set up data is imported and one entry may be up to 3 rows one column from each row can be merged to form a long description, I would like to create a view that would allow you to dynamically query this data and have the description be merged in the result set.

row1 x y z
row2 x b z
row3 x m z

results should look like :: x, (y + b + m) , z

Thank you in advance for any help you can provide!numbers? you can SUM() them, but description? can't be done

you'll need to write some sort of script, either in the application that calls the database, or mayhaps in a stored proc that can cursor through the rows|||numbers? you can SUM() them, but description? can't be done

you'll need to write some sort of script, either in the application that calls the database, or mayhaps in a stored proc that can cursor through the rows

Unfortunately the data is all of char type. :confused:|||Any suggestions of how to go about doing this using a procedure?|||http://www.dbforums.com/t1038027.html|||Thanks for the link...I can't say I totally understand the function though.

I hate to ask this, and sound like an idiot, but can anyone give me the english break down?|||Cut and paste this code in to Query Analyzer...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40520

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

sql

Dynamic Properties task Config file

I'm using a INI file with my Dynamic Properties task for the data sources for my connections in the package. Say the package goes from Q/A to production and the servers change. Now assuming the ini file is changed with the correct (new) server names etc. Where do we specify the location of the INI file?

This is really frustrating. Please helpHowdy,

Assuming you have a dynamic package properties task, open steps then DTSStep_DTSDynamic..... and double click on Property Name section called "Description". You can choose the Source to be "INI File" and the location is determined by the "...." button on the right hand side.

Cheers

SG|||Originally posted by sqlguy7777
Howdy,

Assuming you have a dynamic package properties task, open steps then DTSStep_DTSDynamic..... and double click on Property Name section called "Description". You can choose the Source to be "INI File" and the location is determined by the "...." button on the right hand side.

Cheers

SG

Yeah i figured that but once i move it to a different server i dont want to go through all the packages and changes the config file location. so i was wondering if it could be done through another way where promoting the package to another server w/o actually designing the packages all over again.

Thanks.|||Howdy

Well you can read all the config values from a table instaed of an ini file. So, you could just copy a table across into a database and use that as a config "module" that can be moved around.

Beyond that, I'd suggest its not straight forward.

Cheers,

SG|||I have tired that as well but once you move the DTS package to a different server the connections in the package still point to the original server. Also, for the DT properties in the package, how to specify the source connection to use for Queries in the package?

Thanks,

V|||Howdy

the problem is that you need to define local logons for the server, so each time you copy the package to a new server, you will need to manually alter the local server logon. No way around this I know of, unless all your servers, userIDs & passwords are the same.

A DT needs 2 connections : ( asuming you are copying data around on one server ) :

1 the source connection - server name & source database
2 the destination conn. - server name & destination database

To create a DT - click the soucre, then hold the control key & clikc the destination. Then while the two conenctions highlighted, do right click & choose Transform Data Task.

Cheers

SG

Dynamic packages and maxconcurrentexecutables

I have recently prototyped a system, in which I use meta data to build a package of execute package tasks.

Essentially, in a Script task I do the following:

1) Create a new package in memory.

2) I add variables, logging to this package.

3) To allow for precedences I create a sequence container for any and all execute pacakge tasks that can run in parallel.

4) To help with parameters specific to the execute package task, I add another sequnce container for the individual excute package task.

5) Finally I add the actual execute package task.

6) Save the package to local disk.

7) Execute this pacakge.

I hadn't been setting explicitly setting the MaxConcurrentExecutables, but upon opening the saved copy of the last package I built and executed it has the default -1 setting.

My problem is that while this in-memory package is running it appears to be only running a single executalbe at a time. I'm going to try setting maxconcurrentexecutable to 4 or some other number to see if I get some parallel execution going on.

The real question is "Is there a limitation on using dynamic packages that limits them to only run a single executable at a time?". I haven't found anything in BOL that leads me to believe there is, but It was very obvious that only one executable would run at a time when I test this out.

How are you testing this? If you run the dynamic generated package directly, do multiple executables run?|||Right now my testing involves watching the staging tables that I have. I expect to see them start populating with data somewhat simultaneously.|||Try opening the generated package, and running it directly through BIDS. There are a number of reasons why a package might not run concurrent executables (system resources, etc), but if it works in BIDS, then we can narrow it down to a code issue.

Dynamic feed of table name to Transfer SQL Server Object Task

Hi
I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically.
I have got a foreachloop container which it feeds the table names into a variable @.table_name (string).

Transfer SQL Server Object Task is with in foreachloop container

I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @.table_name

I would be grateful if you can give me any hint.
Thanks
S

The logic looks right to me...are you seeing any error?

I have a blog post that explains how to iterate through a SQL result set (in your case to get the list of tables) using a foreach loop conatiner.

I hope that hepls you

|||

Hi,

I have faced this issue where TablesList property of Transfer SQL Server Object task takes in list of tables to transfer. There is no way to dynamically set the property through SSIS variable because this property expects StringCollection object. If you declare an SSIS variable as Object and assign it to TablesList property of the task thru Expression, it will not work because expressions cannot evaluate Object data type

I did a workaround by creating a child package in ScripTask and adding a TransferSQL server object task programmatically and assigning the TableList property as StringCollection object in VB.NET

Thanks

Mohit

|||Thanks Rafael,
I feed the list of tables (as object) to foreach Loop Container and the loop will put them in to a string variable.
I have got other tasks in the foreach Loop Container as well as transfer SQL Server Object tasks and they do use the table name (the string variable with out the problem)
the problem starts when I try to feed this table name to Tablelist properties of transfer SQL Server Object tasks which it complians saying that I it can not assign the string value to the tablelist property.
I have even tried to feed a dataset(object -list of tables ) to that property and even that didn't work.

|||Thanks Mohit for your reply.
so in the child Script task you are populating the table collation the way it should be but how are you feeding it to TabeList Propery.
would you be able to attach the VB code please.
Many Thanks
|||

This is how to do it...

'create sql server object task to move tables

Dim MoveTable As Executable = Child.Executables.Add("STOCK:TransferSqlServerObjectsTask")

Dim MoveTableTask As TaskHost = CType(MoveTable, TaskHost)

'set properties

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True)

MoveTableTask.Properties("CopyData").SetValue(MoveTableTask, True)

'create a stringcollection of tables

Dim Tables As StringCollection = New StringCollection()

Tables.Add('Table1')

Tables.Add('Table2')

Tables.Add('Table3')

'create string collection for tables to transfer

MoveTableTask.Properties("TablesList").SetValue(MoveTableTask, Tables))

'set the source and destination connections

......................

'execute package and dispose

Thanks

Mohit

|||thanks|||I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?|||

Kolf wrote:

I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?

Can you try with any table with dbo schema wether you are able to transfer. Also check whether you have permission on the schema to access the table.

Thanks

Mohit

|||it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks|||

Kolf wrote:

it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks

It seems it does not understand schemas. Bcoz even creating the Transfer SQL server Object task in design time , I selected one table of schema1. I had same tablename for schema2. When i select one table from schema1 for table list property and close the task and edit again, I see both the tables of different schema selected automatically. Seems there is a problem. Suggest you to use tablename to maintain versions like tablename + "_" + VersionName.

Thanks

Mohit

|||

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

|||

Kolf wrote:

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

Just check in your code for this:

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True) to copy schemas thru Task and

MoveTableTask.Properties("SchemaList").SetValue(MoveTableTask,sc) is assigned list of schemas to transfer .

If does not work and you want to stick to schemas you can try dataflow tasks

Thanks

Mohit

|||Dataflow task can't be used as I have different tables with different table columns
would you be able to create a sample dtsx based on adventureworks please
thanks|||Thanks Mohit
the problem with dataflow tasks is that (my table names are dynamic and I have got a foreach Loop that feed the table names to a dataflow
and in dataflow I have got a
* OLE DB source ( which the data access methode has been set to Variable) and that's how I feed my table name (variable called User::source_table ) and an example value for it will be
[DT.1].TableA
which is pointing to [DT.1] and with in the OLEDB Source I can preview the data

* also I have got an OLE DB Destination which same as OLE DB source is reading the table name from a variable ( which I'm using the same variable name , as the table name and the schema on both servers are the same)

problem: in order for this to work I have to manually click on the column map section in OLEDB Destinaiton section and save the package(manually) as the tables and they columns are changing this method won't be possible.

I hope I 've explained it properly.
Many Thanks

Dynamic feed of table name to Transfer SQL Server Object Task

Hi
I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically.
I have got a foreachloop container which it feeds the table names into a variable @.table_name (string).

Transfer SQL Server Object Task is with in foreachloop container

I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @.table_name

I would be grateful if you can give me any hint.
Thanks
S

The logic looks right to me...are you seeing any error?

I have a blog post that explains how to iterate through a SQL result set (in your case to get the list of tables) using a foreach loop conatiner.

I hope that hepls you

|||

Hi,

I have faced this issue where TablesList property of Transfer SQL Server Object task takes in list of tables to transfer. There is no way to dynamically set the property through SSIS variable because this property expects StringCollection object. If you declare an SSIS variable as Object and assign it to TablesList property of the task thru Expression, it will not work because expressions cannot evaluate Object data type

I did a workaround by creating a child package in ScripTask and adding a TransferSQL server object task programmatically and assigning the TableList property as StringCollection object in VB.NET

Thanks

Mohit

|||Thanks Rafael,
I feed the list of tables (as object) to foreach Loop Container and the loop will put them in to a string variable.
I have got other tasks in the foreach Loop Container as well as transfer SQL Server Object tasks and they do use the table name (the string variable with out the problem)
the problem starts when I try to feed this table name to Tablelist properties of transfer SQL Server Object tasks which it complians saying that I it can not assign the string value to the tablelist property.
I have even tried to feed a dataset(object -list of tables ) to that property and even that didn't work.

|||Thanks Mohit for your reply.
so in the child Script task you are populating the table collation the way it should be but how are you feeding it to TabeList Propery.
would you be able to attach the VB code please.
Many Thanks
|||

This is how to do it...

'create sql server object task to move tables

Dim MoveTable As Executable = Child.Executables.Add("STOCK:TransferSqlServerObjectsTask")

Dim MoveTableTask As TaskHost = CType(MoveTable, TaskHost)

'set properties

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True)

MoveTableTask.Properties("CopyData").SetValue(MoveTableTask, True)

'create a stringcollection of tables

Dim Tables As StringCollection = New StringCollection()

Tables.Add('Table1')

Tables.Add('Table2')

Tables.Add('Table3')

'create string collection for tables to transfer

MoveTableTask.Properties("TablesList").SetValue(MoveTableTask, Tables))

'set the source and destination connections

......................

'execute package and dispose

Thanks

Mohit

|||thanks|||I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?|||

Kolf wrote:

I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?

Can you try with any table with dbo schema wether you are able to transfer. Also check whether you have permission on the schema to access the table.

Thanks

Mohit

|||it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks|||

Kolf wrote:

it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks

It seems it does not understand schemas. Bcoz even creating the Transfer SQL server Object task in design time , I selected one table of schema1. I had same tablename for schema2. When i select one table from schema1 for table list property and close the task and edit again, I see both the tables of different schema selected automatically. Seems there is a problem. Suggest you to use tablename to maintain versions like tablename + "_" + VersionName.

Thanks

Mohit

|||

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

|||

Kolf wrote:

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

Just check in your code for this:

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True) to copy schemas thru Task and

MoveTableTask.Properties("SchemaList").SetValue(MoveTableTask,sc) is assigned list of schemas to transfer .

If does not work and you want to stick to schemas you can try dataflow tasks

Thanks

Mohit

|||Dataflow task can't be used as I have different tables with different table columns
would you be able to create a sample dtsx based on adventureworks please
thanks|||Thanks Mohit
the problem with dataflow tasks is that (my table names are dynamic and I have got a foreach Loop that feed the table names to a dataflow
and in dataflow I have got a
* OLE DB source ( which the data access methode has been set to Variable) and that's how I feed my table name (variable called User::source_table ) and an example value for it will be
[DT.1].TableA
which is pointing to [DT.1] and with in the OLEDB Source I can preview the data

* also I have got an OLE DB Destination which same as OLE DB source is reading the table name from a variable ( which I'm using the same variable name , as the table name and the schema on both servers are the same)

problem: in order for this to work I have to manually click on the column map section in OLEDB Destinaiton section and save the package(manually) as the tables and they columns are changing this method won't be possible.

I hope I 've explained it properly.
Many Thanks

Dynamic Connection Strings in a Scipt

I am trying to split a .csv file on a week+location key. As there are 500+ locations and 52 weeks in a year this is not a manual task.

I have a Script component written but need to dynamically open/close the connection manager changing the connection string in between, to the Week+location key as the filename.

It has to be do-able because the ForEachLoop does it. But how do I?

You'll most likely want to use a ForEach Loop, variables and property expressions.

This blog post should help you out - Looping over files with the Foreach Loop

HTH,

~Matt

|||That would be fine if I was trying to concatenate many to one, I use the ForEachLoop a lot, but I am trying to split one into many. I can't see how to use an FEL for that, or am I just being dumb?|||

I see two approaches you could take:

1. Use a script task (in the control flow, not the data flow) to split up your CSV file into multiple files, then process them all using a dataflow inside of a for each loop.

2. Continue using your script component, but use a foreach loop to pass in the week + location key you want to process, and process them one by one.

~Matt

|||Maybe this post might give you some ideas: http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx|||

Ah, thanks John! I hadn't see that entry before. I'll keep that link handy for future reference.

~Matt

|||Thanks for the help.

The technique I ended up using has a script task inside a for each loop which sets the connect string before a dataflow connects using the new connect string and processing each file. Simple when you have a night's sleep!!
|||Can you please tell me how to set the connectionstring in script task dynamically ? I need to set the initialcatalog property of connectionstring and loop over multiple databases to transfer data in foreach loop in SSIS package. So I have taken the database names in a collection and looping each of them. However when I create another script in it, the visual studio breaks down and says you have encountered unhandled exception. Can you throw some light over it ?|||Can you post the script your are using? Sounds like you might have a bug in it.

Dynamic Connection Strings in a Scipt

I am trying to split a .csv file on a week+location key. As there are 500+ locations and 52 weeks in a year this is not a manual task.

I have a Script component written but need to dynamically open/close the connection manager changing the connection string in between, to the Week+location key as the filename.

It has to be do-able because the ForEachLoop does it. But how do I?

You'll most likely want to use a ForEach Loop, variables and property expressions.

This blog post should help you out - Looping over files with the Foreach Loop

HTH,

~Matt

|||That would be fine if I was trying to concatenate many to one, I use the ForEachLoop a lot, but I am trying to split one into many. I can't see how to use an FEL for that, or am I just being dumb?|||

I see two approaches you could take:

1. Use a script task (in the control flow, not the data flow) to split up your CSV file into multiple files, then process them all using a dataflow inside of a for each loop.

2. Continue using your script component, but use a foreach loop to pass in the week + location key you want to process, and process them one by one.

~Matt

|||Maybe this post might give you some ideas: http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx|||

Ah, thanks John! I hadn't see that entry before. I'll keep that link handy for future reference.

~Matt

|||Thanks for the help.

The technique I ended up using has a script task inside a for each loop which sets the connect string before a dataflow connects using the new connect string and processing each file. Simple when you have a night's sleep!!
|||Can you please tell me how to set the connectionstring in script task dynamically ? I need to set the initialcatalog property of connectionstring and loop over multiple databases to transfer data in foreach loop in SSIS package. So I have taken the database names in a collection and looping each of them. However when I create another script in it, the visual studio breaks down and says you have encountered unhandled exception. Can you throw some light over it ?|||Can you post the script your are using? Sounds like you might have a bug in it.

Dynamic Connection

I am trying to connect to multiple data sources of same type say (sql) pn different servers and run the same data flow task on all of them.

what i need to acomplish is make the connection dynamic, i tryed the sample flat file connection and i understand that i have to

1, declare a variable 'connectionstr'

2, in the connection manager properties > expression property i have to select connection string and exeression as @.[user::connectionstr']

then i am out of thoughts i know i must use FOR loop but how

even FOR loop has expression, enumerator.....

how would i point the connection string variable to a list of values(srvrname,databasename, tblname) stored in a table.

Thanks in advance...

Take a look at this example:

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

Here's a search that might have some other useful samples - http://search.live.com/results.aspx?q=foreach&q1=macro:jamiet.ssis&first=1