How To Build A Package To Ftp Files From BLOBs With FTP Connection Info Also Held In The Database
Apr 24, 2008
I have a table with embedded files in it and also ftp server/user/pwd fields so that each record has all the data and information required and need to configure an SSIS package to to FTP them to the destination as specified in the record within the databaseAt the moment I have a data flow task which uses a Derived Column and an Export Column transformation which creates the files to a temp folder then it runs a ForEach loop container back on the control flow to FTP each file and to then move each file to an archive folder.
All I think I need to know how to do is to somehow control the FTP connection settings with the fields from the database within this foreach loop, but it is a for each file collection. I also need to update each record saying it has been FTP'd as well
Do I need to have an initial query outside of this foreach loop to populate an array which can be used for the 4 FTP connection fields (server/user/pwd/directory), if so how?
Any ideas as to how to correctly finish this package.
Thanks
View 2 Replies
ADVERTISEMENT
Jan 11, 2007
Hi All
€™m just starting out with SSIS and am trying to establish architecture standards that are sound and workable. We will be developing BI solutions for customers who use our banking and finance package that runs on an IBM System i. Modularity is important as all our BI solutions will have a combination of re-used functionality along with varying degrees of unique functionality for each customer. My current area of interest is in externalizing all environmental factors and ensuring such things as passwords are protected.
I understand how configuration files work and how to use environment variables to provide a layer of indirection to them. At this stage we want to avoid encryption of packages and plan to use file system based deployment. We plan to use windows security for SQL server so I have no issue in exposing connection info here but for our DB2 connections we need to include user id and passwords. As I understand it, configuration files can not be encrypted so how do I store this externally while preserving its security. Nothing I have read so far has given me exactly what I need.
View 1 Replies
View Related
Nov 13, 2007
I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
Any suggestions? Thanks.
T
View 3 Replies
View Related
Dec 14, 2006
Hi
I wanted to ask a question in terms of design (Performance, scalability, maintenance, flexibility etc.) is it better to refernce a document on the actual file system (on a file server) i.e. from a database link to the actual file at a disk location
e.g. in the database have something like \fileserver2006 estewtext.txt or C: estewtext.txt
or is it better to store all the files in a database as blobs?
The Question i would like to ask is more in the context of desigining a document management system?
View 5 Replies
View Related
Jun 25, 2007
Hi geniuses,
I have a core database question in MS Access and SQL Server.
Assume the following table in MS Access or SQL Server:
Employees table:
EmpID, Name, Hiredate, Photo;
and the following query:
"SELECT EmpID, Name, Hiredate FROM Employees;"
The question is, "does the blob object (Photo) affect the performance of the query even when not included in the select statement?"
I mean, will I gain some additional performance when separating the blob fields to another table?
Thanks for the answer.
View 4 Replies
View Related
Apr 29, 2015
I currently have a lot of XML blobs being stored in several of the production databases that I look after. They're there as a application "logging" feature, so that the developers can investigate issues if they ever occur.
I want to get these out of my OLTP databases asap and am currently looking at options. I know Brent Ozar recently posted an article about Content Addressable Storage systems [URL] ....
How do you handle point in time recovery? Any other ways of storing this data?
View 9 Replies
View Related
Oct 5, 2015
I have the master and msdb backup files of a broken server, as well as the .bak files of all other non system databases.
Now, in an online sql server, how do I retrieve the logins information from those .bak files?
View 10 Replies
View Related
Apr 9, 2008
Hey,
What I am trying to achieve currently with SSIS is to view the content in one folder for example 'New' and if there is files in this then move it to 'Archived'.
Any nice liinks or tutorial or general advice you guys could give me ?
View 13 Replies
View Related
Jul 16, 2007
My SSIS solution has about hundred packages and time to time I have to edit a package. I understand I could use 'Build' command to compile only updated package, as opposed to Rebuild which recomplies all of the packages.
Nevertheless, in both cases SSIS opens all of the packages in design environment before compilation. My packages are saved in SourceSafe and that process takes quite long and I was wondering if there was any other way to compile only updated package where none of the other packages are opened during Build/Rebuild process? For example we could use dtutil to deploy only updated packages without running Package Installation Wizard.
View 3 Replies
View Related
Jan 17, 2007
There is some way to exclude a package from the build process of a SSIS project ?
Cosimo
View 1 Replies
View Related
Sep 13, 2007
I'm getting really frustrated with using the web.config to store all database information. It seems like you really don't have any real flexibilty when moving an application from server to server (between dev, test and prod). Seems like you have to create separate config files when you move it every time. Seems like you always run into a risk of over writing one of them if you are not careful.
Is there a good way to design the connection object to be able to tell which server it is on and determine if it's a test, dev or prod type of connection. I have used a basic three connection strings that it will compare the computer name with the key. But again, the connection string is hard coded in the web config. At least this way I am able to move all code together without the worry of overwritting the config file. I just have to beleive there is a better way to build something like this. I've heard of setting up enviroment variables on the server, but not sure how safe that is.
I'm looking everywhere on the web, but get really lame examples. If anyone knows a good method or a sample I can check out, that would be great.
Thanks!
View 1 Replies
View Related
Aug 23, 2006
Hello.
I am attempting to install the first of the cumulative hotfix packages ( SQL2005-KB918222-x64-ENU.exe) after applying Service Pack 1 to a SQL Server 2005 std edn 64-bit installation. The service pack installed no problem but the hot fix fails with the following errors in the log file below.
I have installed the 32-bit versions of the hotfix packages onto a different server and I did not get these errors.
Any help on how to sort this out would be much appreciated!
08/23/2006 15:03:44.592 ================================================================================
08/23/2006 15:03:44.608 Hotfix package launched
08/23/2006 15:03:45.842 Product discovery successfully completed during the install process for MSSQLSERVER
08/23/2006 15:03:45.858 SP Level check successfully completed during the install process for MSSQLSERVER
08/23/2006 15:03:45.889 Product language check successfully completed during the install process for MSSQLSERVER
08/23/2006 15:03:45.905 Product version check successfully completed during the install process for MSSQLSERVER
08/23/2006 15:03:45.920 Command-line instance name check completed during the install process
08/23/2006 15:03:45.936 Baseline build check completed during the install process
08/23/2006 15:03:52.107 Attempting to install instance: MSSQLSERVER
08/23/2006 15:03:52.154 Attempting to install target: SRVLEBIZTALK
08/23/2006 15:03:52.170 Attempting to check for locked files: sqlrun_sql.msp
08/23/2006 15:03:52.232 Attempting to check for locked files: \SRVLEBIZTALKd$dd32a156dacc8dbe70a862647d70hotfixsqlFilessqlrun_sql.msp
08/23/2006 15:03:52.248 Creating MSP locked file check log at: C:WINDOWSHotfixSQL9LogsSQL9_Hotfix_KB918222_sqlrun_sql.msp.out
08/23/2006 15:04:02.107 MSP returned 1602: The user cancels installation.
08/23/2006 15:04:02.310 Successfully checked file: \SRVLEBIZTALKd$dd32a156dacc8dbe70a862647d70hotfixsqlFilessqlrun_sql.msp
08/23/2006 15:04:02.326 Successfully opened registry key: SystemCurrentControlSetControlSession Manager
08/23/2006 15:04:02.357 Failed to read registry key: PendingFileRenameOperations
08/23/2006 15:04:02.373 Failed to read associated hotfix build information for the following file: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnxmlfilt.dll
08/23/2006 15:04:02.388 Failed to read associated hotfix build information for the following file: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnThaWBrkr.dll
08/23/2006 15:04:02.435 Failed to read associated hotfix build information for the following file: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnquery.dll
08/23/2006 15:04:02.451 Failed to read associated hotfix build information for the following file: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnls400.dll.
-----------------------
It carries on with errors like the above but my post will be massive if I include it all, so below is the last few lines of the log file.
--------------------------
08/23/2006 15:04:07.591 Failed to read associated hotfix build information for the following file: C:Program Files (x86)Microsoft SQL Server90SharedMicrosoft.SqlSac.Public.dll
08/23/2006 15:04:07.653 Attempting to install file: sqlrun_sql.msp
08/23/2006 15:04:07.700 Attempting to install file: \SRVLEBIZTALKd$dd32a156dacc8dbe70a862647d70hotfixsqlFilessqlrun_sql.msp
08/23/2006 15:04:07.716 Creating MSP install log file at: C:WINDOWSHotfixSQL9LogsSQL9_Hotfix_KB918222_sqlrun_sql.msp.log
08/23/2006 15:04:07.732 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller
08/23/2006 15:04:07.747 Failed to read registry key: Debug
08/23/2006 15:05:11.197 MSP returned 1603: A fatal error occurred during installation.
08/23/2006 15:05:11.212 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller
08/23/2006 15:05:11.244 Failed to read registry key: Debug
08/23/2006 15:05:11.259 Unable to install MSP file: \SRVLEBIZTALKd$dd32a156dacc8dbe70a862647d70hotfixsqlFilessqlrun_sql.msp
08/23/2006 15:05:11.275 The following exception occurred: Unable to install Windows Installer MSP file Date: 08/23/2006 15:05:11.275 File: depotsqlvaultstablesetupmainl1setupsqlsesqlsedllcopyengine.cpp Line: 856
08/23/2006 15:05:11.322
08/23/2006 15:05:11.353 Product Status Summary:
08/23/2006 15:05:11.369 Product: SQL Server Database Services 2005 (64-bit)
08/23/2006 15:05:11.384 MSSQLSERVER - Failure
08/23/2006 15:05:11.415 Details: Unable to install Windows Installer MSP file
View 5 Replies
View Related
Feb 14, 2003
I'm trying to use DTS to copy data from an Accounting system to a SQL table. A login is required by the proprietary ODBC driver to the database-which includes company, user name, password. Is there code that I can put in the DTS package to pass this to the database so it can run automatically?
Thanks.
View 6 Replies
View Related
Aug 6, 2015
I am trying to dynamically create the connection to a database within an SSIS package.
the requirement is to allow the user to pass through the database as a variable and that variable will dynamically create the connection string in the connection manager.
Is this possible, if so how?
View 0 Replies
View Related
Oct 10, 2007
Hi,
I have installed MS SQL Server 2005 and Service Pack 2 on a new Windows 32-bit environment. I also installed the 32-bit 10g client (the Administrator option). The tnsnames.ora file is configured properly and I can tnsping to the Oracle database without any issues. I have created a package in Visual Studio and it runs successfully when I execute the job manually. I saved the package to the MS SQL Server and when I log into Management Studio to create a job for this package, I receive the following error:
Message
Executed as user: xxxSYSTEM. ...rsion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:42:37 AM Error: 2007-10-10 10:42:37.28 Code: 0xC0202009 Source: x Connection manager "x.x" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2007-10-10 10:42:37.28 Code: 0xC020801C Source: Data Flow Task OLE DB Source [18] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method ca... The package execution fa... The step failed.
I have created the package and logged into the server with the same ID to set up the job. And I have set the ProtectionLevel property to "Don't Save Sensitive Data" since I know this has been an issue in the past when I tried automating the job. I've four posts regarding this error message on the MSDN forums that aren't helpful at this moment - I'm hoping that someone will have found a solution since those posts. Any suggestions are welcomed.
Thanks for your help!
Sue
View 14 Replies
View Related
Aug 2, 2006
Hi,I have a problem, when I want to access to my SQL Server Expressdatabase from my ASP.NET application.My workstation ID is KITOLAP-HPMy username is user01Now I built the following connection String (VB.NET):Dim workstation As String =System.Environment.GetEnvironmentVariable("computername")connectionString = "workstation id=""" & workstation & """;packetsize=4096;user id=user01;integrated security=SSPI;data source=""" &workstation & "SQLExpress"";persist security info=True;initialcatalog=webshop"connection = New SqlConnection(connectionString)Although I put as user id "user01" the application makes an error whenI try to open the connection, saying that the login with user "aspnet"failed.How can I change it so that as login-user "user01" is taken and not"aspnet" or otherwise, how can I create a new user in the SQL Serverdatabase with which I could access the DB??thanks,kito
View 1 Replies
View Related
Oct 8, 2007
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
View 20 Replies
View Related
May 16, 2006
I just spent the past 3 or 4 hours trying to figure out how i could represent carriage returns and line feeds properly in package configuration variables that an SSIS package can properly handle, so i figured I'd post my results in case anyone else has struggled with this.
Initially, I thought I would be able to just represent the delimiter as the same value that you can pick from in the flat file connection manager dialog, i.e. {CR}{LF}, {CR}, {LF}, etc. nope.
Then I decided to try actually storing the characters as hex, i.e. _x000D__x000A_ for {CR}{LF}. nope.
After scanning the .dtsx packages for a while, I finally found something that works: you have to represent the curly brackets in hex, and you can simply use the CR or LF string literals. so if you wanted to have a row delimiter that was {CR}{LF}, you would need to set your variable to: _x007B_CR_x007D__x007B_LF_x007D_
Hope this saves some people the pain I had to endure.
View 1 Replies
View Related
Jul 6, 2006
Hi. New to ASP.NET and first time posting.
My web app connects to a SQL database - SQL authentication.
Users login to the web app through the login server control. Once authenticated, it is my understanding that the user name and password are stored on the client as a cookie.
How do you programmatically get this user info and use it for the userid and password parameters of the connection string?
Is there a better way to use the authenticated user info to access a SQL database?
Thanks
View 2 Replies
View Related
Oct 27, 2006
Hi All,
I'm new to SSIS. I'm trying to build a simple package, run it on dev and migrate it to our stg and tst environments. It works on my dev environment great! It simply loads data from a flat file into a table. The trouble i'm having is exporting the connection string info to an xml file and runnign it on other environments. I am not storing the package on the db. I am saving to a file and running through SQL Server BI Development Studio.
I go to "Configuration File" and export InitialCatalog, ServerName, User, Password to the file. I can manually change the xml to the new environment but don't know how to set the password. It's not in the xml and i am constantly getting "Login failed for 'sa'" errors. Anyone know how to do this. I CAN get this whole process to work if i use Windows authentication security but this isn't an option. I need to use the db security.
Thanks,
Dave
View 4 Replies
View Related
Mar 29, 2007
Hello All,
I am trying to convert an application created using DTS classes to SSIS object model. I have found following code in the application.
For x As Integer = 1 To mTask.Properties.Count
If mTask.Properties.Item(x).Name = "SourceConnectionID" Then
CnID = mTask.Properties.Item(x).Value
Exit For
End If
Next
For x As Integer = 1 To mPkg.DTSPackage.Connections.Count
If mPkg.DTSPackage.Connections.Item(x).ID = CnID Then
Return mPkg.DTSPackage.Connections.Item(x).Name
End If
Next
Return mTask.Properties.Item("SourceObjectName").Value
How can I retrieve or Set "SourceConnectinId", "SourceObjectName" of a data flow task in SSIS
Please help me to solve this problem.
Thanks in advance
Subin
View 5 Replies
View Related
Mar 8, 2007
I've read about this hotfix at http://www.kbalertz.com/Feedback_933097.aspx but I can't find anything on microsoft.com about it. It sounds like a worthwhile hotfix, but I'm wondering about the absence of an announcement on microsoft.com.
Anyone else know about this hotfix?
Thanks!
View 3 Replies
View Related
Aug 20, 2007
I keep getting this error message on the subscription execution, even though the account is a domain administrator. Both publisher and subscriber are on the same domain. Can someone tell me what is the problem?
Message
Replication-Replication Distribution Subsystem: agent [Agent Name] failed. Executed as user: [Domain]administrator. A required privilege is not held by the client. The step failed.
Regards,
View 1 Replies
View Related
Apr 3, 2007
I am getting this error message "Replication-agentclassname: agent F1TESTSQLSERVER20-ELC-ELC_Pub-F1TESTSQLExpress-39 failed. Executed as user: . A required privilege is not held by the client. The step failed." All the users replication agents have failed and I am looking on how to resolve this serious error. Where do I start? I am using merge replication.
Thanks!
SAM
View 2 Replies
View Related
Mar 22, 2006
Hello,
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
with best regards
Anton Kalcik
View 5 Replies
View Related
Jun 16, 2015
I running SSIS package job without sql agent , it is working fine.when i am running through sql agent not running.
created Proxy accountÂ
job failed and give above error.
Server is cluster and taking data from desktop.
server is in one domain and desktop in another domain.
View 3 Replies
View Related
Jul 6, 2006
Dear All,
I now have two SSIS package, "TESTING" and "LOADING". The "TESTING" package have an execute package task that call the "LOADING" package. When I want to execute the TESTING package, how can I setup the connection string so that I can edit the password of the database connected by the "LOADING" package?
Regards,
Strike
View 8 Replies
View Related
Jun 29, 2006
I am using execute pacakge task to execute another package . I am giving the Connection string for the package to execute. It works fine in my development machine but when i try to run in another server after i deployed it. It looks for the datasource path of the DTSX file in the same location.
how do i set the path according to each server where the dtsx file is stored. or any other method of storing it like connection string.
if i store it in theparent package variable where should i point to...
thanks
aa
View 1 Replies
View Related
Apr 26, 2006
I am receiving an error on my master package that executes a number of other packages. The individual packages work fine when executed by themselves. However, I am getting the following error when I attempt to execute it from another package:
Error: Failed to acquire connection "conneciton". Connection may not be configured correctly or you may not have the right permissions on this connection.
Thanks in advance for your help.
View 1 Replies
View Related
Feb 1, 2008
Hi, I am noob, so excuse me if this is a stupid question, but I just bought a Learn HTML in 3 Hours book and a learn SQL in 10 minutes book and I was wondering how I would start to go about to build a super flexible enterprise wide web based database application, that is so flexible the users can put in any old crap in the front end and make up new fields as they go. Another one of my requirements is that it perform super fast and be distributed securely across my international multi billion dollar a year company.
So like I know Excel and a little VBA and I know how to use the query builder in Access and I landed this big old contract to revamp this old comapny's infrastructure. Should be pretty easy, right? Everyone is doing it.
So can you guys like help me out for free and stuff?
View 4 Replies
View Related
Jul 23, 2005
What is the fastest way to generate an SQL 2000 databaseout of SQL scripts.The SQL scripts contain the create tables, views, storedprocedures, triggers, constraints, and the tables DATArecords.What are my options? isql? osql? are there other ways?Thank you
View 6 Replies
View Related
Jul 20, 2005
I have been building ASP, ASP.NET and Crystal Reports on SQL serverfor a few years now.I have build them mostly on an Intranet or with companies that havetheir own web servers.I now need to build an application for a company that doesn't not havea web server. Just a small shop that wants an internet application.How do I build an database application when the site will be host byan ISP? The database will be SQL Server 2000, but located locally.The site will be maintained by the ISP? How does the an applicationhosted by an ISP interact with a database located locally (client'ssite)?If someone could point me toward some tutorial or tell me how to dothis, I would appreciate it.
View 1 Replies
View Related
Dec 1, 2015
I have a SQL script folder on my HDD. The folder contains SQL Scripts and subfolders with Constraints and Extended properties. I need a BAT file that builds a database on my SQL Server instance.
I have the following structure:
 Tables (folder)
 dbo.Customers.sql
 dbo.OrderLines.sql
  dbo.Orders.sql
 dbo.Products.sql
  Constraints (folder)
      Foreign Keys(folder)
dbo.OrderLines.FK.sql
dbo.Orders.FK.sql
  Extended Properties(folder)
   dbo.Customers.EXTPRP.sql
   dbo.Products.EXTPRP.sql
The Server name for the SQL CMD is PCMSSQL2014? user: sa pass is empty.
View 3 Replies
View Related
