create table everyday with same name

Question

create table everyday with same name

sam73003

Newbie

Points: 1
More actions
June 12, 2008 at 5:03 pm

#183436

Can SSIS package soultion can be created in such a way that it can drop and recreate staging tables daily based on the ordering ( our column name differs sometimes)of the fields on any given day? But I want to keep name of destination table same everyday and want to keep all the fields of varchar 255. ( its not one time , I want to make routine automatic process). ( source file is pipe delimited with 1st row as column heading)
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 1

In normal circumstances, I would suggest that you use a execute SQL task to simply drop and create prior to performing the dataflow.

However, have I got you right, that your file will change in layout every so often?

Care to explain how that is possible? Surely you can fix that problem at source, and even more so, you may want to be notified when it occurs?

I would be very sceptical if a source cannot provide me the same standard layout daily

~PD

robin712 SSC Enthusiast Points: 107 More actions · Answer 2

the industry which i work for buys different column everyday. Since all our view are based on the staging table, we want to keep the name of staging table same though it changes column .

Sorry If i am not clear

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 3

I am still a bit mistified, do you mean they buy different data every day?

Basically, you would have to set up a connection manager for every different layout

pravat SSC-Addicted Points: 410 More actions · Answer 4

Lets say we have core table( for eg cusotmer) with 400 column which comes everyday. In this table about 350 column is always same. But for other 50 column we keep on changing , sometime they we would not even buy it. Source comes in txt file. Now we want to create staging table (tblstgcustomer---> name should be always same) everyday based on the column name of table. And one of the requirement is all field must be varchar 255 in database.

Is it really possible ?

Thanks

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 5

Why dont you split the table into that which cannot change from that which can change?

Your problem is actually one of two:

a) Your customer table may get very wide with varchar(255) for 50 plus columns, and you may end up hitting a limitation in terms of width

Now, for the fun part....

b) Its really not a problem defining columns and then not loading them (in other words, creating the table and then loading Nulls when you dont receive data). The problem will come in when you have to define the connection manager holding the data. Because from what I understand this is going to vary.

Hope this makes sense

~PD

pravat SSC-Addicted Points: 410 More actions · Answer 6

Thank you for reply. I know my suggestion were same to split the table.

what does it mean?

Its really not a problem defining columns and then not loading them (in other words, creating the table and then loading Nulls when you dont receive data). The problem will come in when you have to define the connection manager holding the data. Because from what I understand this is going to vary

We dont get any data more than 255 varchar and dont mind loading null if we dont recieve data?

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 7

What I mean is that your source OLEDB connection manager is going to change (in terms of layout - n columns).

That is going to be a problem, because you have to create a connection manager per layout

pravat SSC-Addicted Points: 410 More actions · Answer 8

pravat

SSC-Addicted

Points: 410

June 13, 2008 at 9:44 am

#828626

Thank you so much for information