Parameter passing to a DTS package

Hi,
I have a DTS package that exports a query result from SQLServer to a text
file . I sheculed it daily.
What I want to do is give the text file name as paramaetric, I mean,
e.g. salesresultddmmhhss.txt ddmmhhss : run time day month hour and second
Is it possible?Hi
The DTSRun utility has a /A parameter that allows you to set the values of a
global variable. This can then be used to set the filename. If the name is
consistent you should be able to do this in code without passing a value.
You may also want to check out http://www.sqldts.com/default.aspx?292 for
renaming a file and http://www.sqldts.com/default.aspx?234
John
"Banu_tr" wrote:

> Hi,
> I have a DTS package that exports a query result from SQLServer to a text
> file . I sheculed it daily.
> What I want to do is give the text file name as paramaetric, I mean,
> e.g. salesresultddmmhhss.txt ddmmhhss : run time day month hour and secon
d
> Is it possible?

Parameter Mapping in an Execute SQL Task

I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:

if exists

(

select name

from sys.databases

where name = ?

)

begin

drop database ?;

end;

go

create database ?;

go

This is the error I am getting:

[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.

Regards,

DO

Try creating three variables with the same value and map them to the parameters.|||go is not a valid TSQL command. You need to seperate into seperate ExecuteSQL tasks. If you then have 2 executeSQL tasks you should then find it works. You probably want to bypass prepare as I have found that the easiest way of getting the parameter mapping to work.|||

Kaarthik Sivashanmugam wrote:

Try creating three variables with the same value and map them to the parameters.

And if you do do that, the 2 new variables that you create only have to be made equal to the first variable by using expressions.

-Jamie

|||I tried your idea. Unfortuantely, it did not work. Anymore suggestions?|||I removed the "GO" command and tried separate tasks (one to drop the other to create). It did not work. I then set ByPass Prepare to true for both. Again no go. Please let me know if you can think of anything else.|||I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.|||

DatabaseOgre wrote:

I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.

They both achieve the same thing. It is of course your choice which you choose. I would always choose expressions because:

1) Less coding to do

2) Less work for the package to do (i.e. less tasks in your control flow)

3) Expressions use out-of-the-box functionality. Scripts are more of a workaround.

4) The expression is evaluated when the variable is called. If you go the script task route you have to make sure that the value is explicitly changed prior to calling it. Hence I think expressions are more intuitive and easier to be understood by a person who has to understand your package later.

-Jamie

|||

A couple of points. DROP DATABASE can't take a variable the database name.

Secondly I don't think that you can use paramaters in the execute sql task with a sql statement only an SP.

For this reason if you want to execute a sql statement you need to build it up in an expression and then execute that

|||Thanks. I did not know exactly how expressions worked, so the information is much appreciated. Since I am already using the Script Task to accomplish multiple tasks, I will probably stick to using it for now.|||

Thanks. I did not know that you can not use a parameter for a DDL statement. I tried to create a DB, table, view....nothing worked. I guess my options are to use dynamic SQL within the Execute SQL Task, pass the query in a variable, or a Script Task that makes a connection, checks for the db, etc. With regard to your second point, you can use parameters in a SQL query within an Execute SQL Task. However, it only seems to work with very simple queries i.e. select * from table where column = ?. Anything more creative than that seems to throw it for a loop. I am probably going to try the SQL query in a variable method. Let me know if you think of anything else. Thanks again for your post.

=== Edited by DatabaseOgre @. 10 Mar 2006 10:54 PM UTC===
I ended up using the SQL query in a variable method and it worked. Kudos to Jamie, I used one of his other posts to figure it out.

|||Yeh I think it has to be a single statement.|||If your complex query with parameters fails, then try setting "ByPassPrepare=true".sql

Parameter Mapping in an Execute SQL Task

I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:

if exists

(

select name

from sys.databases

where name = ?

)

begin

drop database ?;

end;

go

create database ?;

go

This is the error I am getting:

