Post reply

Transfering text file to database table through SSIS

  • January 17, 2007 at 2:45 am

    #115407

    hi

    I am very very new to sql server 2005.I want to create SSIS package for my text file to transfer in table.How i do this and where i will get that SSIS package (like in sql server 2000 we get that in EM under DTS) and how do i schedule these packages.

     

    Please guide me.

     

    Thanks

  • January 17, 2007 at 8:27 am

    #683751

    Hi,

     

    for a start you can use the import data wizard from SQL Server Management Studio.

    Right click on a database, select Tasks and then Import Data ...

    Follow the wizard, but take care to save the package. For a discussion about where to save your package, please use BOL. You can schedule a package mostly the same way as you did with SQL2000.

     

    Regards,

    Michael

  • January 17, 2007 at 10:04 pm

    #683902

    hi

    I am refering one article from

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

    "To review it, launch SQL Server Management Studio (or if you have it running, select the Connect Object Explorer... item from the File menu). This will display the Connect to Server dialog box, in which you need to point to Integration Services as the Server type entry and specify the server name (and provide authentication information, if necessary). "

    In my project I created one simple dtsx package and stroed it as regular path in project folder but i wanna to view it from Integration Service. I am on client side i.e my server is some where else.

    for that i went to object browser and select server type as Integration Service and give server name but authentication option i saw as grey out i.e i cannot see "sa" as user name but i can see my machine user as user name.And cause of that i am not able to connect to Integration Service server.

    what i should do to connect to Interation Service????

    Thanks

  • January 18, 2007 at 12:08 am

    #683915

    Hi,

    seems you are starting to mix up two things here.

    If you really want to design SSIS packages you need to use Business Intelligence Development Studio (BIDS). Ther you have a Visual Studio based development environment wher you can design your packages.

    In some cases there exists another way to be able to obtain a SSIS package. What you describe is a popular example for that.

    Concerning your connection. When you try to connect to the Integration Services instance, you normally have only integrated authentication enabled, that's why you cannot change the credentials. SSIS is not what you would think of as a database and thus "sa" is not a valid user. You can use "sa" to connect to your database in cases where you have explicitly enabled such connections and defined a password for "sa" (maybe during installation).

    For your case please check whether SSIS is at all running on your machine (Server Configuration Manager) or the remote computer and check which connection options are defined there. You need to enable remote connections as due to security reasons not all possible connections and protocols are enabled by default. You may learn more about that in help about Server Configuration Manager and Surface Area Configuration.

    You may have to ask your administrator to enable the connection for you.

    Regards,

    Michael

  • January 18, 2007 at 12:42 am

    #683920

    Hi

    thanks for your help..I checked SQL SERVER Configuration Manager>>> SQL SERVER SERVICES

    there i can see Integration Services,FullText Search,Sql Server,Analysis Services,Reporting Services....their status are in Running

    Log On As for Integration Services is NTAUTHORITY\NetworkService but rest of services are as LocalSystem.

     

    please tell me where i get

    "You need to enable remote connections as due to security reasons not all possible connections and protocols are enabled by default."

     

    Thanks

  • January 18, 2007 at 1:21 am

    #683924

    Hi,

    you may check the network configuration and/or client configuration in Configuration Manager, but the default config should be sufficient for you.

    The next step is using the Surface Area Configuration and check the confifuration of services and connections as well as for features. On my computer the program can be found in the Programs/SQL Server 2005/Configuration Tools folder of the Start menu.

    Hope that helps,

    Michael

  • January 18, 2007 at 2:12 am

    #683932

    Hi!

    I went to Sql Server 2005 Surface Area Configuration and I found that

    Under MSSQLSERVER 

    Database Engine

    Analysis Services

    Reporting Services

    that means we need to add Integration Services??? and if yes then can you please give us some guide lines??

    Thanks

  • January 18, 2007 at 2:19 am

    #683935

    Hi,

    I found Integration Services at the same level as SQL Server.

    Concerning your connection problems I was thinking about the remote connections as shown in the above picture.

    Regards,

    Michael

  • January 18, 2007 at 2:50 am

    #683937

    On Sql server 2005 Surface Area there are two options

    1) Surface Area Configuration for services and connections

    2) Surface Area Configuration for Features

    under 1) option I am getting

    MSSQLSERVER

    Integration Services

    SQL Server Browser

    and under 2) option I am getting

    under MSSQLSERVER

    Database Engine

    Analysis Services

    Reporting Services

    Now on that please guide me

    Thanks

  • January 19, 2007 at 5:08 am

    #684244

    Thanks friends!

    I opened integration services..It was authentication issue.

    now i have one more que..what is the point there to save packages in two different place one is File System and second is MSDB

    what that makes exact difference?? and if i want to add more steps to my package then how do i edit that package?? as there is no direct link to open package from Integration Services??

    Please guide me

    Thanks

  • January 19, 2007 at 5:17 am

    #684246

    Hi,

    now you need to return to the BIDS part of previous posts.

    I do not know of any other way to edit packages.

    There are a nuimber of reasons to save packages in locations other than MSDB.

    It depends too on your purpose or perspective, e.g. you being a developer or an administrator of a productive system.

    MS provides you with tools to transfer packages between development, test and production or allows you to choose where to deploy them to, ...

    Try BOL please. If this is not enough, there are some excellent books available, giving you a head start developing SSIS (not DTS) packages 😉

     

    Cheers,

    Michael

  • January 22, 2007 at 2:16 am

    #684628

    hi

    i need some more help from you!

    I want to extract data from my tables to text files. I Control Flow for each table.In data flow i created separate OLE DB Source and Flat File Destination as per my number of tables.(Is this right apporach????) Now i want to set my all Headers in tables also include in my Text Files (eg. "EmpID"|"EmpName"|"Address") like that.I set Custom Property of Flat File Destination (Header as these Header Name) but when i ran my package then first row of my table goes and add to this Header name like "EmpID"|"EmpName"|"Address"|"1"|"Jhon"|"Kan" actually i should get like

    "EmpID"|"EmpName"|"Address"

    "1"|"Jhon"|"Kan"

     

    where i am getting wrong???

  • January 22, 2007 at 8:25 am

    #684713

    Hi,

    from your description I cannot say that you are wrong.

    I have included a screenshot of how I would set up the flat file connection manager to achieve what I understood from your description.

    Regards,

    Michael

  • January 22, 2007 at 10:47 pm

    #684848

    Hi

    I am not able to view these screen shot please can you do another time.

     

    Regards,

  • January 23, 2007 at 12:22 am

    #684851

    Hi,

    in the next screen you see my simple example.

    You need to first connect the Flat File destination to the previous step in the data flow and then you need to open the component (edit it). Thus you make sure that the fields you obtain from the data source are known and can be used as field names in the header.

    You will be prompted to create or choose a flat file connection manager. Click the New... Button and follow the steps. The second one will bring you to the screen shown in my previous post.

    Regards,

    Michael

Viewing 15 posts - 1 through 14 (of 14 total)

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