 | |
Re: Calling all Custom DTS Gurus!
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Calling all Custom DTS Gurus! -
03-06-2008
, 01:24 PM
|
Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote In article <d2602a.0202181023.9554e04 (AT) posting (DOT) google.com>, James Croshaw croshaj1 (AT) hotmail (DOT) com> writes snip This should be a simple error to fix but everything in the script seems to check out just fine. Everything is in the right place and the VB Script that is built is correct and syntactically checks out perfectly. If I save the package before I execute it, it will open up in Enterprise Manager just fine yet when I go to edit the data tranformation it gives me the error: "Some of your transformations refer to columns that do not exist in your source or destination." Then it gives the usual three options: 1. Remove invalid transformations, 2. Change Source/Destination. 3. Remove all transformations and redo automapping. Plus if I build the DTS package manually using the same script it works perfectly. I have even reverse engineered a correct and working manually constructed DTS package (with the same VB Script code for the transformation) but everything seemed to match up just fine. snip The error you mention above would indicate to me that everything is definitely not "built is correct", although it may be syntactically correct. I can't spot anything definite because the script you posted will not execute without some major modifications, and is clearly incomplete. aFields is not defined so I get no SourceColumns collection which would seem quite important as this may be the cause of the error. ReturnFieldOrdinal is also missing, but relying on the extra code you've added at the bottom, the script does not match you DestinationColumns collection. The fields Telephone2 and HomeAddress3 are missing from the script, which may cause errors during execution. Also is ImportGUID a unqiueidentifier type, as the format is not valid. You have also missed setting any column properties for both source and destination columns. Properties such as Flags, Size, DataType, Precision, NumericScale, Nullable are normally set. A more complete script would be useful, preferably as an attachment so I don't have to deal with the line wraps introduced by news clients. Have you used the Save As VB option in SQL 2000 or the ScriptPkg tool for SQL 7.0 ? If not I strongly recommend you take a look as this will hopefully show you everything you need to know. (http://support.microsoft.com/default...;en-us;Q239454) Sounds a very interesting project, good luck! Darren, Thanks for your help. I have discovered that there is a bug in the script which I introduced while debugging so my original post wasn't quite true. It was just a minor problem which related to the Trim and Left functions that I added into the VB Script. It seems to be working fine right now, but I am having problems with Fixed Width templates now as the definitions of the SourceColumns collection does not seem to define the column widths/boundaries properly. I have added the following code for fixed width and set the source connection properties correctly as they should be e.g.: .ConnectionProperties("Data Source").Value = sFilename .ConnectionProperties("Mode").Value = 1 ' ANSI .ConnectionProperties("File Format").Value = 2 ' FIXED WIDTH .ConnectionProperties("File Type").Value = 1 .ConnectionProperties("Number of Column").Value = 0 .ConnectionProperties("Row Delimiter").Value = vbCrLf .ConnectionProperties("Column Delimiter").Value = "" ' Just Empty String for Fixed Width .ConnectionProperties("Text Qualifier").Value = "" ' No Text Qualifier for Fixed Width ... iOrdinal = 1 ' Loop through and add in all source columns For iRowCount = 0 To UBound(aFields,2) sFieldValue = aFields(0,iRowCount) iFieldID = aFields(3,iRowCount) ' Only add if not a placeholder field If CStr(iFieldID) <> "0" Then sColumnName = "col" & Right("000" & CStr(iRowCount+1),3) .SourceColumns.Add .SourceColumns.New(sColumnName, iOrdinal) ' Set the size of fixed width field .SourceColumns.Item(iOrdinal).Size = aFields(4,iRowCount) iOrdinal = iOrdinal + 1 End If Next ... As you suggested in your reply, fixed width file format would definitely require the definition of the size property for each SourceColumn defined. Yet even if this is correctly set it doesn't seem to set the column boundaries when I save the Package to SQL Server and consequently does not run. Anyone out there know what has to be set to define the column boundaries properly for a fixed width file? Many thanks in advance, James XL Technologies. |
#2
| |||
| |||
|
|
Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote In article <d2602a.0202181023.9554e04 (AT) posting (DOT) google.com>, James Croshaw croshaj1 (AT) hotmail (DOT) com> writes snip This should be a simple error to fix but everything in the script seems to check out just fine. Everything is in the right place and the VB Script that is built is correct and syntactically checks out perfectly. If I save the package before I execute it, it will open up in Enterprise Manager just fine yet when I go to edit the data tranformation it gives me the error: "Some of your transformations refer to columns that do not exist in your source or destination." Then it gives the usual three options: 1. Remove invalid transformations, 2. Change Source/Destination. 3. Remove all transformations and redo automapping. Plus if I build the DTS package manually using the same script it works perfectly. I have even reverse engineered a correct and working manually constructed DTS package (with the same VB Script code for the transformation) but everything seemed to match up just fine. snip The error you mention above would indicate to me that everything is definitely not "built is correct", although it may be syntactically correct. I can't spot anything definite because the script you posted will not execute without some major modifications, and is clearly incomplete. aFields is not defined so I get no SourceColumns collection which would seem quite important as this may be the cause of the error. ReturnFieldOrdinal is also missing, but relying on the extra code you've added at the bottom, the script does not match you DestinationColumns collection. The fields Telephone2 and HomeAddress3 are missing from the script, which may cause errors during execution. Also is ImportGUID a unqiueidentifier type, as the format is not valid. You have also missed setting any column properties for both source and destination columns. Properties such as Flags, Size, DataType, Precision, NumericScale, Nullable are normally set. A more complete script would be useful, preferably as an attachment so I don't have to deal with the line wraps introduced by news clients. Have you used the Save As VB option in SQL 2000 or the ScriptPkg tool for SQL 7.0 ? If not I strongly recommend you take a look as this will hopefully show you everything you need to know. (http://support.microsoft.com/default...;en-us;Q239454) Sounds a very interesting project, good luck! Darren, Thanks for your help. I have discovered that there is a bug in the script which I introduced while debugging so my original post wasn't quite true. It was just a minor problem which related to the Trim and Left functions that I added into the VB Script. It seems to be working fine right now, but I am having problems with Fixed Width templates now as the definitions of the SourceColumns collection does not seem to define the column widths/boundaries properly. I have added the following code for fixed width and set the source connection properties correctly as they should be e.g.: .ConnectionProperties("Data Source").Value = sFilename .ConnectionProperties("Mode").Value = 1 ' ANSI .ConnectionProperties("File Format").Value = 2 ' FIXED WIDTH .ConnectionProperties("File Type").Value = 1 .ConnectionProperties("Number of Column").Value = 0 .ConnectionProperties("Row Delimiter").Value = vbCrLf .ConnectionProperties("Column Delimiter").Value = "" ' Just Empty String for Fixed Width .ConnectionProperties("Text Qualifier").Value = "" ' No Text Qualifier for Fixed Width ... iOrdinal = 1 ' Loop through and add in all source columns For iRowCount = 0 To UBound(aFields,2) sFieldValue = aFields(0,iRowCount) iFieldID = aFields(3,iRowCount) ' Only add if not a placeholder field If CStr(iFieldID) <> "0" Then sColumnName = "col" & Right("000" & CStr(iRowCount+1),3) .SourceColumns.Add .SourceColumns.New(sColumnName, iOrdinal) ' Set the size of fixed width field .SourceColumns.Item(iOrdinal).Size = aFields(4,iRowCount) iOrdinal = iOrdinal + 1 End If Next ... As you suggested in your reply, fixed width file format would definitely require the definition of the size property for each SourceColumn defined. Yet even if this is correctly set it doesn't seem to set the column boundaries when I save the Package to SQL Server and consequently does not run. Anyone out there know what has to be set to define the column boundaries properly for a fixed width file? Many thanks in advance, James XL Technologies. |
#3
| |||
| |||
|
|
Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote In article <d2602a.0202181023.9554e04 (AT) posting (DOT) google.com>, James Croshaw croshaj1 (AT) hotmail (DOT) com> writes snip This should be a simple error to fix but everything in the script seems to check out just fine. Everything is in the right place and the VB Script that is built is correct and syntactically checks out perfectly. If I save the package before I execute it, it will open up in Enterprise Manager just fine yet when I go to edit the data tranformation it gives me the error: "Some of your transformations refer to columns that do not exist in your source or destination." Then it gives the usual three options: 1. Remove invalid transformations, 2. Change Source/Destination. 3. Remove all transformations and redo automapping. Plus if I build the DTS package manually using the same script it works perfectly. I have even reverse engineered a correct and working manually constructed DTS package (with the same VB Script code for the transformation) but everything seemed to match up just fine. snip The error you mention above would indicate to me that everything is definitely not "built is correct", although it may be syntactically correct. I can't spot anything definite because the script you posted will not execute without some major modifications, and is clearly incomplete. aFields is not defined so I get no SourceColumns collection which would seem quite important as this may be the cause of the error. ReturnFieldOrdinal is also missing, but relying on the extra code you've added at the bottom, the script does not match you DestinationColumns collection. The fields Telephone2 and HomeAddress3 are missing from the script, which may cause errors during execution. Also is ImportGUID a unqiueidentifier type, as the format is not valid. You have also missed setting any column properties for both source and destination columns. Properties such as Flags, Size, DataType, Precision, NumericScale, Nullable are normally set. A more complete script would be useful, preferably as an attachment so I don't have to deal with the line wraps introduced by news clients. Have you used the Save As VB option in SQL 2000 or the ScriptPkg tool for SQL 7.0 ? If not I strongly recommend you take a look as this will hopefully show you everything you need to know. (http://support.microsoft.com/default...;en-us;Q239454) Sounds a very interesting project, good luck! Darren, Thanks for your help. I have discovered that there is a bug in the script which I introduced while debugging so my original post wasn't quite true. It was just a minor problem which related to the Trim and Left functions that I added into the VB Script. It seems to be working fine right now, but I am having problems with Fixed Width templates now as the definitions of the SourceColumns collection does not seem to define the column widths/boundaries properly. I have added the following code for fixed width and set the source connection properties correctly as they should be e.g.: .ConnectionProperties("Data Source").Value = sFilename .ConnectionProperties("Mode").Value = 1 ' ANSI .ConnectionProperties("File Format").Value = 2 ' FIXED WIDTH .ConnectionProperties("File Type").Value = 1 .ConnectionProperties("Number of Column").Value = 0 .ConnectionProperties("Row Delimiter").Value = vbCrLf .ConnectionProperties("Column Delimiter").Value = "" ' Just Empty String for Fixed Width .ConnectionProperties("Text Qualifier").Value = "" ' No Text Qualifier for Fixed Width ... iOrdinal = 1 ' Loop through and add in all source columns For iRowCount = 0 To UBound(aFields,2) sFieldValue = aFields(0,iRowCount) iFieldID = aFields(3,iRowCount) ' Only add if not a placeholder field If CStr(iFieldID) <> "0" Then sColumnName = "col" & Right("000" & CStr(iRowCount+1),3) .SourceColumns.Add .SourceColumns.New(sColumnName, iOrdinal) ' Set the size of fixed width field .SourceColumns.Item(iOrdinal).Size = aFields(4,iRowCount) iOrdinal = iOrdinal + 1 End If Next ... As you suggested in your reply, fixed width file format would definitely require the definition of the size property for each SourceColumn defined. Yet even if this is correctly set it doesn't seem to set the column boundaries when I save the Package to SQL Server and consequently does not run. Anyone out there know what has to be set to define the column boundaries properly for a fixed width file? Many thanks in advance, James XL Technologies. |
#4
| |||
| |||
|
|
Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote In article <d2602a.0202181023.9554e04 (AT) posting (DOT) google.com>, James Croshaw croshaj1 (AT) hotmail (DOT) com> writes snip This should be a simple error to fix but everything in the script seems to check out just fine. Everything is in the right place and the VB Script that is built is correct and syntactically checks out perfectly. If I save the package before I execute it, it will open up in Enterprise Manager just fine yet when I go to edit the data tranformation it gives me the error: "Some of your transformations refer to columns that do not exist in your source or destination." Then it gives the usual three options: 1. Remove invalid transformations, 2. Change Source/Destination. 3. Remove all transformations and redo automapping. Plus if I build the DTS package manually using the same script it works perfectly. I have even reverse engineered a correct and working manually constructed DTS package (with the same VB Script code for the transformation) but everything seemed to match up just fine. snip The error you mention above would indicate to me that everything is definitely not "built is correct", although it may be syntactically correct. I can't spot anything definite because the script you posted will not execute without some major modifications, and is clearly incomplete. aFields is not defined so I get no SourceColumns collection which would seem quite important as this may be the cause of the error. ReturnFieldOrdinal is also missing, but relying on the extra code you've added at the bottom, the script does not match you DestinationColumns collection. The fields Telephone2 and HomeAddress3 are missing from the script, which may cause errors during execution. Also is ImportGUID a unqiueidentifier type, as the format is not valid. You have also missed setting any column properties for both source and destination columns. Properties such as Flags, Size, DataType, Precision, NumericScale, Nullable are normally set. A more complete script would be useful, preferably as an attachment so I don't have to deal with the line wraps introduced by news clients. Have you used the Save As VB option in SQL 2000 or the ScriptPkg tool for SQL 7.0 ? If not I strongly recommend you take a look as this will hopefully show you everything you need to know. (http://support.microsoft.com/default...;en-us;Q239454) Sounds a very interesting project, good luck! Darren, Thanks for your help. I have discovered that there is a bug in the script which I introduced while debugging so my original post wasn't quite true. It was just a minor problem which related to the Trim and Left functions that I added into the VB Script. It seems to be working fine right now, but I am having problems with Fixed Width templates now as the definitions of the SourceColumns collection does not seem to define the column widths/boundaries properly. I have added the following code for fixed width and set the source connection properties correctly as they should be e.g.: .ConnectionProperties("Data Source").Value = sFilename .ConnectionProperties("Mode").Value = 1 ' ANSI .ConnectionProperties("File Format").Value = 2 ' FIXED WIDTH .ConnectionProperties("File Type").Value = 1 .ConnectionProperties("Number of Column").Value = 0 .ConnectionProperties("Row Delimiter").Value = vbCrLf .ConnectionProperties("Column Delimiter").Value = "" ' Just Empty String for Fixed Width .ConnectionProperties("Text Qualifier").Value = "" ' No Text Qualifier for Fixed Width ... iOrdinal = 1 ' Loop through and add in all source columns For iRowCount = 0 To UBound(aFields,2) sFieldValue = aFields(0,iRowCount) iFieldID = aFields(3,iRowCount) ' Only add if not a placeholder field If CStr(iFieldID) <> "0" Then sColumnName = "col" & Right("000" & CStr(iRowCount+1),3) .SourceColumns.Add .SourceColumns.New(sColumnName, iOrdinal) ' Set the size of fixed width field .SourceColumns.Item(iOrdinal).Size = aFields(4,iRowCount) iOrdinal = iOrdinal + 1 End If Next ... As you suggested in your reply, fixed width file format would definitely require the definition of the size property for each SourceColumn defined. Yet even if this is correctly set it doesn't seem to set the column boundaries when I save the Package to SQL Server and consequently does not run. Anyone out there know what has to be set to define the column boundaries properly for a fixed width file? Many thanks in advance, James XL Technologies. |
#5
| |||
| |||
|
|
Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote In article <d2602a.0202181023.9554e04 (AT) posting (DOT) google.com>, James Croshaw croshaj1 (AT) hotmail (DOT) com> writes snip This should be a simple error to fix but everything in the script seems to check out just fine. Everything is in the right place and the VB Script that is built is correct and syntactically checks out perfectly. If I save the package before I execute it, it will open up in Enterprise Manager just fine yet when I go to edit the data tranformation it gives me the error: "Some of your transformations refer to columns that do not exist in your source or destination." Then it gives the usual three options: 1. Remove invalid transformations, 2. Change Source/Destination. 3. Remove all transformations and redo automapping. Plus if I build the DTS package manually using the same script it works perfectly. I have even reverse engineered a correct and working manually constructed DTS package (with the same VB Script code for the transformation) but everything seemed to match up just fine. snip The error you mention above would indicate to me that everything is definitely not "built is correct", although it may be syntactically correct. I can't spot anything definite because the script you posted will not execute without some major modifications, and is clearly incomplete. aFields is not defined so I get no SourceColumns collection which would seem quite important as this may be the cause of the error. ReturnFieldOrdinal is also missing, but relying on the extra code you've added at the bottom, the script does not match you DestinationColumns collection. The fields Telephone2 and HomeAddress3 are missing from the script, which may cause errors during execution. Also is ImportGUID a unqiueidentifier type, as the format is not valid. You have also missed setting any column properties for both source and destination columns. Properties such as Flags, Size, DataType, Precision, NumericScale, Nullable are normally set. A more complete script would be useful, preferably as an attachment so I don't have to deal with the line wraps introduced by news clients. Have you used the Save As VB option in SQL 2000 or the ScriptPkg tool for SQL 7.0 ? If not I strongly recommend you take a look as this will hopefully show you everything you need to know. (http://support.microsoft.com/default...;en-us;Q239454) Sounds a very interesting project, good luck! Darren, Thanks for your help. I have discovered that there is a bug in the script which I introduced while debugging so my original post wasn't quite true. It was just a minor problem which related to the Trim and Left functions that I added into the VB Script. It seems to be working fine right now, but I am having problems with Fixed Width templates now as the definitions of the SourceColumns collection does not seem to define the column widths/boundaries properly. I have added the following code for fixed width and set the source connection properties correctly as they should be e.g.: .ConnectionProperties("Data Source").Value = sFilename .ConnectionProperties("Mode").Value = 1 ' ANSI .ConnectionProperties("File Format").Value = 2 ' FIXED WIDTH .ConnectionProperties("File Type").Value = 1 .ConnectionProperties("Number of Column").Value = 0 .ConnectionProperties("Row Delimiter").Value = vbCrLf .ConnectionProperties("Column Delimiter").Value = "" ' Just Empty String for Fixed Width .ConnectionProperties("Text Qualifier").Value = "" ' No Text Qualifier for Fixed Width ... iOrdinal = 1 ' Loop through and add in all source columns For iRowCount = 0 To UBound(aFields,2) sFieldValue = aFields(0,iRowCount) iFieldID = aFields(3,iRowCount) ' Only add if not a placeholder field If CStr(iFieldID) <> "0" Then sColumnName = "col" & Right("000" & CStr(iRowCount+1),3) .SourceColumns.Add .SourceColumns.New(sColumnName, iOrdinal) ' Set the size of fixed width field .SourceColumns.Item(iOrdinal).Size = aFields(4,iRowCount) iOrdinal = iOrdinal + 1 End If Next ... As you suggested in your reply, fixed width file format would definitely require the definition of the size property for each SourceColumn defined. Yet even if this is correctly set it doesn't seem to set the column boundaries when I save the Package to SQL Server and consequently does not run. Anyone out there know what has to be set to define the column boundaries properly for a fixed width file? Many thanks in advance, James XL Technologies. |
#6
| |||
| |||
|
|
Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote In article <d2602a.0202181023.9554e04 (AT) posting (DOT) google.com>, James Croshaw croshaj1 (AT) hotmail (DOT) com> writes snip This should be a simple error to fix but everything in the script seems to check out just fine. Everything is in the right place and the VB Script that is built is correct and syntactically checks out perfectly. If I save the package before I execute it, it will open up in Enterprise Manager just fine yet when I go to edit the data tranformation it gives me the error: "Some of your transformations refer to columns that do not exist in your source or destination." Then it gives the usual three options: 1. Remove invalid transformations, 2. Change Source/Destination. 3. Remove all transformations and redo automapping. Plus if I build the DTS package manually using the same script it works perfectly. I have even reverse engineered a correct and working manually constructed DTS package (with the same VB Script code for the transformation) but everything seemed to match up just fine. snip The error you mention above would indicate to me that everything is definitely not "built is correct", although it may be syntactically correct. I can't spot anything definite because the script you posted will not execute without some major modifications, and is clearly incomplete. aFields is not defined so I get no SourceColumns collection which would seem quite important as this may be the cause of the error. ReturnFieldOrdinal is also missing, but relying on the extra code you've added at the bottom, the script does not match you DestinationColumns collection. The fields Telephone2 and HomeAddress3 are missing from the script, which may cause errors during execution. Also is ImportGUID a unqiueidentifier type, as the format is not valid. You have also missed setting any column properties for both source and destination columns. Properties such as Flags, Size, DataType, Precision, NumericScale, Nullable are normally set. A more complete script would be useful, preferably as an attachment so I don't have to deal with the line wraps introduced by news clients. Have you used the Save As VB option in SQL 2000 or the ScriptPkg tool for SQL 7.0 ? If not I strongly recommend you take a look as this will hopefully show you everything you need to know. (http://support.microsoft.com/default...;en-us;Q239454) Sounds a very interesting project, good luck! Darren, Thanks for your help. I have discovered that there is a bug in the script which I introduced while debugging so my original post wasn't quite true. It was just a minor problem which related to the Trim and Left functions that I added into the VB Script. It seems to be working fine right now, but I am having problems with Fixed Width templates now as the definitions of the SourceColumns collection does not seem to define the column widths/boundaries properly. I have added the following code for fixed width and set the source connection properties correctly as they should be e.g.: .ConnectionProperties("Data Source").Value = sFilename .ConnectionProperties("Mode").Value = 1 ' ANSI .ConnectionProperties("File Format").Value = 2 ' FIXED WIDTH .ConnectionProperties("File Type").Value = 1 .ConnectionProperties("Number of Column").Value = 0 .ConnectionProperties("Row Delimiter").Value = vbCrLf .ConnectionProperties("Column Delimiter").Value = "" ' Just Empty String for Fixed Width .ConnectionProperties("Text Qualifier").Value = "" ' No Text Qualifier for Fixed Width ... iOrdinal = 1 ' Loop through and add in all source columns For iRowCount = 0 To UBound(aFields,2) sFieldValue = aFields(0,iRowCount) iFieldID = aFields(3,iRowCount) ' Only add if not a placeholder field If CStr(iFieldID) <> "0" Then sColumnName = "col" & Right("000" & CStr(iRowCount+1),3) .SourceColumns.Add .SourceColumns.New(sColumnName, iOrdinal) ' Set the size of fixed width field .SourceColumns.Item(iOrdinal).Size = aFields(4,iRowCount) iOrdinal = iOrdinal + 1 End If Next ... As you suggested in your reply, fixed width file format would definitely require the definition of the size property for each SourceColumn defined. Yet even if this is correctly set it doesn't seem to set the column boundaries when I save the Package to SQL Server and consequently does not run. Anyone out there know what has to be set to define the column boundaries properly for a fixed width file? Many thanks in advance, James XL Technologies. |
#7
| |||
| |||
|
|
Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote In article <d2602a.0202181023.9554e04 (AT) posting (DOT) google.com>, James Croshaw croshaj1 (AT) hotmail (DOT) com> writes snip This should be a simple error to fix but everything in the script seems to check out just fine. Everything is in the right place and the VB Script that is built is correct and syntactically checks out perfectly. If I save the package before I execute it, it will open up in Enterprise Manager just fine yet when I go to edit the data tranformation it gives me the error: "Some of your transformations refer to columns that do not exist in your source or destination." Then it gives the usual three options: 1. Remove invalid transformations, 2. Change Source/Destination. 3. Remove all transformations and redo automapping. Plus if I build the DTS package manually using the same script it works perfectly. I have even reverse engineered a correct and working manually constructed DTS package (with the same VB Script code for the transformation) but everything seemed to match up just fine. snip The error you mention above would indicate to me that everything is definitely not "built is correct", although it may be syntactically correct. I can't spot anything definite because the script you posted will not execute without some major modifications, and is clearly incomplete. aFields is not defined so I get no SourceColumns collection which would seem quite important as this may be the cause of the error. ReturnFieldOrdinal is also missing, but relying on the extra code you've added at the bottom, the script does not match you DestinationColumns collection. The fields Telephone2 and HomeAddress3 are missing from the script, which may cause errors during execution. Also is ImportGUID a unqiueidentifier type, as the format is not valid. You have also missed setting any column properties for both source and destination columns. Properties such as Flags, Size, DataType, Precision, NumericScale, Nullable are normally set. A more complete script would be useful, preferably as an attachment so I don't have to deal with the line wraps introduced by news clients. Have you used the Save As VB option in SQL 2000 or the ScriptPkg tool for SQL 7.0 ? If not I strongly recommend you take a look as this will hopefully show you everything you need to know. (http://support.microsoft.com/default...;en-us;Q239454) Sounds a very interesting project, good luck! Darren, Thanks for your help. I have discovered that there is a bug in the script which I introduced while debugging so my original post wasn't quite true. It was just a minor problem which related to the Trim and Left functions that I added into the VB Script. It seems to be working fine right now, but I am having problems with Fixed Width templates now as the definitions of the SourceColumns collection does not seem to define the column widths/boundaries properly. I have added the following code for fixed width and set the source connection properties correctly as they should be e.g.: .ConnectionProperties("Data Source").Value = sFilename .ConnectionProperties("Mode").Value = 1 ' ANSI .ConnectionProperties("File Format").Value = 2 ' FIXED WIDTH .ConnectionProperties("File Type").Value = 1 .ConnectionProperties("Number of Column").Value = 0 .ConnectionProperties("Row Delimiter").Value = vbCrLf .ConnectionProperties("Column Delimiter").Value = "" ' Just Empty String for Fixed Width .ConnectionProperties("Text Qualifier").Value = "" ' No Text Qualifier for Fixed Width ... iOrdinal = 1 ' Loop through and add in all source columns For iRowCount = 0 To UBound(aFields,2) sFieldValue = aFields(0,iRowCount) iFieldID = aFields(3,iRowCount) ' Only add if not a placeholder field If CStr(iFieldID) <> "0" Then sColumnName = "col" & Right("000" & CStr(iRowCount+1),3) .SourceColumns.Add .SourceColumns.New(sColumnName, iOrdinal) ' Set the size of fixed width field .SourceColumns.Item(iOrdinal).Size = aFields(4,iRowCount) iOrdinal = iOrdinal + 1 End If Next ... As you suggested in your reply, fixed width file format would definitely require the definition of the size property for each SourceColumn defined. Yet even if this is correctly set it doesn't seem to set the column boundaries when I save the Package to SQL Server and consequently does not run. Anyone out there know what has to be set to define the column boundaries properly for a fixed width file? Many thanks in advance, James XL Technologies. |
#8
| |||
| |||
|
|
Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote In article <d2602a.0202181023.9554e04 (AT) posting (DOT) google.com>, James Croshaw croshaj1 (AT) hotmail (DOT) com> writes snip This should be a simple error to fix but everything in the script seems to check out just fine. Everything is in the right place and the VB Script that is built is correct and syntactically checks out perfectly. If I save the package before I execute it, it will open up in Enterprise Manager just fine yet when I go to edit the data tranformation it gives me the error: "Some of your transformations refer to columns that do not exist in your source or destination." Then it gives the usual three options: 1. Remove invalid transformations, 2. Change Source/Destination. 3. Remove all transformations and redo automapping. Plus if I build the DTS package manually using the same script it works perfectly. I have even reverse engineered a correct and working manually constructed DTS package (with the same VB Script code for the transformation) but everything seemed to match up just fine. snip The error you mention above would indicate to me that everything is definitely not "built is correct", although it may be syntactically correct. I can't spot anything definite because the script you posted will not execute without some major modifications, and is clearly incomplete. aFields is not defined so I get no SourceColumns collection which would seem quite important as this may be the cause of the error. ReturnFieldOrdinal is also missing, but relying on the extra code you've added at the bottom, the script does not match you DestinationColumns collection. The fields Telephone2 and HomeAddress3 are missing from the script, which may cause errors during execution. Also is ImportGUID a unqiueidentifier type, as the format is not valid. You have also missed setting any column properties for both source and destination columns. Properties such as Flags, Size, DataType, Precision, NumericScale, Nullable are normally set. A more complete script would be useful, preferably as an attachment so I don't have to deal with the line wraps introduced by news clients. Have you used the Save As VB option in SQL 2000 or the ScriptPkg tool for SQL 7.0 ? If not I strongly recommend you take a look as this will hopefully show you everything you need to know. (http://support.microsoft.com/default...;en-us;Q239454) Sounds a very interesting project, good luck! Darren, Thanks for your help. I have discovered that there is a bug in the script which I introduced while debugging so my original post wasn't quite true. It was just a minor problem which related to the Trim and Left functions that I added into the VB Script. It seems to be working fine right now, but I am having problems with Fixed Width templates now as the definitions of the SourceColumns collection does not seem to define the column widths/boundaries properly. I have added the following code for fixed width and set the source connection properties correctly as they should be e.g.: .ConnectionProperties("Data Source").Value = sFilename .ConnectionProperties("Mode").Value = 1 ' ANSI .ConnectionProperties("File Format").Value = 2 ' FIXED WIDTH .ConnectionProperties("File Type").Value = 1 .ConnectionProperties("Number of Column").Value = 0 .ConnectionProperties("Row Delimiter").Value = vbCrLf .ConnectionProperties("Column Delimiter").Value = "" ' Just Empty String for Fixed Width .ConnectionProperties("Text Qualifier").Value = "" ' No Text Qualifier for Fixed Width ... iOrdinal = 1 ' Loop through and add in all source columns For iRowCount = 0 To UBound(aFields,2) sFieldValue = aFields(0,iRowCount) iFieldID = aFields(3,iRowCount) ' Only add if not a placeholder field If CStr(iFieldID) <> "0" Then sColumnName = "col" & Right("000" & CStr(iRowCount+1),3) .SourceColumns.Add .SourceColumns.New(sColumnName, iOrdinal) ' Set the size of fixed width field .SourceColumns.Item(iOrdinal).Size = aFields(4,iRowCount) iOrdinal = iOrdinal + 1 End If Next ... As you suggested in your reply, fixed width file format would definitely require the definition of the size property for each SourceColumn defined. Yet even if this is correctly set it doesn't seem to set the column boundaries when I save the Package to SQL Server and consequently does not run. Anyone out there know what has to be set to define the column boundaries properly for a fixed width file? Many thanks in advance, James XL Technologies. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.