January 9, 2009 at 7:36 am
So the title states my predicament. openrowset is disabled and i cant use a linked server, so unless someone has a tricke idea (i hope so!), then it looks like im stuck using the SSIS import wizard.
Now you may say, hey thats not bad, edit it to look for a generic file name or all of the files you are looking for and then swap the files out daily as new data is added etc etc.
I have tried editing an SSIS package, but i can never get them to work. If i use the import wizard to import data from an excel file or a flat file then it works fine, but anytime i save the package i cannot get it to work, i have also tried editing the package through the VB app that come with the editor for these packages, but i know little to nothing about VB so i am really just guessing, unfortunately i cant give more info about this process unless someone has some suggestion and steps to take.
I work for a rather large company and have been given a development server that my database is located on to use. and i recevie a lot of different file types and a lot of files that need to be imported to my tables, daily weekly and monthly. I am REALLY sick and tired of clicking through the wizard to import all of these files.
I just realized i am incoherently rambling, so again if anyone has any suggestions please let me know, i am willing to try out anything.
January 9, 2009 at 8:08 am
Assuming the incoming file format lends itself to it, you could go "old school" and use BCP. It's fairly easy to automate, so that you just have to feed it the new name of the file, etc.....
Otherwise - there is also a way to set SSIS packages up so that you can run them from a command-line utility of their own (dtexec), with a global variable to take in the file name of the file you want imported.
I can't help but thing you're using the wrong view to look at the SSIS package. By far and large, unless you're actually building an actual script object, you should be looking at a visio-looking type flow diagram, etc... and not loads and loads of code.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 9, 2009 at 8:14 am
Matt Miller (1/9/2009)
Assuming the incoming file format lends itself to it, you could go "old school" and use BCP. It's fairly easy to automate, so that you just have to feed it the new name of the file, etc.....
I am looking into this right now, but i wanted to reply and thank you. I am guessing you mean bulk copy program?
Matt Miller (1/9/2009)
I can't help but thing you're using the wrong view to look at the SSIS package. By far and large, unless you're actually building an actual script object, you should be looking at a visio-looking type flow diagram, etc... and not loads and loads of code.
[/quote]
I think you are correct, and i do remember seeing a flow chart type editor where you get the sourse edit the columns and then insert into the table etc.
It looks and feels fairly simple but each time i do this i get an import error. It finds the data but from what i remember without doing it again right now, it errors out on the conversion and.or copy over.
Considering the question about the work flow editor, and the server/user setup. Do i need to specify my local machine on the server I.e. instead of C:\whatever.txt as the source i use the qualified domain name and share?
January 9, 2009 at 8:26 am
JPLeBlanc (1/9/2009)
It looks and feels fairly simple but each time i do this i get an import error. It finds the data but from what i remember without doing it again right now, it errors out on the conversion and.or copy over.
SSIS is very, very picky about data types. It will not do any form of implicit conversion, so you have to make sure that the data types match, or do explicit conversions (there's a conversion operator iirc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2009 at 8:35 am
GilaMonster (1/9/2009)
JPLeBlanc (1/9/2009)
It looks and feels fairly simple but each time i do this i get an import error. It finds the data but from what i remember without doing it again right now, it errors out on the conversion and.or copy over.SSIS is very, very picky about data types. It will not do any form of implicit conversion, so you have to make sure that the data types match, or do explicit conversions (there's a conversion operator iirc)
I have attempted this, but who knows if i was doing it correctly or not, do you all know of any specific learning courses for the workflow editor? or do i just have to be a VB guru? It all looks clear and simple, but maybe im just not firing on all 2 cylinders 🙂
So besides BCP and SSIS are there anymore suggestions for bulk imports? I really like TSQL coding for this but well without openrowset im not sure where else to go.
and on BCP (again sorry im not researching right now end of month end of year reporting yay!) are these outside programs that need to be purchased (freeware/opensource) or is this part of MSSQL server management studio?
January 9, 2009 at 9:00 am
JPLeBlanc (1/9/2009)
I have attempted this, but who knows if i was doing it correctly or not, do you all know of any specific learning courses for the workflow editor? or do i just have to be a VB guru? It all looks clear and simple, but maybe im just not firing on all 2 cylinders 🙂
Data flows and all the operators that are part of them (conversions included) are found in the data flow tab, they're not part of the work flow. All that's part of the work flow is the Data flow operator itself.
I've been working with SSIS on and off for a couple of years and I've never had to write a line of VB code. If you're writing code in SSIS, good chances are you're going the wrong way
and on BCP (again sorry im not researching right now end of month end of year reporting yay!) are these outside programs that need to be purchased (freeware/opensource) or is this part of MSSQL server management studio?
Open books online and look up bcp.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2009 at 9:13 am
Thanks for all the help everyone, i am going to do some looking and im sure ill hit this thread again with more questions. 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply