AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

Autogenerate reply or ServerProc generated message............

Hello,

I got my sample application to work that I am building my proof of concept out of. I need to be able to auto generate a reply message that would normally be in the run routine. The only way I see to do this is pull it from a table but I do not want to do that. I have tried tests where I change the code to see if I can send a message back but I have to reinstall the assembly into the database which is not what I am looking for. I am looking for a way to change the message by either accessing the GUI and getting the string, calling another function to do this, or something like that. I want it so I can change the code in the run routine in VS 2005 but this does not work. I am sure their is a trick to do this but am not sure what that trick is. What is a good way to do this other than accessing a table in the database?

Thanks,

Scott Allison...

What do you mean by auto-generate a reply? A reply could either be static (i.e. independent of the request and state), a stateless (i.e. dependent only on the request) or stateful (i.e. dependent on both the request as well as some state... the state would normally be stored in the database, but may also be stored separately). I cannot see why you need to recompile and redeploy your app to meet any of the above patterns.

Rushi

|||

Hello Rushi,

It calls the ServiceProc in my code and that is loaded into the database. I am writing this for applications that will need to report the status of a current event. If this is not stored in the database then they would not want to write anything to the database. This means they will get the request then need to reply to that request with a text message that they generate. In the worst case situation it can come from the database but would rather just be retrieved from a function call. It seems as though from your message this can be done so I am sure I have a setting incorrect. What I have is currently static and I want the response dependent on the request. What I meant from Autogenerated is retrieved from another assembly or another part of that service.

Thanks,

Scott Allison...

|||If the function that generates a response from request is a method in a different assembly, you could deploy that assembly into the database and simply invoke the method from ServiceProc.|||

Hello Rushi,

This is a remote system and will not be able to deploy all of the assemblies to the database. It consists of several services and needs dynamic access to what the message. Are you telling me that the only way to get a dynamic message is to write it to a database and have the ServiceProc read it? This is a draw back beacuse it will slow things down but I want to make sure before I tell people that they have to do that.

Thanks,

Scott Allison...

Autogenerate Primary Key

Hi all
How do you autogenerate your own primary key in SQL.
Instead of SQL generating an IDENTIY number which would be 1, 2 ,3..etc
I was wanting to give it my own sequence of numbers, how exactly do I do that can anyone help??why? what do these sequence of numbers look like? do they have special meanings?|||Humour me for a second - why?|||why? what do these sequence of numbers look like? do they have special meanings?Humour him too ^^^^^^^ :)|||The number would look like this 07-0000

