Guys
I've migrated a SQL Server 2000 DTS package over to SSIS (which seems to have worked), its a really simple package with just one item, it first checks for a tables existance and deletes if necessary, then recreates the table and then inserts data from a selection of other tables, except its only creating the table no data is being inserted, yet the script to insert data works as I've tried it in a query. Any ideas
Thanks inadvance
SSIS package
Roxanne163
Hi Thanks for the swift reply, I'm using the control flows and an execute sql task, as is it was imported, the only change I've made is the server names, as I said it does work no errors are generated, just when I check the table it's empty.
the T Sql used in the task is as follows, not sure what other information to provide, but if theres something specific let me know
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'tblwManagementInformation'
AND type = 'U')
DROP TABLE tblwManagementInformation
GO
CREATE TABLE [dbo].[tblwManagementInformation] (
[Pol_No_Uni] [int] NOT NULL,
[Log_Date] DATETIME Null,
[Reference] VARCHAR(100) Null,
[U_W_Ref] VARCHAR(100) Null,
[Insured] TEXT Null,
[Description] VARCHAR(100) Null,
[n100_Percent_Si] MONEY Null,
[Com] REAL Null,
[Written_Si] MONEY Null,
[Premium] MONEY Null,
[Commencement_Date] DATETIME Null,
[IPT] MONEY Null,
[Taxed_Premium] MONEY Null,
[Cli_Earned] MONEY Null,
[Premium_To_Insurance] MONEY Null,
[Total_To_Insurance] MONEY Null,
[Pol_No] VARCHAR(15) Null,
[Policy_Signed] DATETIME Null,
[Rate] DECIMAL(18,3) Null,
[PolicyFormName] VARCHAR(20) Null,
[WordingsFormName] VARCHAR(25) Null,
[Percent_To_Insurer] DECIMAL(8,2) Null,
[UserName] VARCHAR(50) Null,
[TeamName] VARCHAR(100) Null,
[CategoryGroupDescription] VARCHAR(50) Null,
[BusinessName] VARCHAR(50) Null,
[PIS_No] VARCHAR(20) Null,
[pDate] DATETIME Null,
[Total_Premium] MONEY Null,
[Name_of_solicitor] VARCHAR(50) Null,
[Commission] REAL Null,
[pFirm] INT Null,
[pLocation] INT Null,
[DX_Address] VARCHAR(100) Null,
[Telephone] VARCHAR(50) Null,
[Fax] VARCHAR(50) Null,
[Ad_House_Name] VARCHAR(50) Null,
[Ad_Street] VARCHAR(50) Null,
[Locality] VARCHAR(50) Null,
[Ad_City] VARCHAR(50) Null,
[Ad_County] VARCHAR(50) Null,
[Ad_Postcode] VARCHAR(50) Null,
[pContact] INT Null,
[Title] VARCHAR(50) Null,
[First_Name] VARCHAR(50) Null,
[Surname] VARCHAR(50) Null,
[Job_Title] VARCHAR(50) Null,
[Solicitor_Ref] VARCHAR(50) Null,
[Direct_Telephone] VARCHAR(50) Null,
[Direct_Fax] VARCHAR(50) Null,
[Direct_Email] VARCHAR(50) Null,
[Law_Society] BIT Null,
[ContactType] VARCHAR(50) Null,
[Delete_Mail] BIT Null,
[Hit_Rate] DECIMAL(18,2) Null,
[Rank_Value] DECIMAL(18,2) Null,
[Enquiries] INT Null,
[Rank] INT Null,
[DateLastContacted] SMALLDATETIME Null,
[Contacted] BIT Null,
[Phone] BIT Null,
[ContactsContactsFax] Bit Null,
[Post] BIT Null,
[Email] BIT Null,
[PanelMember1] BIT Null,
[PanelMember2] BIT Null,
[PanelMember3] BIT Null,
[PanelMember4] BIT Null,
[PanelMember5] BIT Null,
[ContactsCommission] BIT Null,
[UncompetitivePremiums] BIT Null,
[Service] BIT Null,
[CompanyPolicy] BIT Null,
[Product] Bit Null,
[Flexibility] BIT Null,
[Contact_Added] DATETIME Null,
[RegEmail] BIT Null,
[DirectMarketing] BIT Null,
[ContactsUsers] VARCHAR(100) Null,
[ContactsContactsUsers] VARCHAR(100) Null,
[ContactsLocationUsers] VARCHAR(100) Null,
[UnderWriter] VARCHAR(100) Null
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblwManagementInformation] ADD
CONSTRAINT [PK_ManagementInformation] PRIMARY KEY CLUSTERED
(
[Pol_No_Uni]
) ON [PRIMARY]
GO
INSERT INTO tblwManagementInformation
SELECT
dbo.rsPolicy.Pol_No_Uni,
dbo.rsPolicy.Log_Date,
dbo.rsPolicy.Reference,
dbo.rsPolicy.U_W_Ref,
dbo.rsPolicy.Insured,
dbo.rsList_Category.Description,
dbo.rsPolicy.n100_Percent_Si,
dbo.rsPolicy.Com,
dbo.rsPolicy.Written_Si,
dbo.rsPolicy.Premium,
dbo.rsPolicy.Commencement_Date,
dbo.rsPolicy.IPT,
dbo.rsPolicy.Taxed_Premium,
dbo.rsPolicy.Cli_Earned,
dbo.rsPolicy.Premium_To_Insurance,
dbo.rsPolicy.Total_To_Insurance,
dbo.rsPolicy.Pol_No,
dbo.rsPolicy.Policy_Signed,
dbo.rsPolicy.Rate,
dbo.rsPolicyForm.FormName,
dbo.rsList_Wordings.FormName AS 'WordingFormName',
dbo.rsPolicy.Percent_To_Insurer,
dbo.rsList_Users.UserName,
dbo.rsUsersTeam.Name,
dbo.rsList_Category_Group.Description AS 'CategoryGroupDescription',
dbo.rsList_BusinessSource.BusinessName,
dbo.rsPIS.PIS_No,
dbo.rsPIS.pDate,
dbo.rsPIS.Total_Premium,
dbo.rsContacts.Name_of_solicitor,
dbo.rsContacts.Commission,
dbo.rsContacts.pFirm,
dbo.rsContactsLocation.pLocation,
dbo.rsContactsLocation.DX_Address,
dbo.rsContactsLocation.Telephone,
dbo.rsContactsLocation.Fax,
dbo.rsContactsLocation.Ad_House_Name,
dbo.rsContactsLocation.Ad_Street,
dbo.rsContactsLocation.Locality,
dbo.rsContactsLocation.Ad_City,
dbo.rsContactsLocation.Ad_County,
dbo.rsContactsLocation.Ad_Postcode,
dbo.rsContactsContacts.pContact,
dbo.rsContactsContacts.Title,
dbo.rsContactsContacts.First_Name,
dbo.rsContactsContacts.Surname,
dbo.rsContactsContacts.Job_Title,
dbo.rsContactsContacts.Solicitor_Ref,
dbo.rsContactsContacts.Direct_Telephone,
dbo.rsContactsContacts.Direct_Fax,
dbo.rsContactsContacts.Direct_Email,
dbo.rsContactsContacts.Law_Society,
dbo.rsContactsContacts.ContactType,
dbo.rsContactsContacts.Delete_Mail,
dbo.rsContactsContacts.Hit_Rate,
dbo.rsContactsContacts.Rank_Value,
dbo.rsContactsContacts.Enquiries,
dbo.rsContactsContacts.Rank,
dbo.rsContactsContacts.DateLastContacted,
dbo.rsContactsContacts.Contacted,
dbo.rsContactsContacts.Phone,
dbo.rsContactsContacts.Fax AS 'ContactsContactsFax',
dbo.rsContactsContacts.Post,
dbo.rsContactsContacts.Email,
dbo.rsContactsContacts.PanelMember1,
dbo.rsContactsContacts.PanelMember2,
dbo.rsContactsContacts.PanelMember3,
dbo.rsContactsContacts.PanelMember4,
dbo.rsContactsContacts.PanelMember5,
dbo.rsContactsContacts.Commission AS 'ContactsCommission',
dbo.rsContactsContacts.UncompetitivePremiums,
dbo.rsContactsContacts.Service,
dbo.rsContactsContacts.CompanyPolicy,
dbo.rsContactsContacts.Product,
dbo.rsContactsContacts.Flexibility,
dbo.rsContactsContacts.Contact_Added,
dbo.rsContactsContacts.RegEmail,
dbo.rsContactsContacts.DirectMarketing,
dbo.rsList_Users.UserName AS 'ContactsUsers',
dbo.rsList_Users.UserName AS 'ContactsContactsUsers',
dbo.rsList_Users.UserName AS 'ContactsLocationUsers',
dbo.rsList_users.UserName as 'UnderWriter'
FROM dbo.rsPolicy LEFT OUTER JOIN
dbo.rsList_Users ON dbo.rsPolicy.Operator_id = dbo.rsList_Users.pUserID LEFT OUTER JOIN
dbo.rsUsersTeam ON dbo.rsPolicy.UsersTeam_id = dbo.rsUsersTeam.UsersTeam_id LEFT OUTER JOIN
dbo.rsList_Category_Group ON dbo.rsPolicy.pCategory_Group = dbo.rsList_Category_Group.pCategory_Group LEFT OUTER JOIN
dbo.rsList_Wordings ON dbo.rsPolicy.Pol_Word = dbo.rsList_Wordings.pWordNo LEFT OUTER JOIN
dbo.rsPolicyForm ON dbo.rsPolicy.Pol_Form = dbo.rsPolicyForm.pFormNo LEFT OUTER JOIN
dbo.rsList_BusinessSource ON dbo.rsPolicy.BusinessSource = dbo.rsList_BusinessSource.BusID LEFT OUTER JOIN
dbo.rsList_Category ON dbo.rsPolicy.Risk_Category = dbo.rsList_Category.pRisk_Category LEFT OUTER JOIN
dbo.rsContactsContacts ON dbo.rsPolicy.pContact = dbo.rsContactsContacts.pContact LEFT OUTER JOIN
dbo.rsContacts ON dbo.rsPolicy.pFirm = dbo.rsContacts.pFirm LEFT OUTER JOIN
dbo.rsContactsLocation ON dbo.rsPolicy.pLocation = dbo.rsContactsLocation.pLocation LEFT OUTER JOIN
dbo.rsPIS ON dbo.rsPolicy.PIS_No = dbo.rsPIS.PIS_No LEFT OUTER JOIN
dbo.rsList_users a ON rsContacts.Operator_Id = a.pUserId LEFT OUTER JOIN
dbo.rsList_users b ON rsContactsContacts.Operator_Id = b.pUserId LEFT OUTER JOIN
dbo.rsList_users c ON rsContactsLocation.Operator_Id = c.pUserId LEFT OUTER JOIN
dbo.rsList_Users d ON rsPolicy.Operator_Id = d.pUserId
order by pol_no_uni
go
Nick-au
So how come it worked when you ran it in SSMS then
-Jamie
mdschwarz
Hi
Yes, it runs with no problems and used to work in DTS
Sam Hobbs
I don't think that's a good idea. You should concentrate on why this isn't working rather than ignore it and try it some other way. It could be that you experience the same problems.
-Jamie
luca82
Mat1t
Yes. Put Precedence Constraints between them. Precedence Constraints are the Green/Red/Blue lines that dangle off the bottom of tasks in the designer until you hook them up. Same as in DTS.
-Jamie
SDodobara
Hi Jamie
After some investigating I discovered that it was the drop object first option, technically the table didn't exist, I had thought it would have been a check statement like if it exists..... obvisouly not, anyway I've managed to sort that, so thanks guys for all your input
dan59
Duncan,
Does that SQL script work if you run it outside of SSIS
-Jamie
CMValdivia
Strange.
Perhaps try putting the CREATE TABLE script and the INSERT script into seperate Execute SQL Tasks.
Run Profiler as well. Check that the SQL that you expect to get executed IS getting executed.
-Jamie
okidoki
Duncan,
Leave the Create Table in one Execute SQL Task as your first Task. Configure a Source with your Select statement and a Destination insated of the Insert statement.
RussP
I think it was down to the order in which the ssis package was being run, there was a copy sql server object in the package which copied the populated table to another database, there doesn't seem to be a way to place a executing order on objects so I guess it was running the copy table first, when I removed that it worked.
Is there a way to place a running order on tasks as I would like to automate this and I need it to create and populate the table then copy it
Thanks
JCDS
Unfortunately for you, you haven't provided enough details... Show the query perhaps, and please provide more specific information as to your setup.