How to use variables for server connections

Hello,

Today is day two of using SSIS and having some issues adjusting. In DTS I would simply populate my global variables via an Active X step or pass them in at the job line.

I am having a hard time being able to do either or in SSIS. Really all I want to be able to do is pass in a server name to the Connection of an execute SQL task from a scheduled job line.

Same goes for any variable really ... how do I pass them in, and how do I assign them to specific areas of a task?

Thanks!

Mr.Bean wrote:

Hello,

Today is day two of using SSIS and having some issues adjusting. In DTS I would simply populate my global variables via an Active X step or pass them in at the job line.

I am having a hard time being able to do either or in SSIS. Really all I want to be able to do is pass in a server name to the Connection of an execute SQL task from a scheduled job line.

Same goes for any variable really ... how do I pass them in, and how do I assign them to specific areas of a task?

Thanks!

If you want to pass values into the package from the command-line then use the /SET option of dtexec.exe.

Does that answer the question?

By the way, the most analogous thing to populating global variables from an ActiveX Script is to do it in a Script Task. Don't use the ActiveX Script in SSIS - it isn't useful. The following post explains how you do it using a script task:

Writing to a variable from a script task
(http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx)

Get all of that working first and then we can address "how do I assign them to specific areas of a task" (short answer - use expressions).

-Jamie

|||

By the way, if you're finding the transition from DTS difficult I've got some documentation that may help:

The mind-shift from DTS
(http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1396.aspx)

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