[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.

Regards,

DO

Try creating three variables with the same value and map them to the parameters.|||go is not a valid TSQL command. You need to seperate into seperate ExecuteSQL tasks. If you then have 2 executeSQL tasks you should then find it works. You probably want to bypass prepare as I have found that the easiest way of getting the parameter mapping to work.|||

Kaarthik Sivashanmugam wrote:

Try creating three variables with the same value and map them to the parameters.

And if you do do that, the 2 new variables that you create only have to be made equal to the first variable by using expressions.

-Jamie

|||I tried your idea. Unfortuantely, it did not work. Anymore suggestions?|||I removed the "GO" command and tried separate tasks (one to drop the other to create). It did not work. I then set ByPass Prepare to true for both. Again no go. Please let me know if you can think of anything else.|||I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.|||

DatabaseOgre wrote:

I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.

They both achieve the same thing. It is of course your choice which you choose. I would always choose expressions because:

1) Less coding to do

2) Less work for the package to do (i.e. less tasks in your control flow)

3) Expressions use out-of-the-box functionality. Scripts are more of a workaround.

4) The expression is evaluated when the variable is called. If you go the script task route you have to make sure that the value is explicitly changed prior to calling it. Hence I think expressions are more intuitive and easier to be understood by a person who has to understand your package later.

-Jamie

|||

A couple of points. DROP DATABASE can't take a variable the database name.

Secondly I don't think that you can use paramaters in the execute sql task with a sql statement only an SP.

For this reason if you want to execute a sql statement you need to build it up in an expression and then execute that

|||Thanks. I did not know exactly how expressions worked, so the information is much appreciated. Since I am already using the Script Task to accomplish multiple tasks, I will probably stick to using it for now.|||

Thanks. I did not know that you can not use a parameter for a DDL statement. I tried to create a DB, table, view....nothing worked. I guess my options are to use dynamic SQL within the Execute SQL Task, pass the query in a variable, or a Script Task that makes a connection, checks for the db, etc. With regard to your second point, you can use parameters in a SQL query within an Execute SQL Task. However, it only seems to work with very simple queries i.e. select * from table where column = ?. Anything more creative than that seems to throw it for a loop. I am probably going to try the SQL query in a variable method. Let me know if you think of anything else. Thanks again for your post.

=== Edited by DatabaseOgre @. 10 Mar 2006 10:54 PM UTC===
I ended up using the SQL query in a variable method and it worked. Kudos to Jamie, I used one of his other posts to figure it out.

|||Yeh I think it has to be a single statement.|||If your complex query with parameters fails, then try setting "ByPassPrepare=true".

Parallel execution of DTS steps.

Hi,
I have a DTS package that exports 50 database tables from SQL Server to
Oracle.
At this point the DTS package runs steps after each other (serial
execution). Since both servers are extremely powerful, the can handle more
than one concurrent DTS step at a time.
Is there any way to have DTS execute five or six steps in parallel fashion?
Any help would be appreciated,
MaxYes, you can. Basically, tasks run in parallel, unless you have set up
workflows that force them to be run serially. That said, if your source is
the same for all of your data pumps, then the process gets serialized, since
the connection is in use. In that case, create multiple connections, each
of which point to the same server.
For example, if you are pumping from ServerA to ServerB, create, say, 4
ServerA connections - ServrA1, ServerA2, etc. and the same goes for ServerB.
Then, make sure your pumps use the different connections.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:eobZWSBWGHA.5044@.TK2MSFTNGP09.phx.gbl...
Hi,
I have a DTS package that exports 50 database tables from SQL Server to
Oracle.
At this point the DTS package runs steps after each other (serial
execution). Since both servers are extremely powerful, the can handle more
than one concurrent DTS step at a time.
Is there any way to have DTS execute five or six steps in parallel fashion?
Any help would be appreciated,
Max|||Then you will have to use a different connection for each datapump.
AMB
"Maxwell2006" wrote:
> Hi,
>
> I have a DTS package that exports 50 database tables from SQL Server to
> Oracle.
> At this point the DTS package runs steps after each other (serial
> execution). Since both servers are extremely powerful, the can handle more
> than one concurrent DTS step at a time.
>
> Is there any way to have DTS execute five or six steps in parallel fashion?
>
> Any help would be appreciated,
> Max
>
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp

parallel execution

Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it

Regards
MThe easiest way is probably to have 3 copies of your Data Flow, each using a different File Connection Manager, and three copies of your File Connection Manager, each for a different file. You will also need to uncheck the "Table Lock" option on the destinations. If there are no precedences defined for your Data Flows, they will execute in parallel. Ugly, but simple.
There used to be a parallel execution option on the Loop task that would have allowed you to do this with one Data Flow, but it was removed.
It is also possible to use a master package that executes your current package three times in parallel, thus avoiding the Data Flow duplication above, but you'll face the challenge of having each instance of the package open a separate file. You would need to have three different Package Configuration files to define different values for a parameter that you would use in an Expression to set the ConnectionString property on your File Connection Manager. Complicated, but probably a superior design if you're up for it. You'll still need to turn off table locking.
Note that you'll need multiple processors to make this a worthwhile endeavor. Also, if your files are very large tempdb will suffer without the table locking.|||
Note that you'll need multiple processors to make this a worthwhile endeavor. Also, if your files are very large tempdb will suffer without the table locking.

Hey Jay,
Is it mandatory to have multiple processors for parallel execution for the above mentioned issue.
I have done all the above things that you have suggested, but it stills seems that the loading is happening sequentially. Please note that the my machine is single processor machine.

Regards
Meghana|||It shouldn't be mandatory, but I wouldn't expect you to see any benefits without multiple processors. I ran a test on a single processor machine and did not have any difficulty getting my Data Flows to run concurrently. I also discovered that disabling the table locking is not necessary.
One thing to check is the MaxConcurrentExecutables property. The only way I could force my package to run sequentially is to set this value to 1. The default value is -1, which means number of processors plus two, so it should let you load three files simultaneously.
I also tested performance. Using a 100k line flat file going directly into a local SQL table, I saw a 10% decrease in performance with two parallel files and 34% decrease with three.
|||

mmhaise wrote:

Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it

Regards
M

Have you tried using a MULTIFLATFILE connection manager? This will enable you to load data from all of the files using just a single data-flow (which is executed just once).
If the metadata of the 3 files is identical this should work very nicely.

If the metadata is different then you can use 2 or 3 Derived Column Transforms to make the pipeline metadata identical and then use a UNION ALL transform to combine the 3 data-paths into one. Again, this is a single data-flow that you execute just the once.
If you are intent on loading the 3 seperately then executing a package 3 times as Jay suggested would be the "cleanest" approach because you can reuse functionality rather than having 3 seperate but identical data-flows.

I would definately look into using the MULTIFLATFILE connection manager though if I were you.

-Jamie|||

Is it possible to make the MULTIFLATFILE connection load in parallel? I have tried using one to load 4 csv file in parallel to the same destination table. The data flow is loading them sequentially.

I have another package with four separate file connection managers and data flows; however, I thought it would be nice to use the MULTIFLATFILE since the files have identical metadata. We require them to load in parallel.

parallel execution

Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it

Regards
MThe easiest way is probably to have 3 copies of your Data Flow, each using a different File Connection Manager, and three copies of your File Connection Manager, each for a different file. You will also need to uncheck the "Table Lock" option on the destinations. If there are no precedences defined for your Data Flows, they will execute in parallel. Ugly, but simple.
There used to be a parallel execution option on the Loop task that would have allowed you to do this with one Data Flow, but it was removed.
It is also possible to use a master package that executes your current package three times in parallel, thus avoiding the Data Flow duplication above, but you'll face the challenge of having each instance of the package open a separate file. You would need to have three different Package Configuration files to define different values for a parameter that you would use in an Expression to set the ConnectionString property on your File Connection Manager. Complicated, but probably a superior design if you're up for it. You'll still need to turn off table locking.
Note that you'll need multiple processors to make this a worthwhile endeavor. Also, if your files are very large tempdb will suffer without the table locking.|||
Note that you'll need multiple processors to make this a worthwhile endeavor. Also, if your files are very large tempdb will suffer without the table locking.

Hey Jay,
Is it mandatory to have multiple processors for parallel execution for the above mentioned issue.
I have done all the above things that you have suggested, but it stills seems that the loading is happening sequentially. Please note that the my machine is single processor machine.

Regards
Meghana|||It shouldn't be mandatory, but I wouldn't expect you to see any benefits without multiple processors. I ran a test on a single processor machine and did not have any difficulty getting my Data Flows to run concurrently. I also discovered that disabling the table locking is not necessary.
One thing to check is the MaxConcurrentExecutables property. The only way I could force my package to run sequentially is to set this value to 1. The default value is -1, which means number of processors plus two, so it should let you load three files simultaneously.
I also tested performance. Using a 100k line flat file going directly into a local SQL table, I saw a 10% decrease in performance with two parallel files and 34% decrease with three.
|||

mmhaise wrote:

Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it

Regards
M

Have you tried using a MULTIFLATFILE connection manager? This will enable you to load data from all of the files using just a single data-flow (which is executed just once).
If the metadata of the 3 files is identical this should work very nicely.

If the metadata is different then you can use 2 or 3 Derived Column Transforms to make the pipeline metadata identical and then use a UNION ALL transform to combine the 3 data-paths into one. Again, this is a single data-flow that you execute just the once.
If you are intent on loading the 3 seperately then executing a package 3 times as Jay suggested would be the "cleanest" approach because you can reuse functionality rather than having 3 seperate but identical data-flows.

I would definately look into using the MULTIFLATFILE connection manager though if I were you.

-Jamie|||

Is it possible to make the MULTIFLATFILE connection load in parallel? I have tried using one to load 4 csv file in parallel to the same destination table. The data flow is loading them sequentially.

I have another package with four separate file connection managers and data flows; however, I thought it would be nice to use the MULTIFLATFILE since the files have identical metadata. We require them to load in parallel.

Pakcage Execution Error under SQL server agent

Hi SSIS experts!

I have been trying to schedule a package I design to run off hour, but unable to do so. Here is a strange issue:

1. I am able to run and excute the package successfully through VSS. After I finished designing all my flows and containers, my exceution was successful to all my data sources.

2. I was able to deploy and run the actual package by sending to my local file system and it runs successfully through Execute Package Utility.

HOWEVER!!! when I tried to schedule this package through file system under sql server agent to run at night or through start job within SQL agent always failed...

I am puzzled so I added some logging on the package. The error message shows the following....

<message>The connection "{087B883F-D188-440A-9501-FF38CF5CEC28}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

<message>Failed to acquire connection "10.0.2.2.LogDB.jhwang". Connection may not be configured correctly or you may not have the right permissions on this connection.

But I thought if I had set the connection correctly to remember my passwords and using SQL server standard login within my package (connection manager) and should resolved the connection issues....

Why did it failed when I try to run it under sqlagent? But not through Execute Package Utility? Is there is a special setting I need to do for it to run under sql agent?

I notice within the job step when I choose the file source to point to my package... there was a tab called data sources where it has the connections I defined in my package. Does it matter if I put a check box on them or not? Either way they failed to connect.

Please help!

JON

Not sure what the problem is but you may want to debug it by using this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

Pakage running in Sql server 2005

hi friends,

i created the one package for import the data from excel to server 2005

in intellegence services i add the package after deploy

if we run the package (by giving the connection working fine)

BUT I WANT TO RUN THE PACKAGE BY DAILY AUTOMATICALLY(just like a scheduler)

is it possible if possible please give me the coding for auto run a packages

regards

koti

Hi ya,

You would need to deploy it first to file system or Ms Sql Server and then place it as a job in sql server agent. Schedule that job to run daily.

Hope that helps

Cheers

Rizwan