Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

SQL 2005 express with SSIS

I would like to somehow be able to create an SSIS package with my Express version.  I have a good amount of data that i would like to upload.  Is there anyway i can do this w/o upgrading to the full version?  

0
ansonindustries
Asked:
ansonindustries
1 Solution
 
Anthony PerkinsCommented:
>>Is there anyway i can do this w/o upgrading to the full version?  <<
I am afraid not.  Even the SQL Server Advance Services does not appear to support any variation of SSIS.  See here:
Features Supported by the Editions of SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms143761.aspx
0
 
jmoss111Commented:
Developer Edition ($49 USD) is Enterprise Edition one seat not to be used for production.
0
 
nmcdermaidCommented:
If you just want to upload data you can use BCP or BULK INSERT. You can then use stored procedures to process the data.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ansonindustriesAuthor Commented:
i knew it was to good to be true.  lol  

well nmcdermaid, are there any good examples that i can use for BULK INSERT and the procedure you are referring to.  This is what my table looks like:

1. projdataid, (int, not null)
2. projectid,  (int, null)
3. costzone,  (varchar(10), null)
4. costcat,  (int, null)
5. costcode, (varchar(5), null)
6. quantity, (decimal(8,2), null)
7. qtyunit, (varchar(5), null)
8. totalhours, (decimal(8,2), null)
9. changeorder,(varchar(1), null)
10. complete, (varchar(1), null)
11. uploadid, (varchar(21), null)
12. created_by, (int,null)
13. created_date, (varchar(21), null)
14. created_ip (varchar(21), null)

1. is it important to have have values placed in the "BULK INSERT" command for each column heading above?  my goal is to let the projectdataid auto number itself since i would not know the next sequence number in the talbe unless i manually look it up.

We have a web based app that the user can manually choose values from options 3-8 above.  the rest of them are computer gen.  I would like to create an upload file that will perform the same type purpose my BULK statment should look something like this

BULK INSERT FROM c:\upload.txt  
( , , 9800, 944, 000, 10, LF, 40, Y,  , ,  ,  ,)

I would like to leave the other columns blank.  is this possible?
0
 
nmcdermaidCommented:
BULK INSERT is for inserting data into a database from a flat file. If you have people entering data from a web app then you should be entering data directly into the database. You shoudn't be going via a flat file at all, unless there is some other design parameter which I am unaware of.
i.e.
INSERT INTO YourTable (costzone,costcat,..,..)
VALUES ('X','Y',....)
As fields 9-14 are nullable, then yes, if you leave them out of the insert statement then no problem, they'll be blank.
To create an 'autonumber', make the projdataid an IDENTITY field. You don't need a sequence number table. You can use the SCOPE_IDENTITY() function to check the identity that was just created if you need it.
Assuming that there is some reason for needing to import a flat file, then you use a format file to tell BULK INSERT to ignore certain fields in the import table (i.e. 9-14)
If you can post your table creation script and a sample of your flat file, it will be quicker for me to show an example bulk insert statement, and example format file.
Here is an example anyway (straight from doco)
BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml')
 If you need to do any processing after the file is uploaded then you can use a stored procedure as well.
0
 
ansonindustriesAuthor Commented:
that was the easiest route.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now