DTS Connections and SSIS Connection Managers
(http://blogs.conchango.com/jamiethomson/archive/2006/04/02/3422.aspx)

The new ETL Paradigm
(http://blogs.conchango.com/jamiethomson/archive/2005/02/15/1002.aspx)

-Jamie

|||So now what I'm reading here ...

http://www.databasejournal.com/features/mssql/article.php/3509601

DTExec /FILE SamplePackage.dtsx /SET \Package.Connections[ConnectionID].InitialCatalog;tempdb

Is really the intended way to pass in server names? What if (like in several of my processes) I connect to multiple servers for different steps? I really have to retrieve the GUID of those tasks and populate them appropriately like above?

I'm just now starting to look into the Package Configurations ... not sure what I'm doin here just yet, but would this be a better solution?

I miss DTS ...|||

Mr.Bean wrote:

So now what I'm reading here ...

http://www.databasejournal.com/features/mssql/article.php/3509601

DTExec /FILE SamplePackage.dtsx /SET \Package.Connections[ConnectionID].InitialCatalog;tempdb

Is really the intended way to pass in server names?

Someties yes. Sometimes no. Depends on your scenario.

Mr.Bean wrote:

What if (like in several of my processes) I connect to multiple servers for different steps?

Then you'll have multiple connection managers.

Mr.Bean wrote:

I really have to retrieve the GUID of those tasks and populate them appropriately like above?

Not sure I understand what you mean. You never have to know about task GUIDs.

Mr.Bean wrote:

I'm just now starting to look into the Package Configurations ... not sure what I'm doin here just yet, but would this be a better solution?

Possibly. Depends on your scenario. Most people find that configurations are the way to go rather than passing in connectoin details from the command-line.

Mr.Bean wrote:

I miss DTS ...

Well there is a steep learning curve with SSIS but most people find it worth it when they get there. Configurations, for example, are wonderfully powerful and remove many of the headaches people have in DTS.

-Jamie

|||Many of my processes (including this one) are designed to be very generic so that I can dynamically set specific items ... such as server name, database names, etc.

So the current SSIS package I'm working on, I have built and ready to go, but am struggling to find the best way to allow for dynamic variables (server/dbnames). Right now everything is static and it works ... but I need to change that.

Just built my first package configuration, and this might be what I'm looking for? By selecting which tasks I want put into this configuration (such as variables and connections), this will properly update those areas?

My wording isn't the greatest ... as I said, day two and not doing so well with this yet.

Thanks again|||

Mr.Bean wrote:

Many of my processes (including this one) are designed to be very generic so that I can dynamically set specific items ... such as server name, database names, etc.

So the current SSIS package I'm working on, I have built and ready to go, but am struggling to find the best way to allow for dynamic variables (server/dbnames). Right now everything is static and it works ... but I need to change that.

Just built my first package configuration, and this might be what I'm looking for? By selecting which tasks I want put into this configuration (such as variables and connections), this will properly update those areas?

My wording isn't the greatest ... as I said, day two and not doing so well with this yet.

Thanks again

It sounds as though you want a generic package. Something that you can execute for multiple environments and effectively "parameterize" it on each execution. Is this correct? In this scenario you will probably want to pass in values using the /SET option of dtexec.exe. By the way, make it easy on yourself, pass in the value for the ConnectionString property, not a seperate property for ServerName/Catalog etc...

Configurations are more suited to moving packages through your dev/test/UAT/live environments.

-Jamie

|||Yeah that is more of what I am looking for ... so I guess that brings me to my next question.

How do I do that? For example ...

I have a variable called DBList. I want this variable to have database names passed in, what would my /SET command look like?

Then for the connections. Say I have a SourceServer and DestServer variable. Same question above applies here, but how do I bind a variable to the connection in the properties of a task?

Thanks again for all your help.|||

Mr.Bean wrote:

Yeah that is more of what I am looking for ... so I guess that brings me to my next question.

How do I do that? For example ...

I have a variable called DBList. I want this variable to have database names passed in, what would my /SET command look like?

Then for the connections. Say I have a SourceServer and DestServer variable. Same question above applies here, but how do I bind a variable to the connection in the properties of a task?

Thanks again for all your help.

You don't. You can reference the ConnectionString property of the connection manager directly from the command line. So your command-line would look something like:

dtexec.exe /FILE "C:\Package1.dtsx" /SET "\Package.Connections[conn_name].Properties[ConnectionString]";SomeValue

-Jamie

|||

Jamie Thomson wrote:

Mr.Bean wrote:

Yeah that is more of what I am looking for ... so I guess that brings me to my next question.

How do I do that? For example ...

I have a variable called DBList. I want this variable to have database names passed in, what would my /SET command look like?

Then for the connections. Say I have a SourceServer and DestServer variable. Same question above applies here, but how do I bind a variable to the connection in the properties of a task?

Thanks again for all your help.

You don't. You can reference the ConnectionString property of the connection manager directly from the command line. So your command-line would look something like:

dtexec.exe /FILE "C:\Package1.dtsx" /SET "\Package.Connections[conn_name].Properties[ConnectionString]";SomeValue

-Jamie

Sorry for all the questions ... but I'm not sure I follow you on that.

http://img382.imageshack.us/img382/3171/ssisconnectionpropsbx5.jpg

As you can see the connection at this execute SQL task has no specific name to it. This is the connection I want to be able to update dynamically. So are you stating to create a new connection to a server, and simply update that per your /SET example?

dtexec.exe /FILE "C:\Package1.dtsx" /SET "\Package.Connections[SERVERNAME].Properties[ConnectionString]";SERVERNAME

Thanks again|||

Mr.Bean wrote:

Jamie Thomson wrote:

Mr.Bean wrote:

Yeah that is more of what I am looking for ... so I guess that brings me to my next question.

How do I do that? For example ...

I have a variable called DBList. I want this variable to have database names passed in, what would my /SET command look like?

Then for the connections. Say I have a SourceServer and DestServer variable. Same question above applies here, but how do I bind a variable to the connection in the properties of a task?

Thanks again for all your help.

You don't. You can reference the ConnectionString property of the connection manager directly from the command line. So your command-line would look something like:

dtexec.exe /FILE "C:\Package1.dtsx" /SET "\Package.Connections[conn_name].Properties[ConnectionString]";SomeValue

-Jamie

Sorry for all the questions ... but I'm not sure I follow you on that.

http://img382.imageshack.us/img382/3171/ssisconnectionpropsbx5.jpg

As you can see the connection at this execute SQL task has no specific name to it. This is the connection I want to be able to update dynamically. So are you stating to create a new connection to a server, and simply update that per your /SET example?

dtexec.exe /FILE "C:\Package1.dtsx" /SET "\Package.Connections[SERVERNAME].Properties[ConnectionString]";SERVERNAME

Thanks again

Hmmm...seems to be a lack of understanding here. Tasks contain no knowledge about the sources they ultimately connect to. They have a reference to a connection manager - and THAT is where the details of the external data source (be a it a file, a database or whatever) reside.

In other words - forget about the task. You have to update the the ConnectionString property of the connection manager being used by the task.

-Jamie

|||I think I follow you on that ... but the fact is that I have to create a connection in the connection manager for the task to refrence correct? So that means I still have to set a static connection to a server in the connection manager as it does not allow me to create a blank OLE DB connection that can be populated from the command line (which is what I want to do), right?

Sorry for the confusion here.|||

Mr.Bean wrote:

I think I follow you on that ... but the fact is that I have to create a connection in the connection manager for the task to refrence correct?

Correct.

Mr.Bean wrote:

So that means I still have to set a static connection to a server in the connection manager as it does not allow me to create a blank OLE DB connection that can be populated from the command line (which is what I want to do), right?

Sorry for the confusion here.

No need to apologise!

You do need to set the ConnectionString property of the connection manager to SOMETHING, yes. You may say "Why do I need to do that if I'm going to change it anyway when I execute?" I think that's a legitimate complaint however I don't see it as a problem. If it IS a problem then you can put BIDS into offline mode by selecting 'Work Offline' on the 'SSIS' menu.

Hope that helps.

-Jamie

|||I see ... makes sense.

So in summary, does this looks about right?

/FILE "C:\Directory\Package.dtsx" /SET "\Package.Variables[User::DBList].Value";"“DB1, DB2, DB3“" /SET "\Package.Connections[SERVERNAMESRC].Properties[ConnectionString]";SERVERNAMESRC
/SET "\Package.Connections[SERVERNAMEDEST].Properties[ConnectionString]";SERVERNAMEDEST

Does the command line look right? Do I have to specify each /SET?

Thanks again for all your help, definitely made this easier and made me realize its time to sit down and do some reading!
|||

Mr.Bean wrote:

I see ... makes sense.

So in summary, does this looks about right?

/FILE "C:\Directory\Package.dtsx" /SET "\Package.Variables[User::DBList].Value";"“DB1, DB2, DB3“" /SET "\Package.Connections[SERVERNAMESRC].Properties[ConnectionString]";SERVERNAMESRC
/SET "\Package.Connections[SERVERNAMEDEST].Properties[ConnectionString]";SERVERNAMEDEST

Yes, it looks right. Only you can tell if it is or not. Note that the value you supply will have to eb a full connection string. check out http://www.connectionstrings.com or just cop and paste from those that you set up in the package and edit accordingly.

Mr.Bean wrote:

Does the command line look right? Do I have to specify each /SET?

Yes.

Mr.Bean wrote:

Thanks again for all your help, definitely made this easier and made me realize its time to sit down and do some reading!

No getting around that I'm afraid :)

By the way if you get to sorting this out I would appreciate your marking one of my posts as an answer. The search function takes that into account when it delivers search results.

-Jamie

HOW TO USE SSIS (DTS) IN SQL EXPRESS

I ′m trying to use ssis in Sql Express in order to replace my old DTS that I used in MSDE but I found that SSIS doesn′t work in Express, question is

How could I use new DTS technology in Express ?

Thanks in advance.

SSIS does not come with Express or Workgroup editions of SQL Server 2005. See Integration Services with Basic Transforms here http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Derek

|||

The SQL Server 2005 Backwards Compatability Components include the DTS2000 Runtime that will allow you to use your old 2000 DTS packages in SQL Express. You can download it from the SQL Server 2005 Feature Pack page.

Regards,

Mike Wachal

How to use password with a DTS package

We have a VBA application that calls a SQL Server 2000 Job which calls
a DTS package to pull data from one database and populate another
database. In order to pull the data, we need to provide a password to
the connection. Currently, the VBA app populates an .ini file with the
password (provided by the app user) which the DTS package then uses to
log in to the source database.
I'm not very happy about storing a password anywhere where somebody
could find it. Can anybody suggest a better solution?
Thanks in advance,
SaulHi
Your package password can be specified on the command line using the /M or
/~M parameter, therefore you may want to pass an encrypted parameter or
possibly an encrypted command check out
http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
am not sure why you are running this from VBA and not using SQL Agent!
John
"saul_margolis@.hotmail.com" wrote:

> We have a VBA application that calls a SQL Server 2000 Job which calls
> a DTS package to pull data from one database and populate another
> database. In order to pull the data, we need to provide a password to
> the connection. Currently, the VBA app populates an .ini file with the
> password (provided by the app user) which the DTS package then uses to
> log in to the source database.
> I'm not very happy about storing a password anywhere where somebody
> could find it. Can anybody suggest a better solution?
> Thanks in advance,
> Saul
>|||Thanks John. I know I can pass in global variables when calling the
DTS package, but the DTS package is called by the job. I would need
some way of passing in the password to the job. Do you know if that's
possible?
John Bell wrote:[vbcol=seagreen]
> Hi
> Your package password can be specified on the command line using the /M or
> /~M parameter, therefore you may want to pass an encrypted parameter or
> possibly an encrypted command check out
> http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun.
I
> am not sure why you are running this from VBA and not using SQL Agent!
> John
> "saul_margolis@.hotmail.com" wrote:
>|||Hi
I am not sure why you need to do this as the password for any given package
would be relatively fixed! It is not passed as a global variable by it's own
argument on the command line.
If you ran the package from a stored procedure it could pick up the package
name and other parameters from a table
http://www.sqldts.com/default.aspx?210, alternatively use the /Z parameter
and encrypt the whole command and keep the ini file. You could also use a
table of encrypted commands!!
John
"saul_margolis@.hotmail.com" wrote:

> Thanks John. I know I can pass in global variables when calling the
> DTS package, but the DTS package is called by the job. I would need
> some way of passing in the password to the job. Do you know if that's
> possible?
>
>
> John Bell wrote:
>|||Hi John,
It's not actually the password for the package that is needed. It's
the password that the package will use to login to the server which
contains the source data.
John Bell wrote:[vbcol=seagreen]
> Hi
> I am not sure why you need to do this as the password for any given packag
e
> would be relatively fixed! It is not passed as a global variable by it's o
wn
> argument on the command line.
> If you ran the package from a stored procedure it could pick up the packag
e
> name and other parameters from a table
> http://www.sqldts.com/default.aspx?210, alternatively use the /Z parameter
> and encrypt the whole command and keep the ini file. You could also use a
> table of encrypted commands!!
> John
>
> "saul_margolis@.hotmail.com" wrote:
>|||Hi
Can you not used the /E parameter?
John
"saul_margolis@.hotmail.com" wrote:

> Hi John,
> It's not actually the password for the package that is needed. It's
> the password that the package will use to login to the server which
> contains the source data.
>
> John Bell wrote:
>

How to use password with a DTS package

We have a VBA application that calls a SQL Server 2000 Job which calls
a DTS package to pull data from one database and populate another
database. In order to pull the data, we need to provide a password to
the connection. Currently, the VBA app populates an .ini file with the
password (provided by the app user) which the DTS package then uses to
log in to the source database.
I'm not very happy about storing a password anywhere where somebody
could find it. Can anybody suggest a better solution?
Thanks in advance,
SaulHi
Your package password can be specified on the command line using the /M or
/~M parameter, therefore you may want to pass an encrypted parameter or
possibly an encrypted command check out
http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
am not sure why you are running this from VBA and not using SQL Agent!
John
"saul_margolis@.hotmail.com" wrote:
> We have a VBA application that calls a SQL Server 2000 Job which calls
> a DTS package to pull data from one database and populate another
> database. In order to pull the data, we need to provide a password to
> the connection. Currently, the VBA app populates an .ini file with the
> password (provided by the app user) which the DTS package then uses to
> log in to the source database.
> I'm not very happy about storing a password anywhere where somebody
> could find it. Can anybody suggest a better solution?
> Thanks in advance,
> Saul
>|||Thanks John. I know I can pass in global variables when calling the
DTS package, but the DTS package is called by the job. I would need
some way of passing in the password to the job. Do you know if that's
possible?
John Bell wrote:
> Hi
> Your package password can be specified on the command line using the /M or
> /~M parameter, therefore you may want to pass an encrypted parameter or
> possibly an encrypted command check out
> http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
> am not sure why you are running this from VBA and not using SQL Agent!
> John
> "saul_margolis@.hotmail.com" wrote:
> > We have a VBA application that calls a SQL Server 2000 Job which calls
> > a DTS package to pull data from one database and populate another
> > database. In order to pull the data, we need to provide a password to
> > the connection. Currently, the VBA app populates an .ini file with the
> > password (provided by the app user) which the DTS package then uses to
> > log in to the source database.
> >
> > I'm not very happy about storing a password anywhere where somebody
> > could find it. Can anybody suggest a better solution?
> >
> > Thanks in advance,
> > Saul
> >
> >|||Hi
I am not sure why you need to do this as the password for any given package
would be relatively fixed! It is not passed as a global variable by it's own
argument on the command line.
If you ran the package from a stored procedure it could pick up the package
name and other parameters from a table
http://www.sqldts.com/default.aspx?210, alternatively use the /Z parameter
and encrypt the whole command and keep the ini file. You could also use a
table of encrypted commands!!
John
"saul_margolis@.hotmail.com" wrote:
> Thanks John. I know I can pass in global variables when calling the
> DTS package, but the DTS package is called by the job. I would need
> some way of passing in the password to the job. Do you know if that's
> possible?
>
>
> John Bell wrote:
> > Hi
> >
> > Your package password can be specified on the command line using the /M or
> > /~M parameter, therefore you may want to pass an encrypted parameter or
> > possibly an encrypted command check out
> > http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
> > am not sure why you are running this from VBA and not using SQL Agent!
> >
> > John
> >
> > "saul_margolis@.hotmail.com" wrote:
> >
> > > We have a VBA application that calls a SQL Server 2000 Job which calls
> > > a DTS package to pull data from one database and populate another
> > > database. In order to pull the data, we need to provide a password to
> > > the connection. Currently, the VBA app populates an .ini file with the
> > > password (provided by the app user) which the DTS package then uses to
> > > log in to the source database.
> > >
> > > I'm not very happy about storing a password anywhere where somebody
> > > could find it. Can anybody suggest a better solution?
> > >
> > > Thanks in advance,
> > > Saul
> > >
> > >
>|||Hi John,
It's not actually the password for the package that is needed. It's
the password that the package will use to login to the server which
contains the source data.
John Bell wrote:
> Hi
> I am not sure why you need to do this as the password for any given package
> would be relatively fixed! It is not passed as a global variable by it's own
> argument on the command line.
> If you ran the package from a stored procedure it could pick up the package
> name and other parameters from a table
> http://www.sqldts.com/default.aspx?210, alternatively use the /Z parameter
> and encrypt the whole command and keep the ini file. You could also use a
> table of encrypted commands!!
> John
>
> "saul_margolis@.hotmail.com" wrote:
> > Thanks John. I know I can pass in global variables when calling the
> > DTS package, but the DTS package is called by the job. I would need
> > some way of passing in the password to the job. Do you know if that's
> > possible?
> >
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Your package password can be specified on the command line using the /M or
> > > /~M parameter, therefore you may want to pass an encrypted parameter or
> > > possibly an encrypted command check out
> > > http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
> > > am not sure why you are running this from VBA and not using SQL Agent!
> > >
> > > John
> > >
> > > "saul_margolis@.hotmail.com" wrote:
> > >
> > > > We have a VBA application that calls a SQL Server 2000 Job which calls
> > > > a DTS package to pull data from one database and populate another
> > > > database. In order to pull the data, we need to provide a password to
> > > > the connection. Currently, the VBA app populates an .ini file with the
> > > > password (provided by the app user) which the DTS package then uses to
> > > > log in to the source database.
> > > >
> > > > I'm not very happy about storing a password anywhere where somebody
> > > > could find it. Can anybody suggest a better solution?
> > > >
> > > > Thanks in advance,
> > > > Saul
> > > >
> > > >
> >
> >|||Hi
Can you not used the /E parameter?
John
"saul_margolis@.hotmail.com" wrote:
> Hi John,
> It's not actually the password for the package that is needed. It's
> the password that the package will use to login to the server which
> contains the source data.
>
> John Bell wrote:
> > Hi
> >
> > I am not sure why you need to do this as the password for any given package
> > would be relatively fixed! It is not passed as a global variable by it's own
> > argument on the command line.
> >
> > If you ran the package from a stored procedure it could pick up the package
> > name and other parameters from a table
> > http://www.sqldts.com/default.aspx?210, alternatively use the /Z parameter
> > and encrypt the whole command and keep the ini file. You could also use a
> > table of encrypted commands!!
> >
> > John
> >
> >
> >
> > "saul_margolis@.hotmail.com" wrote:
> >
> > > Thanks John. I know I can pass in global variables when calling the
> > > DTS package, but the DTS package is called by the job. I would need
> > > some way of passing in the password to the job. Do you know if that's
> > > possible?
> > >
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Your package password can be specified on the command line using the /M or
> > > > /~M parameter, therefore you may want to pass an encrypted parameter or
> > > > possibly an encrypted command check out
> > > > http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
> > > > am not sure why you are running this from VBA and not using SQL Agent!
> > > >
> > > > John
> > > >
> > > > "saul_margolis@.hotmail.com" wrote:
> > > >
> > > > > We have a VBA application that calls a SQL Server 2000 Job which calls
> > > > > a DTS package to pull data from one database and populate another
> > > > > database. In order to pull the data, we need to provide a password to
> > > > > the connection. Currently, the VBA app populates an .ini file with the
> > > > > password (provided by the app user) which the DTS package then uses to
> > > > > log in to the source database.
> > > > >
> > > > > I'm not very happy about storing a password anywhere where somebody
> > > > > could find it. Can anybody suggest a better solution?
> > > > >
> > > > > Thanks in advance,
> > > > > Saul
> > > > >
> > > > >
> > >
> > >
>

How to use criteria with text file source in DTS package?

I have a peculiar problem with a database.

I need to import data from text files where one column matches a value in a table that's already on the database. I cannot even fathom how to do this without pulling the entire 1GB file onto the database and simply deleting data that doesn't match the criteria. While this would work, I'm sure there's a more efficient way to do it! Can anyone give me some pointers? I'm not exactly a DTS expert.

Thank you.for this you will have to edit the column transformation ActiveX script generated by DTS. just need to add a "if" condition to skip the row in case the condition is not matching. something like below will do the job

Function Main()
if DTSSource("Src_Col_Name") = "Filter_Value" then
DTSDestination("Col1") = DTSSource("Col1")
DTSDestination("Col2") = DTSSource("Col2")
DTSDestination ............
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipRow
end if
End Function|||Is there an easier way to do this? The values I'm filtering by are held in a SQL table. I don't want to have to manually update these in the script because there will be several dozen policy IDs we're filtering by, and the table we're updating has 229 columns so that's a ton of scripting to do!

For example, we have a 1gb file we could import, but the people who use the DB are interested only in data pertaining to 30 policy IDs. They want us to import ONLY the data for those policy IDs; they don't want to see anything else.|||In your DTS function, write a few lines of VBA code to connect to the database and see if the current source row's policy ID exists in the table that holds the list of interesting policies.

-PatP|||Thanks for the suggestions, guys. :beer:

The way we ended up doing this is a very roundabout and slow. We're doing it in batches of 100,000 and putting the data into an intermediate table, running an update to put the data that matches the criteria into the final table, truncating the intermediate table, and going around to the start and doing it again until we get to the end of the file. This way, we managed to read through a 1GB text file and pull out only what we were interested in in our 200MB database. I know this is hideously inefficient and our DBAs would probably kill us if they knew about it but I did ask them for help and all they could do was point me in the direction of a couple of other programmers in the company who didn't have any ideas.

I'm not sure if we will continue to use this solution but it is so far the easiest one to maintain that we've thought of. I won't be at this job much longer (few months at the most) and when I leave the other team members (who are less experienced with SQL Server than I am, as horrifying as that thought is) have to maintain what I've written, so gigantic long ActiveX scripts are something I'd prefer to avoid if at all possible; some team members cannot write VBScript at all. Also, the words 'best practice' are unfortunately rarely uttered here. :mad:

Realistically we can't avoid ActiveX scripts; we have a few here and there, always to convert dates from one format to another, but the scripts suggested would just be too large for our team to handle. ActiveX definitely has its place though; I just wrote a script last week to convert Julian dates to Gregorian. No other way to do it than with a script! These suggestions may come in handy for a smaller-scale import too, so I'll tuck away a copy of the tread J.I.C. :D

How to use a vriable in DTS Package

Hello,

I have set up a simple DTS Package to copy a table (backup) in my database. I have specified the name of the table, however I want the naming of the new table to be dynamic and use the current timestamp, but I am not sure if and how I can define a variable and use it in the package.

Any thoughts? Do you need any more info?

Thanks.

RodneyHere is my SQL for the new table creation. I want the following name to be dynamic "CON_D_EmployeeInfo_backup20040526" Maybe use "CON_D_EmployeeInfo_{CurrentDateTime}".

CREATE TABLE [ancosalesdm].[dbo].[CON_D_EmployeeInfo_backup20040526] (
[EmployeeID] varchar (15) NULL,
[EmployeeTitle] varchar (46) NULL,
[EmployeePosition] varchar (46) NULL,
[EmployeeCostCenter] varchar (4) NULL,
[EmployeeDivision] varchar (256) NOT NULL,
[EmployeeStatusFlag] varchar (3) NULL,
[curr_ind] varchar (1) NOT NULL,
[last_up_date] datetime NOT NULL,
[eff_start_date] datetime NOT NULL,
[eff_end_date] datetime NULL,
[key] int NOT NULL
)