If I Had Any Hair Left I'd Pull It Out. How Do I Create A SSIS Package Which Creates A Table That Never Expires?
Mar 6, 2007
Hello everyone,
I'm not at all comfortable with SSIS so please forgive me if I overload you all with information here:
I need to create a data table using SSIS which does not delete the previous days data. So far all the data tables we use to write reports in Visual Studio are constructed in SSIS as follows.
1 - Excecute SQL Task - DELETE FROM STOCK
2 - Data Flow Task
3 - Data Reader Source - SELECT * FROM ODBCDATASOURCE
4 - OLE DB Destination (Creates table STOCK)
The data tables which are created this way are stored in a data warehouse and scheduled to refresh once a day, which means that any data from yesterday is lost when the updates run. So, I tried to create a table which never has its previous days' data deleted by using just the last three steps above - and it worked great in Visual Studio, no problem at all. However, when I added this SSIS Package to the Update Job in SQL Server Management Studio, the job totally rejected the packed with the message: "The command line parameters are invalid. The step failed".
I thought I could work around this problem by asking the job step to excecute a simple SQL query to insert the data from table1 into table2 (and would thus negate the need for a SSIS Packege at all), but it threw me a curve ball with some message about not being able to use proxy accounts to run T-SQL Scripts.
If anyone knows how to create a SSIS package in which the data never expires please could you impart some wisdom my way. I only need to do this once for a specific report.
Please, when answering, bear in mind that I'm a simple fellow with little understanding of the inner workings of SQL Server and its various components, so please use short sentences and simple words.
DDL and then write data using INSERT INTO ... DDL.
There are three strange things that i've encountered and will be glad to get help from anyone:
If I use as a table name expression with spaces, the name is changed to expression with underscore '_' symbol instead of spaces. I can not set trailing '$' when creating a table, but when inserting data I have to use the name with traling '$' otherwise the exception is thrown. Moreover, if i get the scheme of the Excel file later I am getting TWO tables instead of one: a first with the name without '$' and another the same with trailing '$'. Nedless to say that visually Excel shows only a name without '$' If I am trying to do same operations using OLEDB 12.0 (Office2007) I get invalid file. If anyone knows how I can overcome above issues, please write me a code. I am coding in C# but VB examples are as good as any other.
I want to automate SSIS package to pull data from SAP ECC 6.0 on monthly basis.
I am trying to use Microsoft .NET Data Provider for mySAP Business Suite which will call RFC_READ_TABLE function in SAP.
i.e. Source SAP Table: CE1LSC0 SAP Field: Budat (Calendar Date Field) Data Type : varchar Format: YYYYMMDD
Interface Microsoft .NET Data Provider for mySAP Business Suite
Destination SQL Table: TCE1LSC0
Right now I am pulling data manually from Import / Export task
SQL Script which i am to using to pull last month data: Filter: To bring data on monthly basis ( If i run the package in May then i will like to pull data for month of April)
Code Snippet select * from CE1LSC0 where CE1LSC0.BUDAT between 20080401 and 20080430
Now to automate Call SAP Function Module: RFC_READ_TABLE requires following variables @QUERY_TABLE @DELIMITER @OPTIONS @FIELDS
Script: Code SnippetEXEC rfc_name[{value | @variable [OUTPUT]}][,...n][@parameter = {value | @variable [OUTPUT]}][,...n] [;] SQL Stored Procedure to get variable: @LastMonthFirstDay , @ LastMonthLastDayCode SnippetCreate proc sp_GetDateasDECLARE @Today datetime, @FirstDay datetime , @LastDay datetime , @LastMonthFirstday int, @LastMonthLastDay intSET @Today = CAST(CONVERT(nvarchar(10), GETDATE(), 120) AS datetime) -- strips time offSET @LastDay = DATEADD(day, - DAY(@Today), @Today) -- last day of previous monthSET @FirstDay = DATEADD(month, -1, DATEADD(day, 1, @LastDay)) -- first day of previous monthset @LastMonthFirstday = Cast(Replace(Convert(varchar(10), @FirstDay, 120), '-', '') As int)--@FirstDay set @LastMonthLastDay = Cast(Replace(Convert(varchar(10), @LastDay, 120), '-', '') As int)--@LastDayselect @LastMonthFirstday as 'LastMonthFirstday', @LastMonthLastDay as 'LastMonthLastDay' My problem is that i am not able to provide my stored procedure variable to SAP RFC to automate my ssis package. Can anyone suggest something or advice how to automate my package... I have went through all the sap.net connector site and related documents but it of no use as it work with visual studio 2003 and I am using visual studio 2005 / MS SQL SERVER 2005....
hey out there, just started playing with this so bare with me... i've used the data export wizard to create a dts package in sql server 2000. the package takes selected tables, creates an access database and then dumps all the data into the tables. it works fine when i run the package in enterprise manager, but when i call it from asp.net (vb.net) the access database gets created, the tables are created but it fails to dump the data! this is my code:1 Public Sub executeDts() 2 3 Dim oPkg As DTS.Package2 4 oPkg = New DTS.Package2 5 Dim oStep As DTS.Step 6 Dim sMessage As New StringBuilder 7 8 9 oPkg.LoadFromSQLServer("serverNameHere", "userNameHere", "passwordHere", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , "packageNameHere") 10 11 For Each oStep In oPkg.Steps 12 13 sMessage.Append("<p> Step [" & oStep.Name & "] ") 14 15 If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then 16 17 sMessage.Append(" failed<br>") 18 19 Else 20 21 sMessage.Append(" succeeded<br>") 22 23 End If 24 25 sMessage.Append("Task """ & oPkg.Tasks.Item(oStep.TaskName).Description & """</p>") 26 27 Next 28 29 Response.Write("sMessage = " & sMessage.ToString & "<br/>") 30 31 oPkg.Execute() 32 33 oPkg.UnInitialize() 34 35 oPkg = Nothing 36 37 End Sub 38
am i missing a step?! it seems very odd that the tables are created but the insert fails... any advice anyone can offer would be great! cheers, jake
I have several SSIS packages which are run in sequence. But some strange reason sometimes a SQL Dump is created. If I restore database and restart the same ETL is works, or it creates another SQL Dump in a different place. Any idea on how to debug this problem?
SQL Server 2005 Developer Edition SP1 ( should I upgrade to SP2? ) Windows 2003/r2 64b ( latest patches )
Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) Microsoft .NET Framework Version 2.0.50727
Installed Edition: IDE Standard Microsoft Visual Studio 2005 Premier Partner Edition - ENU Service Pack 1 (KB926601) This service pack is for Microsoft Visual Studio 2005 Premier Partner Edition - ENU. If you later install a more recent service pack, this service pack will be uninstalled automatically. For more information, visit http://support.microsoft.com/kb/926601
SQL Server Analysis Services Microsoft SQL Server Analysis Services Designer Version 9.00.2047.00 SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 9.00.2047.00 SQL Server Reporting Services Microsoft SQL Server Reporting Services Designers Version 9.00.2047.00
I am learning SSIS and I am working through the Wrox book on 2005 SSIS. When I try to Import the files from the AdventureWorks2000 database I cannot see the Human Resources files. I was able to go through the wizard once and see the files and select them and proceed, but now I cannot see them. Please keep in mind I am a newby to this. What should I check for or what am I doing wrong?
Is there anyone out there that can help me - please! I'm about to put my head through a wall.
I used to be able to create SSIS packages in VS 05. It's been a while since I've done it, but when I went in the other day, it would let me - all I can do is get errors. It tells me an "error prevented the view from loading" when it opens in control flow (blank packages and ones I created before all this started) and I get this error :
"Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (System.Windows.Forms)"
If I go over to the Data Flow tab and there are no errors until I click "Click here to add a new Data Flow task", then I get this :
"Object reference not set to an instance of an object. (Microsoft.DataTransformationServices.Design)"
So... here's what I've tried.
Uninstalled and reinstalled VS 05 and SP1 (this was before I realized the problem was with SQL).
Uninstalled and reinstalled Office 2003 Web Tools.
Made sure all the MSXMLs were registered.
Uninstalled and reinstalled all SQL Servers and Tools/Components.
Made sure all SQL Services were running under Local Account
Made sure Integrated Services service was running.
Applied all SQL patches.
I cannot get this to work and I need to be able to create packages. The only other step I can think of is wiping my computer and I don't want to have to do that.
Please please can anyone help me?? I've been scouring the internet and working on this for 1 day and 1/2.
Can someone point me to any whitepaper on how to use a Transact SQL script to create an SSIS package. I will need to be able to run the script at various customer sites.
I am working on modifying a VB6 app that dynamically creates DTS packages to copy data from one database to another depending on the selections made in UI. The project currently uses DTSPackage object library and DTSDataDump Scripting object library. We are in the process of upgrading the server to SQL 2005. I am exploring the possibility of replacing code that generates DTS packages on the fly with SSIS packages.
Is it feasible to do this in VB6 ? I have referred to similar posts which focus mainly on VB.NET or C#. Any help with white paper or sample code would be appreciated.
I want to create a package that imports data from a Visual Foxpro database to SQL Server 2005 Express database. I used the wizard in BI Development Studio (similar to the DTS in SQL Server 2000) to create a package and noticed that the SQL statements created in the Preparation SQL Task only has code for creating tables. I want to make the package such that it first creates the destination database before creating all the related tables in it! When I tried to edit the SQL code to include DROP DATABASE and/or CREATE DATABASE statements, these were rejected.
Is it possible to do this or do I have to first create the database outside the package and then call the package? I want to make this a seamless process for clients who do not have the know-how of SQL Server database administration.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
i have to manipulate/calculate an attribute at runtime in a number of dataset within a package. since the manipulation will be the same for all these datasets and that it has to be done with a script, i am looking to find a way of creating a single instance of this script and call it as and when necessary.
could i use a variable of data type object and pass and retrieve parameters into and out of it?
lets assume the following scenary to illustrate my point. i have to create a new attribute in two datasets ( ds1, ds2) which is based on the following attributes: firstname surname
and that i want to create this new attribute as:
surname, firstname
so i am creating a single string composed of surname plus a comma and firstname.
thus i will be creating a new attributes called custNames for two or more dataset and it tomorrow it is decided that i should be done in a different way then i only need to amend in on place.
i know it is not clear but i will be glad to anwser any queries to clarify this question.
I am new to SQL Server 2005, I need Help to create a SSIS package for the below taks, My task is Refer a Column [Status] in the Logtable which is used to maintain the logs for the running processes, When ever any Rows with the "FAIL" status in Status column occurs in the LogTable then get the Respective ErrorRowNumber and get the Details of the Error from other 2, 3 tables which will be a simple SQL statements, when you get the Error Details then mail these details to the given Email id.
For this I need a help In creating a SSIS package which will continuously check the Status and if any FAIL status occurs then the Error details need to mail to mail id which are stored in the config files of SSIS package.
If any one has better idea to send the mails then please let me know.
if any online tutorial is available that will also help. Thanks in advance
Create a SSIS package for following scenario.I have one excel file which will contain 10 records for Monday, 12 records for Tuesday, 7 on Wed, no records on Thursday so if records are there I get mail if no records are there I didn't get mail daily.
Hi, I want to create a package to import some tables from database X from Server XYZ to database X of server ABC. (As my X database on server XYZ is gets updating everyday so i need to update it on X of server ABC using the package.) So i have created a package using the import export data transformationn services. It runs fine while creating. i.e importing data for the first time. But when i have saved that SSIS package on SQL or File system and scheduled it to run daily, but if fails everytime. I am not getting the error its giving. Because everytime when i go to view history of that package it just gives me messages like step1 started by user xyz and failed. Can you please help me to sort out this problem. If possible give me steps which will help me to create package to run above scenario. you can mail me the solution on abhijeets@nedbank.co.za
I am receiving the following error when attempting to create a new package in SSIS 2005. I have completeley re-installed, and even had our support folks re-image my pc, but no luck. Works fine on my laptop, and others are not having any issues.
Microsoft Visual Studio is unable to load this document: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
Any help would be greatly appreciated. Thanks, Bryan
I have a solution with a couple of ssis projects in it. Everytime I open the solution, Visual Studio creates an extra .database file for the project's existing xxx.database file. The solution is under VSS control and VS2005 checks out the project and shows the file as a newly added file.
What causes this and how can I prevent this from occurring?
There are 2 of us working on the solution and the other fellow does not see this behavior.
The files do not show up in the VS2005 solution explorer. If they are user specific as I suspect (impersonation info?), then they should not be added to the .dtproj project file.
I am new to SSIS. I have a requirement that from a flat file need to import the data into SQL Server DB(SQl Server2008r2).
1.When the file doesn't found in dir need to send a mail. 2.error reading And writing ,on which record the error occurred and capture the error details and need to send mail. 3. In success also need to send a mail.
I am looking high and low for some assistance with developing a VB .NET solution that I programmatically create a package and add tasks. I am adding a BULK INSERT task to load large FLAT TEXT files into SQL Server 2005 tables. When I execute the application I execute a package validation and it always returns FAILURE. I have been reading and searching like crazy and I have bought 2 microsoft books, TO NO AVAIL! Can anyone PLEASE help me with this. Thank you!
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.
Requirement: When we create SSIS Pacakge using Businessinteligence studio.Each Source and Destination or whatever we using the Control required DB Connection.
we connect theDB server and Database Table through manaully .Instead of Manual i need dynamic Global varible for Connection String .How to achieve this connection string.
because suppose we create SSIS Package in Developement Server Latter We change the Server from Developement to Another Testing Server . at that time we dont requierd for changing manulay.any one pls reply me.
Same as in Dotnet we give configiration XML file .we gave the Connection strng. how to in SSIS we do?
2) NG20150623.txt 2015-06-23 00:00:00.000 NG 20150701 43
3) HO20150624.txt 2015-06-24 00:00:00.000 HO 20150701 43 And so on..
But the requirement is to have a dynamic query where we can have more number of Codes or less number of codes and similarly the package should generate dynamic text files, one .txt file per code. What is the best way to create a package which can meet the above requirement?
We are in the process of trying to automate our production releases (what a concept ;-)
The database is SQL server 2005 All objects are being stored in VSS Using Nant and Cruise Control for the actual migrations. I have two directories - Create (for a brandnew database) and Change (db object changes)
In my 'Change' script, I do the following -
1 - Take backup of database 2 - Migrate objects from 'change' directory to production 3 - Script out all objects of database and save in the 'Create' directory
For the #3, I was hoping I could create an SSIS package that would script out all database objects and save them on the VSS server.
I'm new to SSIS and want to verify it's something that can be done before I start down that path. If anyone has any examples or references, it would be much appreciated.
The following are the output lines from my code which is constructing some T-SQL queries on the fly. The query highlighted in yellow is the problem query. The code upto the problem query is working correctly and I am able to see the output tables in the Query Analyzer
STEP 1 : Create 3 Tables In Dynamic T-SQL : Showing 3 Strings from Exec statement
Create Table ##Test_word28July2007185548990201 ( t float, e float, s float, word varchar(80) ) Create Table ##OUT_Test_word28July2007185548990201 ( t float, e float, s float, word varchar(80), KeywordID int, rank float ) Create Table ##STVR_FLOAT_Test_word28July2007185548990201 ( var_val float)
Step 2: Retrieving A Value Into Another Global Table (table has only 1 row and 1 column) Insert Into ##STVR_FLOAT_Test_word28July2007185548990201 ( var_val ) Select IsNull( t , 0 ) from ##Test_word28July2007185548990201 (1 row(s) affected)
Step 3: I need the value (var_val) in ##STVR_FLOAT_Test_word28July2007185548990201 Update ##Out_Test_word28July2007194827580759 Set t = Select var_val from ##STVR_FLOAT_Test_word28July2007194827580759 where t > Select var_val from ##STVR_FLOAT_Test_word28July2007194827580759 Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'Select'. Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'Select'. (1 row(s) affected)
Problem Definition
Part 1
The update query is trying to retrieve a value in a dynamically constructed table. ##STVR_FLOAT_Test_word28July2007185548990201 (1 column var_val , 1 row)
Update <Table_Name> set t = @var_val where t > @var_val The update query is simple, except I need to "SELECT" @var_val from the dynamic table
The @var_val is a float value.
I have outputted the contents of the table that is holding the @temp_val variable It has the correct value and the table has only 1 row and 1 column.
WHAT IS THE SYNTAX OF A QUERY TO SELECT A SINGLE VALUE FROM A TEMP TABLE WITH ONLY 1 ROW OR COLUMN AND USE THE SELECTED VALUE AS A VARIABLE IN AN UPDATE STATEMENT
I have a SQL2000 DTS package that executes vbscript to loop through a recordset which:
- runs a stored procedure and populated tables
- builds a recordset from the populated tables to write records to an Excel file
- writes status to text files with either the error or success notices
I use FSO to set up the success and error files, but the scheduled job in SQL2005 which calls the SSIS package returns the following error:
"Retrieving the file name for a component failed with error code 0x0015F74C"
I can successullly run this (vbscript) in both the SSIS package via the BI Development Studio and in MS Access (exactly the same code in both) - but not as a SSIS package called in a scheduled job in SQL2005.
I am at an impasse with this ... any and ALL assistance would be GREATLY appreciated.