The first two digits are the year, the others are in numerical secquence.. like 07-0001, 07-0002, 07-0003 (JP IR#)and so on, they normally log this in a journal but now they want a database to log this in where the database would generate these numbers and they have to do is put in the type, the amount and whether its a hold or not. its for our surveillance department and they have IR numbers that help in their reports|||smart numbers are stupid for many many reasons. I would just store those 2 parts in seperate fields and bring them together in the UI so they can see the number they want.|||Sorry I didnt realize they would be stupid, I just thought I would ask. Doesnt hurt to ask. I wasnt sure how to create something like|||Heh. The problem with semi intelligent (and also dumb) bespoke keys is when it comes to serialisation. This is not much of an issue if you add one row at a tme and do not have high concurrency but if either of these are not true then performance really suffers. Do you need to reset the count each year? SQL Server does not support this internally. I think other RDBMSs (like MySQL) do but that is not much help :)

autogenerate numbers from 000001 to 999999

I would like the numbers 000001 to 999999 to autogenerate in a new
database. I will be transfering information from another database and
in that database the numbers 000001 to 010000 are already taken. They
are used as identifiers in other programs and it would be easier if
they were stored as written. Using identity the 0's are eliminated.
Is there a way to keep them?

Thank you,
MIf you're happy to generate IDs one at a time, try this:

CREATE TABLE Sometable (col1 CHAR(6) PRIMARY KEY CHECK (col1 LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9]'), col2 VARCHAR(10) NOT NULL)

INSERT INTO Sometable (col1, col2)
SELECT RIGHT('000000'+CAST(
COALESCE(CAST(MAX(col1) AS INTEGER),0)+1 AS VARCHAR(6)),6),
'Blah Blah'
FROM Sometable

--
David Portas
----
Please reply only to the newsgroup
--|||Thank you very much. It occurred to me after reading your post that I
didn't need to do any math with the numbers, so I defined them as
varchar(6) and wrote a little program in vb.net with some loops to
fill them in for me. It worked out very well.
-M

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<Qf2dnfksRaR5GO2iRVn-vw@.giganews.com>...
> If you're happy to generate IDs one at a time, try this:
> CREATE TABLE Sometable (col1 CHAR(6) PRIMARY KEY CHECK (col1 LIKE
> '[0-9][0-9][0-9][0-9][0-9][0-9]'), col2 VARCHAR(10) NOT NULL)
> INSERT INTO Sometable (col1, col2)
> SELECT RIGHT('000000'+CAST(
> COALESCE(CAST(MAX(col1) AS INTEGER),0)+1 AS VARCHAR(6)),6),
> 'Blah Blah'
> FROM Sometable

AutoGenerate No. in SQL Server 2000

Hi
I would like to create an Autogenerate function which has to do the following

autogenerate field type is varchar(10). in that first 2 characters are purely character string. remaining will be numbers

i'll pass the following parameters into the function
1. tablename
2. columnname
3. 2 character string that has to build the first 2 characters
eg: functionname(emp, empid, 'EM')

Here the function has to execute and return the generated no.
eg: EM1 - IF RECORDS NOT AVAIL IN THE TABLE
EM5 - IF ALREADY RECORDS ARE AVAIL &THE MAX RECORD NO IS EM4.

In this functioin i've to pass any tablename and corresponding field with the 2 character build string... Already i tried. Few problems are there in passing the tablename as parameter...

Anybody help me in that...

Thanks in advance...Hi,

What were the problems you got?

Madhivanan|||Hi,

I would like to create an Autogenerate Nos. (total length is varchar(10), in that first 2 letters purely characters, remainings are numerical value).
Value has to start from 1. ie., if records not avail then...'PT1', if records avail then... 'PT4', 'PT5'... like that will be there... i'll pass the Tablename, Columnname and buildstring as parameter. The return value will be autogenerated no(varhchar(10)).

Function calling will be
eg: functionname(tablename, columnname, 'PT')

Give me the solution.

Thx...|||Hi,

Inside the function write something like this

Declare @.AutoGen varchar(10)

if (select count(*) from @.t) =0
select @.AutoGen = 'PT1'
else
select @.AutoGen = 'PT'+convert(varchar(8),max(right(id,len(id)-2))+1) from tableName

Madhivanan|||Hi,
Actually i've got a problem to passing the Tablename as parameter.
Its not working... U try to create a real function in SQL server 2000 with
a table, test it, then... give me reply. then only u can get what'll be the problem...

Tx in advance...|||You will need to use dynamic SQL to do this by building your SQL Statement as a string and then EXECuting it.

That said, do NOT do this. The way you are generating these codes goes against good principles of design, and my crystal ball tells me that this will be an endless source of trouble.

I strongly urge you to reconsider your design.|||Hi...

Anybody got the Autogenerate Nos. Function in SQL Server 2000.

Already i mentioned the problems in the same thread...

Help me

Tx in Advance...|||Easy to do! Without your data I can't test this, but something as simple as:CREATE PROCEDURE fubar
@.pcTable sysname
, @.pcColumn sysname
, @.pcPrefix CHAR(2)
, @.pcResult CHAR(10) OUTPUT
AS

if 'foo' = @.pcTable AND 'bar' = @.pcColumn
SELECT @.pcResult = @.pcResult + Coalesce(Replace(' ', '0'
, Str((SELECT Max(Convert(INT, SubString(foo.bar, 3, 8))
FROM foo) + 1, 8)), '00000001')
else if 'baz' = @.pcTable and 'bat' = @.pcColumn
SELECT @.pcResult = @.pcResult + Coalesce(Replace(' ', '0'
, Str((SELECT Max(Convert(INT, SubString(baz.bat, 3, 8))
FROM baz) + 1, 8)), '00000001')
else SELECT @.pcResult = Repeat('?', 10)

RETURN-PatP|||Sorry...

In ur procedure why u checked the table name. I don't want to check the table name and column names. i'll just pass the table and column names with build string (2 character string). Thats all. The function has to return a no from the specific given table. (if rows are not avail) --> 'PT1'

if rows avail--> 'PT5' (for example already the maximum value is PT4).

It should be for all table... I've no. of tables. For each and every table i can not check it like ur procedure coding... got it...?

Tx... Reconsider it and give the better solution...|||You're trying to do something that SQL doesn't do well because it has other features that work SO much better. I'm sorry you don't like my solution, even though it does exactly what you asked for and relatively efficiently at that.

Maybe someone else has a better answer. Personally, I'd just suggest that you simply use a better solution such as an IDENTITY column that would sidestep the need for this nonsense.

-PatP|||select 'PT'+convert(varchar(8),max(isnull(right(id,len(id )-2),0))+1) from tableName

Hi,

I would like to create an Autogenerate Nos. (total length is varchar(10), in that first 2 letters purely characters, remainings are numerical value).
Value has to start from 1. ie., if records not avail then...'PT1', if records avail then... 'PT4', 'PT5'... like that will be there... i'll pass the Tablename, Columnname and buildstring as parameter. The return value will be autogenerated no(varhchar(10)).

Function calling will be
eg: functionname(tablename, columnname, 'PT')

Give me the solution.

Thx...|||select 'PT'+convert(varchar(8),max(isnull(right(id,len(id )-2),0))+1) from @.tableName

here @.tablename is the parameter. if i give like this its giving an error...
please try to do the function, check it and give me the reply.

Tx in advance...|||Hi...

Anybody is having good solution for my question regarding this autogenerate nos. in SQL server 2000. Question is avail in this same thread...

Good suggestions and Good & Efficient Solutions are welcome

Tx in advance.|||PatP gave you a good & efficient solution. If you don't understand, it is not due to not having the right solution.

Autogenerate data

Hi,
Does any one know of a script that can autogenerate test data. Does not have
to be real intellagible data at this stage, just to fill tables.
Thanks
RobertRobert
http://vyaskn.tripod.com/code/generate_inserts.txt
http://www.karaszi.com/SQLServer/in...rate_script.asp
"Robert Bravery" <me@.u.com> wrote in message
news:uxVGMpJgGHA.5092@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Does any one know of a script that can autogenerate test data. Does not
> have
> to be real intellagible data at this stage, just to fill tables.
> Thanks
> Robert
>|||Robert, http://vyaskn.tripod.com/code/generate_inserts.txt -- is what I have
also used it in the past. Its really script.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Uri Dimant" wrote:

> Robert
> http://vyaskn.tripod.com/code/generate_inserts.txt
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> "Robert Bravery" <me@.u.com> wrote in message
> news:uxVGMpJgGHA.5092@.TK2MSFTNGP04.phx.gbl...
>
>

AutoGenerate

Hi
I have set a field "MessageId" as primary in a Messages table. What I want is that whenever user inserts a message through my site, the MsSql should automatically generate MessageId for the new message inserted, but this is not happening. Any suggestions, advice are highly appreciated. Thank YouIs the column set as an IDENTITY column? If not, that explains the problem. In Enterprise Manager, go into Design mode for the table, and make sure in the properties window, Identity is True (or Yes, do not recall which is used).|||You've got to create a table in SQL with something like the below. As long as there is input in the column named "Message" then the MessageID will automatically increase.

CREATE TABLE Message
(
MessageID int IDENTITY(1,1) PRIMARY KEY,
Messagevarchar (2000)NOT NULL
)

Good luck!
-Gabian-|||Thanks to both of you gentleman.

One more thing, what enum of SqlDbtype should i keep for my actual Message(thats being recorded by the user) : "text" or "varChar" ?|||What are your needs:

Varchar will allow a maximum of 8000 bytes
Text will allow very large values ~ 2gb

Varchar will give you much more flexibility for searching and manipulating data though and if it is sufficient would be my recomendation.|||Depends on the size...

I usually use varchar (^_^)